Tracking column updates using sql server change tracking

This is in continuation of the previous post on introduction to change tracking, in this post, we will see how to track column updates. We already updated the column tracking on our person table using the following command.

USE CTDemoDb
GO
ALTER TABLE dbo.Person ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)

Let’s run an update on one of the columns.

USE CTDemoDb
GO
UPDATE [dbo].[Person] SET [DateOfBirth] = '1963-12-9' WHERE ID = 5

and running the below change tracking queries

USE CTDemoDb
GO
DECLARE @PREVIOUS_SYNCED_VERSION BIGINT
DECLARE @CURRENT_CHANGE_VERSION BIGINT

SET @PREVIOUS_SYNCED_VERSION = 3 -- Here you need to use the version till which you already synced last time
SET @CURRENT_CHANGE_VERSION = CHANGE_TRACKING_CURRENT_VERSION()
SELECT @CURRENT_CHANGE_VERSION CURRENT_CHANGE_VERSION

SELECT * FROM CHANGETABLE(CHANGES dbo.Person, @PREVIOUS_SYNCED_VERSION) AS CT1 ORDER BY SYS_CHANGE_VERSION

results are

The SYS_CHANGE_COLUMNS column gives us all the columns which changed in a masked manner. Let’s see how to use it.

Finding changed columns

Run the below query to see if the given column has been changed.

-- CHECK IF THE COLUMN IS UPDATED
DECLARE @PREVIOUS_SYNCED_VERSION BIGINT
SET @PREVIOUS_SYNCED_VERSION = 3 -- Here you need to use the version till which you already synced last time
DECLARE @SYS_CHANGE_COLUMNS AS varbinary(4100)

SELECT @SYS_CHANGE_COLUMNS =  SYS_CHANGE_COLUMNS FROM CHANGETABLE(CHANGES dbo.Person, @PREVIOUS_SYNCED_VERSION) AS CT1 ORDER BY SYS_CHANGE_VERSION

SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK  
    (COLUMNPROPERTY(OBJECT_ID('Person'), 'DateOfBirth', 'ColumnId')  
    ,@SYS_CHANGE_COLUMNS) HasColumnChanged;  

In the above query we use  CHANGE_TRACKING_IS_COLUMN_IN_MASK function to check if a particular column id exist in the SYS_CHANGE_COLUMNS  mask value.

We can further use this function to find the changed value by combining it with our change tracking query.

-- GET THE VALUE OF THE CHANGED COLUMN
SELECT 
   CT.SYS_CHANGE_OPERATION, CT.Id, PRS.FirstName, PRS.DateOfBirth,
   CT.SYS_CHANGE_VERSION, CT.SYS_CHANGE_CREATION_VERSION,
   CT.SYS_CHANGE_OPERATION, CT.SYS_CHANGE_CONTEXT,
   CONVERT(VARCHAR, CT.SYS_CHANGE_CONTEXT) AS Tracking_Context,
   CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Person'), 'DateOfBirth', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) AS HasColumnChanged
FROM CHANGETABLE (CHANGES dbo.Person, @PREVIOUS_SYNCED_VERSION) CT
LEFT OUTER JOIN Person PRS ON CT.Id = PRS.Id
ORDER BY CT.SYS_CHANGE_VERSION

Results:

Setting tracking context

Using change tracking we can also set a tracking context to the changes (updates /inserts / deletes) being done i.e. we can provide some information related to the change being done and this could be any textual information e.g. who made the change ,reason for the change or any other associated metadata.

In the below query we set the context for one of the deletes being done.

	DECLARE @TRACKING_CONTEXT VARBINARY(120)
	SET @TRACKING_CONTEXT = CAST('Testing tracking context' AS VARBINARY(120));
	WITH CHANGE_TRACKING_CONTEXT ( @TRACKING_CONTEXT ) DELETE FROM dbo.Person where ID =1

If you run the change tracking queries you will get below results

SYS_CHANGE_CONTEXT column has the context value associated with the change in a binary form.

Let’s see how to get the value.

DECLARE @PREVIOUS_SYNCED_VERSION BIGINT
SET @PREVIOUS_SYNCED_VERSION = 5 -- Here you need to use the version till which you already synced last time
SELECT CAST(SYS_CHANGE_CONTEXT AS VARCHAR(200)) FROM CHANGETABLE(CHANGES dbo.Person, @PREVIOUS_SYNCED_VERSION) AS CT1 ORDER BY SYS_CHANGE_VERSION

Results:

Hope this gives you all clarity on how to use SQL server change tracking. Change tracking is quite a useful feature that is easy to use and can be used in various ways e.g. enabling auditing on the database or implementing differential sync from a SQL server database to another data source or application.