Microsoft® SQL Server® 2012 Bible

945

CHAPTER

41

Data Change Tracking

and Capture

IN THIS CHAPTER

Leveraging the T-Log

Synchronizing Data Using Standard Edition

Building Solutions for High-end B/I ETL

A

ll Change Tracking does is say to the world: “This row was changed; here’s the PK.” Clean and

simple, no fuss, no muss. It’s easy to confi gure and query.

Although Change Data Capture is limited to only the Enterprise Edition, Change Tracking is

available in all the SQL Server editions, even SQL Server Express.

Change Tracking occurs synchronously within the transaction. It simply records in an internal

table the primary key values of the rows that are modifi ed. Although there’s a performance cost to

recording the changes within the transaction, it means that SQL Agent is not required.

Optionally, Change Tracking can store which columns were changed, using a bit-mapped method

similar to how triggers know which column was included in the DML code.

The real purpose of Change Tracking is to support synchronization (not auditing). By easily and

reliably recording the primary keys of which rows were inserted, updated, or deleted since the last

synchronization, it becomes much simpler to perform the synchronization.

Change Tracking returns the net changes. If a row is inserted and updated since the last synchro-

nization, then Change Tracking lists it as an insert and inserts the latest version of the row. If the

row is inserted and deleted, then it won’t even show in the Change Tracking results — which is per-

fect for applications that need synchronization.

Several applications can benefi t from using Change Tracking:

■ (^) Microsoft Synch Framework
■ Caching data in middle tiers for performance
■ (^) Synchronizing occasionally connected mobile applications
c41.indd 945c41.indd 945 7/31/2012 10:17:18 AM7/31/2012 10:17:18 AM
http://www.it-ebooks.info