Database Administration Broken_old_clock____by_N3kronomisT

Published on December 7th, 2014 | by Warner Chaves


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:

  1. Enable the feature at the database level
  2. Enable the feature on a table level

Here’s the T-SQL for the example we will use in this post:


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

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;


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;


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

Then take care of Updates:

UPDATE ClientHoursTracked
HoursTracked ht inner join
CHANGETABLE(CHANGES HoursTracked, @syncVersion) ct
ON ht.RecordId=ct.RecordId inner join
ClientHoursTracked cht
ON ht.RecordId=cht.RecordId

Finally, take care of any DELETES:

DELETE FROM ClientHoursTracked
WHERE RecordId in
(SELECT RecordId from CHANGETABLE(CHANGES HoursTracked, @syncVersion) ct

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!

Tags: , , ,

About the Author

SQL Server Certified Master and MVP. DBA for MS technologies for over 10 years.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Back to Top ↑
  • Sign Up For Our Newsletter

    Stay up to date on the latest from SQL Server products and Features.

    Sign Up Now

    Sign up for mailing list and receive new posts directly to your inbox.

    We promise never to share or sell any of your personal information.

  • Recent Posts

  • Categories

  • Archives

  • Tags

  • Topics

  • Subscribe to our RSS Feed A SQL Server Web Blog
    Canada, Ontario, Ottawa.

    The Authors