Microsoft SQL Server 2012 Bible

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 ...