Querying Change Tracking

When Change Tracking is enabled for a table, SQL Server begins to store information about which rows have changed. This data may be queried to select only the changed data from the source table — perfect for synchronization.

Version Numbers

Key to understanding Change Tracking is that Change Tracking numbers every transaction with a databasewide version number, which becomes important when working with the changed data. This version number may be viewed using a function:

SELECT Change_tracking_current_version();

Result:

0

The current version number is the number of the latest Change Tracking version stored by Change Tracking, so if the current version is 5, then there is a version 5 in the database, and the next transaction will be version 6.

The following code makes inserts and updates to the HumanResources.Department table while watching the Change Tracking version number:

INSERT HumanResources.Department (Name, GroupName) 
 VALUES ('CT New Row', ‘SQLPASS'),
     ('Test Two' , ‘SQLRally');   

SELECT Change_tracking_current_version();

Result:

1

The inserts added two new rows, with primary key values of DepartmentID 17 and 18.

And now an update:

UPDATE HumanResources.Department
 SET Name = ‘PASS Summit'
 WHERE Name = ‘CT New Row';

The update affected row DepartmentID = 17.

Testing the Change Tracking version shows that it has been incremented to 2:

SELECT Change_tracking_current_version();

Result:

2

The version number is critical to querying ChangeTable (explained ...

Get Microsoft SQL Server 2012 Bible now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.