Change Tracking in SQL Server

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.

Prerequisites:

You must first enable CHANGE TRACKING at database level.
The table you wish to track must have a primary key.

Sample lab script

You can enable the same from UI. <<Database properties>>

CT_01

Now, the next step is to enable Change Tracking on the table we wish to keep track of.

DML Operations:

Now, we will do some insert, update and delete operation on “State” table.

CT_02

Column Name Description
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.

CT_03

CT_04
All DML Changes so far
CT_05
Specific column update status

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:

 

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

Leave a Reply

%d bloggers like this: