A couple of months ago I needed to write an application that synchronizes data between systems. After the initial sync which were quite a few rows I didn’t want to sync the complete database every few days so I needed another solution. Then i saw a colleague using SQL server change tracking. I didn’t know what it was so I decided it might fit my needs and I needed to figure it out. So let’s get to it

So we are starting with a brand new database containing users. As you might see I used a couple of familiar names.

This is the table/data we are going to use for this example. We need to enable change tracking in the database properties or in the table. The SQL shown below enables it in the table.

ALTER TABLE Users
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON)  

This enables a feature in SQL server that tracks each change to row(s) with a specific verion number. So lets change the data and see that it works:

A explained earlier SQL server tracks all the changes with a version number. The initial version was ‘0’ so lets find out the new version number:

DECLARE @next_baseline bigint;  
SET @next_baseline = CHANGE_TRACKING_CURRENT_VERSION();  
SELECT @next_baseline

This results in a version of ‘1’ but now we need to know what changed in this version. So first we need to store the previous version and query it through the CHANGETABLE statement.

DECLARE @last_sync_version bigint;  
  SET @last_sync_version = 0
SELECT u.FirstName, u.LastName, c.Id,  
    c.SYS_CHANGE_VERSION, c.SYS_CHANGE_OPERATION
FROM CHANGETABLE (CHANGES Users, @last_sync_version) AS c  
    LEFT OUTER JOIN Users AS u  
        ON U.Id = c.Id  

This results in the output below:

FirstName     LastName	       Id	SYS_CHANGE_VERSION	SYS_CHANGE_OPERATION
Scott2        Hanselman2        1        0                      U

A little explanation about the values:

So when you want to use this in a program you have to:

  • Get the latest version from the table itself and passing the last sync version as parameter to the query displayed earlier.
  • After executing you know what has changed and then you can synchronize the data
  • Store the last synched version somewhere persistant

Links for reference:

Photo by panumas nikhomkhai from Pexels

Leave a Reply

Your email address will not be published. Required fields are marked *