Microsoft® SQL Server® 2012 Bible

951

Chapter 41: Data Change Tracking and Capture

41

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 in the next section),

and it must be within the range of the oldest possible version number for a given table and

the current database version number. The old data is probably being cleaned up automati-

cally, so the oldest possible version number will likely vary for each table.

The following query can report the valid version number range for any table. In this case, it

returns the current valid queryable range for HumanResources.Department:

SELECT

Change_tracking_min_valid_version

(Object_id(N'HumanResources.Department')) as 'oldest',

Change_tracking_current_version() as 'current';

Result:

oldest current

-------------------- --------------------

0 2

Changes by the Row

Here’s where Change Tracking shows results. The primary keys of the rows that have been

modifi ed after a given version number can be found by querying the ChangeTable table-

valued function, passing to it the Change Tracking table and a beginning version num-

ber. For example, passing table XYZ and version number 10 to ChangeTable returns the

changes for version 11 and versions following that were made to table XYZ. Think of the

version number as the number of the last synchronization, so this synchronization needs

all the changes after the last synchronization.

c41.indd 951c41.indd 951 7/31/2012 10:17:20 AM7/31/2012 10:17:20 AM

http://www.it-ebooks.info