Change tracking was introduced in SQL Server 2008 and is similar to Change data capture but differences are, it works in synchronous process, does not require SQL agent to be running, it’s lighter than CDC.
You must first enable CHANGE TRACKING at database level.
The table you wish to track must have a primary key.
--Enabling at database level ALTER DATABASE dbBasics SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
You can enable the same from UI. <<Database properties>>
Now, the next step is to enable Change Tracking on the table we wish to keep track of.
ALTER TABLE [dbo].[State] ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
Now, we will do some insert, update and delete operation on “State” table.
--Insert INSERT INTO [dbo].[State] (StateName, [StateCode], [Population], [Language]) VALUES ('Orissa', 'OR', '41,947,358', 'Oriya') --Update UPDATE [dbo].[State] SET Population = '31169273' WHERE StateCode = 'AS' --Delete DELETE FROM [dbo].[State] WHERE StateCode = 'BR' SELECT * from CHANGETABLE(CHANGES [State], 0) ct --0 = All changes
|SYS_CHANGE_VERSION||It represents the last version number when a particular row was updated.|
|SYS_CHANGE_CREATION_VERSION||It represents the version number when a record was inserted. It will not be over-written as in case of SYS_CHANGE_VERSION.|
|SYS_CHANGE_OPERATION||It represents the DML operations (I=INSERT, U=UPDATE and D=DELETE)|
|SYS_CHANGE_COLUMNS||It represents all the columns impacted since last baseline version. This column will have values only for UPDATE operations and if columns are not impacted during update it will have NULL.|
|<primary key columns>||It represents the primary key columns of your tracked user table. You can join your tracked user table with this columns to get only changed data from the tracked user table.|
- There are four objects being created in the database, related to Change Tracking.
- CHANGE_TRACKING_MIN_VALID_VERSION – This function returns the minimum valid version for a table after the change tracking information is available or retained. If this returns a higher value than your <PreviousSyncVersion>, you need to do a full pull from your tracking table.
- CHANGE_TRACKING_CURRENT_VERSION – This function returns the current version after the last committed transaction at the database level. You will get NULL if you have not turned on Change Tracking at the database level.
- CHANGE_TRACKING_IS_COLUMN_IN_MASK – The SYS_CHANGE_COLUMNS column in the table returned by CHANGETABLE function contains information in the binary format. CHANGE_TRACKING_IS_COLUMN_IN_MASK function takes two parameter, first one is the table name and second one is column name and will return 1 or 0 depending on whether that column was updated or not.
- WITH CHANGE_TRACKING_CONTEXT – If you want to differentiate the changes being done by your own application compared to others you can use this statement with your DML operations.
SELECT CHANGE_TRACKING_CURRENT_VERSION () as CurrentChangeVersion , CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('State')) as MinValidVersion SELECT * FROM CHANGETABLE (CHANGES State,0) as CT ORDER BY SYS_CHANGE_VERSION
SELECT CTTable.StateName, CTTable.SYS_CHANGE_OPERATION, St.StateCode, St.Population, St.Language, CTTable.SYS_CHANGE_VERSION, CTTable.SYS_CHANGE_COLUMNS, CTTable.SYS_CHANGE_CONTEXT FROM CHANGETABLE (CHANGES State, 0) AS CTTable LEFT OUTER JOIN State AS St ON St.StateName = CTTable.StateName
SELECT St.*, [isStateNameChanged?] = CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('State'), 'StateName', 'ColumnId'), SYS_CHANGE_COLUMNS), [isStateCodeChanged?] = CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('State'), 'StateCode', 'ColumnId'), SYS_CHANGE_COLUMNS), [isPopulationChanged?] = CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('State'), 'Population', 'ColumnId'), SYS_CHANGE_COLUMNS), [isLanguageChanged?] = CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('State'), 'Language', 'ColumnId'), SYS_CHANGE_COLUMNS) FROM CHANGETABLE (CHANGES [State], 0) AS CTTable LEFT OUTER JOIN [State] AS St ON St.StateName = CTTable.StateName WHERE CTTable.SYS_CHANGE_OPERATION = 'U'
Note: Primary key updates are not treated as update in CT, but rather it will be treated as deletion of old value and insertion of new value.
Disable Change Tracking:
--To check change tracking enabled table names SELECT OBJECT_NAME(OBJECT_ID) AS TableName, is_track_columns_updated_on, min_valid_version, begin_version, cleanup_version FROM sys.change_tracking_tables --To disable change tracking on table level ALTER TABLE [TableName] ENABLE CHANGE_TRACKING --To disable change tracking on database level ALTER database [DatabaseName] SET CHANGE_TRACKING = OFF