Change tracking is a powerful feature in SQL Server that allows you to track changes made to your database tables. By default, change tracking is enabled at the database level, which means that any changes made to any table in the database will be tracked. However, there may be cases where you only want to track changes made to specific columns in a table. In this article, we will explore how to enable column level change tracking in SQL Server.

Step 1 – Turn on Change Tracking at the Database Level

The first step is to enable change tracking at the database level. This can be done by right-clicking on your database in SQL Server Management Studio, selecting “Properties,” and then navigating to the “Change Tracking” tab. Here, you can set the “Change Tracking” option to “True.”

Step 2 – Turn on Change Tracking at the Table Level

Once change tracking is enabled at the database level, you can then enable it at the table level. Right-click on the table you want to track changes for, select “Properties,” and navigate to the “Change Tracking” tab. Set the “Change Tracking” option to “True.”

Step 3 – Enable Column Update Tracking

In addition to enabling change tracking at the table level, you can also enable column update tracking. This allows you to track changes made to specific columns in the table. From the same “Change Tracking” window, set the “Track Columns Updated” option to “True.”

Step 4 – Update Records and Mark Changes

Now that change tracking is enabled, you can update records in the table and mark the changes using the “CHANGE_TRACKING_CONTEXT” function. This function allows you to easily identify or select records that have been updated. Here’s an example:

DECLARE @Context varbinary(128) = CAST('Update - ColumnName' AS varbinary(128));

WITH CHANGE_TRACKING_CONTEXT (@Context)  
UPDATE YourTable
SET ColumnName = 'NewValue'
WHERE YourCondition;

By using the “CHANGE_TRACKING_CONTEXT” function, you can mark the changes made to the “ColumnName” column with the specified context. This will make it easier to identify these changes later.

Step 5 – Retrieve Changed Records

Once you have marked the changes, you can retrieve the changed records using the “CHANGETABLE (VERSION)” function. This function returns the latest change tracking information for the current rows in a table, including the change version number and the change context if used. Here’s an example:

SELECT *
FROM YourTable
CROSS APPLY CHANGETABLE (VERSION YourTable, (PrimaryKeyColumn), (YourPrimaryKeyValue)) AS CT
WHERE CT.SYS_CHANGE_VERSION IS NOT NULL;

This query joins the “CHANGETABLE (VERSION)” function with the original table using the primary key columns. It retrieves all rows that have been changed, along with their corresponding change version number and change context.

Step 6 – Filter Changed Records by Column

If you only want to retrieve records where a specific column has been updated, you can use the “CHANGE_TRACKING_IS_COLUMN_IN_MASK” function. This function determines if a specific column was updated or not. Here’s an example:

SELECT *
FROM YourTable
CROSS APPLY CHANGETABLE (CHANGES YourTable, @ControlVersionNo) AS CT
WHERE CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('YourTable'), 'ColumnName', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) = 1;

In this query, we use the “CHANGE_TRACKING_IS_COLUMN_IN_MASK” function to check if the “ColumnName” column was updated. We pass the column ID using the “COLUMNPROPERTY” function. Only the rows where the column was updated will be returned.

Conclusion

Enabling column level change tracking in SQL Server allows you to track changes made to specific columns in your database tables. By following the steps outlined in this article, you can easily enable column level change tracking and retrieve the changed records. This feature provides a lightweight and efficient way to track and monitor changes in your database.

Thank you for reading!