Published on December 7th, 2014 | by Warner Chaves0
Practical Intro: SQL Server Table Change Tracking
On our “Practical Intro” posts we’re going to focus on a feature of SQL Server and provide the code, examples and scripts to get started. For this post, we’ll be focusing on Table Change Tracking.
Change tracking was introduced in SQL 2008 and is available in all Editions, all the way from Express to Enterprise. It allows to track what rows have changed on a table and what the change was, through a set of simple to use functions and tracking tables.
When should you use it?
Here are the use cases for Change Tracking:
- Your scenario consists of a very simple sync scenario where a full replication setup is too much overhead.
- You want to publish data from a SQL Server source that doesn’t support being a replication publisher. For example, a SQL Server Express table.
- You want to consume the data from a target that can’t be setup as a replication subscriber. This can be an application, another RDBMS platform, etc. As long as the client can submit a query, it can consume the result of Change Tracking.
What is the difference between Change Tracking (CT) and Change Data Capture (CDC)?
This is a common question that we get, not only because the functionality is similar but also the two features have such similar names!
Here are the main differences:
- Change Data Capture is an Enterprise Edition feature whereas Change Tracking is available on all Editions.
- Change Data Capture tracks EVERY CHANGE done to a record. That means if a record has changed 1000 times, CDC will track all those changes, whereas CT will only provide the latest version of the record so that it can be synced back.
How to enable it?
There are two steps to enabling change tracking:
- Enable the feature at the database level
- Enable the feature on a table level
Here’s the T-SQL for the example we will use in this post:
ALTER DATABASE HR SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON)
Note the two settings:
- CHANGE_RETENTION: is the time that SQL Server will keep the information necessary to sync any clients.
- AUTO_CLEANUP: tells SQL Server whether to keep cleaning up the Change Tracking info or to leave it untouched.
On the table level:
use HR ALTER TABLE HoursTracked ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF)
Note the TRACK_COLUMNS_UPDATED setting. This will indicate if SQL Server will track not only what records have changed but also which columns. In this initial post we’ll leave it OFF.
Let’s see an example
Using the HoursTracked table mentioned above, let’s run through a scenario of the results and operations we can sync through Change Tracking.
First, let’s assume our application is syncing for the first time and we need to save the initial synchronization version:
declare @initialVersion bigint; set @initialVersion=CHANGE_TRACKING_CURRENT_VERSION(); SELECT @initialVersion
The result of this initial value is 0 since it’s the first version since we enabled Change Tracking.
Now let’s do some changes on the table:
UPDATE HoursTracked set TotalHours=TotalHours+1 where EmployeeId=1; INSERT INTO HoursTracked SELECT 1,12,2013,10 DELETE HoursTracked where EmployeeId=10;
Let’s check the current version again:
declare @syncVersion bigint; set @syncVersion=CHANGE_TRACKING_CURRENT_VERSION(); select @syncVersion
The result is now version 3 after the modifications we made.
Now let’s assume our initial application wants to see what has changed since our initial sync:
select ct.SYS_CHANGE_OPERATION, ct.RecordId from CHANGETABLE(CHANGES HoursTracked, 0) ct
And this is the result:
We can see that Change Tracking has identified the 3 RecordId values that were modified and also the type of change that happened for each.
Now what happens if we update a record marked as Insert:
UPDATE HoursTracked set TotalHours=TotalHours+1 where EmployeeId=1 and [Year]=2013 and [Month]=12;
If we were to run the sync with the initial 0 version, the result is still the same, since a client on version 0 would still need to do the INSERT for record 5001.
However, if we had an application that had done a more recent sync and for example was on version 3:
select ct.SYS_CHANGE_OPERATION, ct.RecordId from CHANGETABLE(CHANGES HoursTracked, 3) ct
Then Change Tracking is smart enough to tell us that we don’t need to do an INSERT, we need to do an UPDATE:
In this manner, every time the application syncs it needs to save the sync version, use it the next time it syncs and continue this process. Here’s an example code to modify a client table based on the changes (error handling omitted). Remember the @syncVersion value would contain the value captured on the previous sync.
First take care of Inserts:
INSERT INTO ClientHoursTracked SELECT EmployeeId, [Year],[Month],TotalHours FROM CHANGETABLE(CHANGES HoursTracked, @syncVersion) ct INNER JOIN HoursTracked ht on ct.RecordId=ht.RecordId WHERE ct.SYS_CHANGE_OPERATION='I'
Then take care of Updates:
UPDATE ClientHoursTracked SET [Year]=ht.[Year], [Month]=ht.[Month], TotalHours=ht.TotalHours FROM HoursTracked ht inner join CHANGETABLE(CHANGES HoursTracked, @syncVersion) ct ON ht.RecordId=ct.RecordId inner join ClientHoursTracked cht ON ht.RecordId=cht.RecordId WHERE ct.SYS_CHANGE_OPERATION='U'
Finally, take care of any DELETES:
DELETE FROM ClientHoursTracked WHERE RecordId in (SELECT RecordId from CHANGETABLE(CHANGES HoursTracked, @syncVersion) ct WHERE ct.SYS_CHANGE_OPERATION='D')
And that’s it for our initial introduction. In our next part we’ll go through some of the internals of Change Tracking and tracking specific column changes. Stay tuned!