SQL Server Change Tracking to Track Columns Updated

By: Jim Evans | Updated: 2021-02-24 | Comments | Related: > Change Data Capture

Problem

In a previous article I showed how to use Change Tracking to Mark updated records using CHANGE_TRACKING_CONTEXT without changing their values and retrieving the updated records. But what if you only want to identify or move records if certain columns change? In this article I will demonstrate how to enable SQL Change Tracking at the Column level and identify records for which specific column values changed.

Solution

For this example, I will use a copy of the AdventureWorksLT (Lite) sample database in Azure. My database is named AWLDB1. SQL Change Tracking was introduced in SQL 2008, so you can test this in any version of SQL 2008 or newer.

Step 1 – Turn on SQL Change Tracking at the Database Level

First, turn on Change Tracking at the Database Level. In SQL Server Management Studio, right click your database and click Properties. In the Properties window click Change Tracking. In the right pain set Change Tracking to TRUE.

Step 2 – Turn on Change Tracking at the Table Level

For this demo I will use the SalesLT.Customer table. Right click on the table you want to track changes. Click Properties, click Change Tracking, then in the right pane set Change Tracking to TRUE.

Step 2a – Turn on Column Update Tracking

From the same Change Tracking window as above, also set Track Columns Updated to TRUE.

Step 3 – List Change Tracking Functions

In this exercise we will be using the following Change Tracking functions:

A) Use CHANGE_TRACKING_CURRENT_VERSION() to get the current database version which will be used next time when identifying changes.
B) The CHANGETABLE (CHANGES) table function: Returns tracking information since the version specified. The arguments are the table name and the change version number.
C) The CHANGETABLE (VERSION) table 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. This table function arguments are the user table and the primary key columns of that table.
D) The WITH CHANGE_TRACKING_CONTEXT (context): Used to mark the changed records to easily be identified or selected later. The context is a string converted to varbinary (128).
E) Use CHANGE_TRACKING_MIN_VALID_VERSION() to see the minimum version for which you can obtain changes. This value is related to the retention period set for change tracking.
F) Use CHANGE_TRACKING_IS_COLUMN_IN_MASK() to determine is a specific column was updated or not. Can be used in the WHERE clause to only return records if a specific column changed.

Step 4 – Update Some Records

I will setup the example by doing 3 different updates in table SalesLT.Customer. I will focus on updating the Columns SalesPerson and ModifiedDate. For this demo I will mark my updates with CHANGE_TRACKING_CONTEXT as to better show the different data updates. With Change Tracking enabled it is easy to mark your transactions using WITH CHANGE_TRACKING_CONTEXT above the Update statement. NOTE: After each Update I check the CHANGE_TRACKING_CURRENT_VERSION(). This will be used later in the demo.

 --1. Update: Reassign some customer to SalesPerson = 'adventure-works\JimEvans1'.
DECLARE @Context varbinary(128) = CAST('1st Update - SalesPerson' AS varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@Context)  
 UPDATE SalesLT.Customer
SET [SalesPerson] = 'adventure-works\JimEvans1',
   [ModifiedDate] = GETDATE() 
WHERE CustomerID between 1 and 10
GO
 
--Check current Change Tracking Version Number
SELECT CHANGE_TRACKING_CURRENT_VERSION(); 
GO
 
--2. Update just the ModifiedDate column for some customers.
DECLARE @Context varbinary(128) = CAST('2nd Update - ModifiedDate' AS varbinary(128));
 
WITH CHANGE_TRACKING_CONTEXT (@Context)  
UPDATE SalesLT.Customer
SET [ModifiedDate] = GETDATE() 
WHERE CustomerID between 30 and 40
GO
 
--Check current Change Tracking Version Number
SELECT CHANGE_TRACKING_CURRENT_VERSION(); 
GO
 
--3. Reassign some customer to SalesPerson AnneSmith2
DECLARE @Context varbinary(128) = CAST('3nd Update - SalesPerson' AS varbinary(128));
 
WITH CHANGE_TRACKING_CONTEXT (@Context)  
 UPDATE SalesLT.Customer
SET [SalesPerson] = 'adventure-works\AnneSmith2',
   [ModifiedDate] = GETDATE() 
WHERE CustomerID between 11 and 20
GO
 
--Check current Change Tracking Version Number
SELECT CHANGE_TRACKING_CURRENT_VERSION(); 
GO

Step 5 – Show Previous Updated Records using CHANGETABLE(VERSION)

This step shows the results of the prior updates by Joining the SalesLT.Customer to the CHANGETABLE() function using Cross Apply. The results show each update marked by SYS_CHANGE_CONTEXT and the version numbers associated with the updates.

--Retrieve All rows with CROSS APPLY Join to the CHANGETABLE (VERSION) Function, returning the row VERSION number and the SYS_CHANGE_CONTEXT if used for updates.
SELECT c.[CustomerID], c.[SalesPerson], c.[ModifiedDate]
      ,CT.SYS_CHANGE_VERSION 
      ,CAST(ct.SYS_CHANGE_CONTEXT as varchar(255)) as 'SYS_CHANGE_CONTEXT'
      ,CT.CustomerID
FROM SalesLT.Customer AS c  
   CROSS APPLY CHANGETABLE(VERSION SalesLT.Customer, (CustomerID), (c.CustomerID)) AS CT
