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:
- https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/changetable-transact-sql?view=sql-server-2017
- https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server?view=sql-server-2017
Photo by panumas nikhomkhai from Pexels