Comparing Change Data Capture and Change Tracking

How Change Tracking On is different from CDC?

Comparing Change Data Capture and Change Tracking

• Change Tracking is a light-weight synchronous process designed to have very minimal impact on DML operations whereas Change Data Capture is an asynchronous process which reads the transaction log asynchronously in the background (also minimal impact on the system) to track and record the DML changes.
• Since Change Tracking is a synchronous process, change information is available immediately after a commit whereas since Change Data Capture is an asynchronous process there might be some delay in recording.
• Change Tracking only stores the fact that some DML operations have happened (no recording of changed data) whereas Change Data Capture stores both the fact that DML operations have happened and also its related data along with complete history.
• Change Tracking has far less storage requirements than Change Data Capture.
• Change Tracking does not require SQL Server Agent to be running whereas Change Data Capture requires SQL Server Agent to be running.

·SQL Server 2008

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. 

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.

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

See Also