Skip to main content

Enable SQL Server Change Tracking using T-SQL

Enable SQL Server Change Tracking using T-SQL

You can enable the Change Tracking feature for a table to keep track of the rows which are inserted, updated or deleted.  

Change tracking is a way for SQL Server automatically keep track of the rows that have been inserted, updated or deleted in a table.

You have to enable the Change tracking at the database level and also on each table that you want to track.

Suppose you want to enable change tracking for your database named APPDB, you can use following tsql script:

 ALTER DATABASE APPDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)

GO


Following query list out the database which have Change tracking enabled:

SELECT DB_NAME(database_id) [DATABASE_NAME],* FROM sys.change_tracking_databases



To enable Change tracking for a particular table use the following script, which enable CT for table named EMP

ALTER TABLE dbo.EMP ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF)

GO


Use the below script to list out tables which have Change tracking enabled:

SELECT OBJECT_NAME(object_id) [TABLE_NAME],* FROM sys.change_tracking_tables


You can use Change tracking concept to set up a DW project. 

Suppose you have the Application DB and its corresponding DW DB. For each table in APP db, there will be a corresponding table in your DW DB in order to keep the latest and history data. In your application database you can enable the change tracking for the tables which you want to keep history in DW. Then implement a mechanism like SQL jobs which check data changes in  the tables  using SSIS package in your  APP DB and push the changes to corresponding DW table . Once the job  has run ,latest data which has added to the APP table will get inserted to DW table as the latest row and existing rows will get updated as history records in that table.


Labels:

Comments

Post a Comment

Popular posts from this blog

SQL Server script Error: Insufficient result space to convert uniqueidentifier value to char.

Error:    G etting below error while trying to copy data from one table to another: Msg 8170, Level 16, State 2, Line 1004  Insufficient result space to convert uniqueidentifier value to char.   Fix: UserID column which would be having Unique identifier data require more space (36 character). Size is specified as 15 here. To fix it just specify 36 as varchar length or specify Uniqueidentifier as data type for UserID  column.
Post a Comment
Read more

While executing Select query to pull data from a different server, SQL Server is showing following error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'LAPTOP-09O6NE3K\SQLEXPRESS03' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. Completion time: 2024-02-05T17:18:24.6869756+05:30 Solution: Step1:  Check if the server is available in the sys.servers table SELECT name from sys.servers Step2:  If no, add the new server to the sys.servers table EXEC sp_addlinkedserver @server = 'server name' Eg" EXEC sp_addlinkedserver @server = 'LAPTOP-09O6NE3K\SQLEXP RESS03' Step3:  Recheck if the new server name is added to the sys.servers table. If yes, try then rerunning the select query which throwed the error. Now it should pull data from the table in the new server successfully.    
Post a Comment
Read more

Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2

Overcoming Performance Issues with Memory Optimized tables - SQL Server 2014 CPT2 1.        Problem Tuning    databases so that they provide the best possible performance is one of the most important responsibilities of database administrators (DBAs).   SQL Server was originally designed at a time when it could be assumed that main memory was very expensive, so data needed to reside on disk except when it was actually needed for processing.   As the amount data    within an organization increases, accumulation of data affects the productivity of DBAs, who must spend considerable time and effort on achieving the high levels of database performance. 2.        Solution Nowadays memory is much cheaper and a working data set can be stored entirely in memory. The assumption that main memory was very expensive is no longer valid as memory prices have dropped enormously. Memory-opti...
Post a Comment
Read more