Comparing Change Data Capture and Change Tracking
SQL Server 2008 introduces two tracking features that enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Before these features were available, custom tracking mechanisms had to be implemented in applications. These custom mechanisms often required schema changes to the tracked table or the use of triggers. Neither change data capture nor change tracking requires any schema changes at the source or the use of triggers.
Change Data Capture
Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system..
As shown in the following illustration, the changes that were made to user tables are captured in corresponding change tables. These change tables provide an historical view of the changes over time. The change data capture functions that SQL Server provides enable the change data to be consumed easily and systematically.
.gif "Conceptual illustration of change data capture")
Change Tracking
Change tracking captures the fact that rows in a table were changed, but does not capture the data that was changed. This enables applications to determine the rows that have changed with the latest row data being obtained directly from the user tables. Therefore, change tracking is more limited in the historical questions it can answer compared to change data capture. However, for those applications that do not require the historical information, there is far less storage overhead because of the changed data not being captured. A synchronous tracking mechanism is used to track the changes. This has been designed to have minimal overhead to the DML operations.
The following illustration shows a synchronization scenario that would benefit by using change tracking. In the scenario, an application requires the following information: all the rows in the table that were changed since in the last time that the table was synchronized, and only the current row data. Because a synchronous mechanism is used to track the changes, an application can perform two-way synchronization and reliably detect any conflicts that might have occurred.
.gif "Conceptual illustration of change tracking")
Feature Differences Between Change Data Capture and Change Tracking
The following table lists the feature differences between change data capture and change tracking. The tracking mechanism in change data capture involves an asynchronous capture of changes from the transaction log so that changes are available after the DML operation. In change tracking, the tracking mechanism involves synchronous tracking of changes in line with DML operations so that change information is available immediately.
Feature |
Change data capture |
Change tracking |
|---|---|---|
Tracked changes |
|
|
DML changes |
Yes |
Yes |
Tracked information |
|
|
Historical data |
Yes |
No |
Whether column was changed |
Yes |
Yes |
DML type |
Yes |
Yes |