Column Level Change Tracking (SQL Server)

Many a times you might have come across a situation where you have a large table with lot of columns and you are interested in tracking changes only to the columns which you are interested in by polling the table, sql server provides an easy way to accomplish this by using something called change tracking, let me demonstrate this technique using an example.

CREATE TABLE [dbo].[tblEmployee](

[EmployeeID] [int] IDENTITY(1,1) NOT NULL,

[Name] [varchar](20) NOT NULL,

[City] [varchar](20) NOT NULL,

[Department] [varchar](20) NOT NULL,

[Gender] [varchar](6) NOT NULL)

GO

To track changes to the table tblEmployee here are the steps you need to follow:

1.Enable change tracking at database level

ALTER DATABASE (YourDatabaseName)

SET CHANGE_TRACKING = ON

(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

2.Enable change tracking at table level

ALTER TABLE tblEmployee

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

After this use the following query to check for updates occurred in the table

DECLARE @CurrentSynchronizationVersion int = CHANGE_TRACKING_CURRENT_VERSION()

DECLARE @LastSynchronizationVersion int = @CurrentSynchronizationVersion — 1

DECLARE @Name_ColumnId int = COLUMNPROPERTY(OBJECT_ID(‘tblEmployee’), ‘Name’, ‘ColumnId’)

DECLARE @Gender_ColumnId int = COLUMNPROPERTY(OBJECT_ID(‘tblEmployee’), ‘Gender’, ‘ColumnId’)

SELECT

e.EmployeeID,

e.Name,

e.Gender,

@CurrentSynchronizationVersion CurrentSynchronizationVersion,

@LastSynchronizationVersion LastSynchronizationVersion,

CHANGE_TRACKING_IS_COLUMN_IN_MASK(@Name_ColumnId, CT.SYS_CHANGE_COLUMNS) CT_Name_Changed,

CHANGE_TRACKING_IS_COLUMN_IN_MASK(@Gender_ColumnId, CT.SYS_CHANGE_COLUMNS) CT_Gender_Changed

FROM

tblEmployee AS e

INNER JOIN

CHANGETABLE(CHANGES tblEmployee, @LastSynchronizationVersion) AS CT

ON

e.EmployeeID = CT.EmployeeID AND

CT.SYS_CHANGE_OPERATION = ‘U’

Here you need to notice the variable CurrentSynchronizationVersion which gives the current version after the update and you need to subtract it by 1 to get the LastSynchronizationVersion, by this way check for any updates to the columns you are interested in the table.

Happy coding!!!