O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required