Microsoft SQL Server 2012 Bible

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

All 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 configure 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 modified. 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 synchronization, 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 perfect for applications ...