Change Data Capture vs Change Tracking

on June 23, 2010

Here is a quick taxonomy of Change Data Capture and Change Tracking. I’m going to keep this short, but if you really want all the details, skip to the links at the bottom for complete information.

Change Tracking (CT)

Change Tracking is a synchronous mechanism which modifies change tracking tables as part of ongoing transactions to indicate when a row has been changed. It does not record past or intermediate versions of the data itself, only that a change has occurred. It is recommended to use snapshot isolation with Change Tracking! (See the links below for details on why.)

Change Data Capture (CDC)

Change Data Capture is asynchronous and uses the transaction log in a manner similar to replication. Past values of data are maintained and are made available in change tables by a capture process, managed by the SQL Agent, which regularly scans the T-Log. As with replication, this can prevent re-use of parts of the log.

If you suffer from excessive log reuse waits, you can post a comment on the SQLAgentMan blog and ask for your log back.

Comparing Editions, Methodology, and Notes…

The following recap is largely a paraphrasing of the prior two paragraphs, with some Edition info thrown in:

Feature	Change Tracking	Change Data Capture
Supported Editions	Express, Workgroup, Web, Standard, Enterprise,	Enterprise
Summary	Tracks when a row has changed. Which columns changed can also be tracked.	Tracks when data has changed and includes the values as well. Entire table or subset of columns can be captured.
Methodology	Synchronous with DML, records change tracking info as part of transaction	Asynchronous from Transaction Log
Recommendations	Use with snapshot isolation. Note: places some limitations on DML (see links below)	Use esp for large scale data warehouse applications. Note: can be used in a DB which is also a replication publisher if need be.