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.
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:
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();
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:
The version number is critical to querying ChangeTable (explained ...