Microsoft® SQL Server® 2012 Bible

950

Part VII: Monitoring and Auditing

Result (abbreviated):

table ChangeTracking

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

HumanResources.Department sys.change_tracking_757577737

Armed with the name, it’s easy to fi nd the disk space used. Because Change Tracking was

just enabled in this database, the internal table is still empty:

EXEC sp_spaceused 'sys.change_tracking_757577737'

Result:

name rows reserved data index_size unused

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

change_tracking_757577737 0 0 KB 0 KB 0 KB 0 KB

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();

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

http://www.it-ebooks.info