Where CT.SYS_CHANGE_VERSION IS NOT NULL
Order by CT.SYS_CHANGE_VERSION;
GO

Result 5a – Shows the results of the previous 3 Updates and their different version numbers and marked SYS_CHANGE_CONTEXT.

Step 6 – Use CHANGETABLE (CHANGES) Function & CHANGE_TRACKING_IS_COLUMN_IN_MASK

The CHANGETABLE (CHANGES) function is used to listing all changes that were made since a specific version. Adding the CHANGE_TRACKING_IS_COLUMN_IN_MASK() function to the where clause retrieves only rows for which the SalesPerson was updated. For this we will use the Version Numbers values from Updates in Step 4. Try different version number values!

 --Set @ControlVersionNo to your value based on your change tracking version numbers.  It will be used as an argument in the CHANGETABLE (CHANGES) function.
DECLARE @ControlVersionNo int = 107  
 
--CHANGE_TRACKING_IS_COLUMN_IN_MASK to interprets the SYS_CHANGE_COLUMNS value that is returned by the CHANGETABLE(CHANGES …) function.
 SELECT CT.[CustomerID] --just PKeys
    ,CT.SYS_CHANGE_VERSION
    ,CT.SYS_CHANGE_OPERATION    
    ,CT.SYS_CHANGE_COLUMNS 
    ,CAST(CT.SYS_CHANGE_CONTEXT AS varchar(128)) as 'Marked Context'
    ,COLUMNPROPERTY(OBJECT_ID('SalesLT.Customer'), 'SalesPerson', 'ColumnId') as 'ColumnId'
    ,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('SalesLT.Customer'), 'SalesPerson', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) as 'SalesPerson Changed'
    ,CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('SalesLT.Customer'), 'ModifiedDate', 'ColumnId'), CT.SYS_CHANGE_COLUMNS)as 'ModifiedDate Changed'
 
FROM CHANGETABLE (CHANGES SalesLT.Customer, @ControlVersionNo) AS CT
WHERE CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('SalesLT.Customer'), 'SalesPerson', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) = 1

Result 6a – Shows the results based on SalesPerson updates with the marked context.

Step 7 – Return Changed Rows From Original Table

I will show 3 variations of queries retrieving only the rows that the SalesPerson was updated. Again using CHANGETABLE (CHANGES) and CHANGE_TRACKING_IS_COLUMN_IN_MASK(). This time adding a Join to the original table SalesLT.Customer returning the Rows that changed. For this we will use the Version Numbers values from Updates in Step 4.

 --1. Return All Updates since the target Version!
DECLARE @ControlVersionNo int = 107
SELECT CT.SYS_CHANGE_VERSION, C.*
FROM CHANGETABLE (CHANGES SalesLT.Customer, @ControlVersionNo) AS CT
   inner join SalesLT.Customer C on C.[CustomerID] = CT.[CustomerID];
GO
 
--2. Return All Updates since the target Version only where the SalesPerson value changed!
DECLARE @ControlVersionNo int = 107
SELECT CT.SYS_CHANGE_VERSION, C.*
FROM CHANGETABLE (CHANGES SalesLT.Customer, @ControlVersionNo) AS CT
   inner join SalesLT.Customer C on C.[CustomerID] = CT.[CustomerID]
WHERE CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('SalesLT.Customer'), 'SalesPerson', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) = 1
 and CT.SYS_CHANGE_OPERATION = 'U'; --Updates Only
GO
 
--3. Return All Updates since the target Version only where the SalesPerson value changed and the SYS_CHANGE_CONTEXT= '3nd Update - SalesPerson'!
DECLARE @ControlVersionNo int = 107
SELECT CT.SYS_CHANGE_VERSION
      ,CAST(CT.SYS_CHANGE_CONTEXT AS varchar(128))
      , C.*
FROM CHANGETABLE (CHANGES SalesLT.Customer, @ControlVersionNo) AS CT
   inner join SalesLT.Customer C on C.[CustomerID] = CT.[CustomerID]
WHERE CT.SYS_CHANGE_CONTEXT = CAST('3nd Update - SalesPerson' AS varbinary(128))
 and CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('SalesLT.Customer'), 'SalesPerson', 'ColumnId'), CT.SYS_CHANGE_COLUMNS) = 1
 and CT.SYS_CHANGE_OPERATION = 'U'; --Updates Only
 GO

Note: Again, in this example I pass the target change version number to the CHANGETABLE function.

Result 7a – Only showing the Results of the 3^rd Query, filtering on SalesPerson changes and SYS_CHANGE_CONTEXT.

Wrap Up

Change Tracking is a nice light weight feature that can be turned on and implemented across all tables without having to modify your schema. Enabling Track Column Updates is a great option if you want to identify records for which only specific columns are updated. Using Change_Tracking_Context provides a great way to mark records updated to easily identify them later. Also, remember that these functions are affected by the Change Tracking retention or Auto Clean up setting. Also, be sure to review my previous Change Tracking article to learn how to mark a record so it can be re-synchronized to a secondary system without changing the data values of the original record using CHANGE_TRACKING_CONTEXT.

Next Steps

Review my previous Change Tracking Article
Review Microsoft Documents on Change Tracking
Read about Using Change Tracing in SQL Server 2008
Find other articles on Change Tracking – MSSQLTips Search

About the author

Jim Evans is an IT Manager currently for Crowe who has managed DBA, Developer, BI and Data Management teams for over 20 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-02-24