SQL Server 2008:CDC and change tracking

There is often a need to record data changes caused by DML operations (Insert/updae/delete) in the user table, and in previous versions of SQL Server 2008, such functionality could only be achieved through trigger or data alignment (for example, SCD processing). and must be developed for each user table. There are two new features for recording data changes in SQL Server 2008, and this article makes a brief comparison of the features of the change data Capture (CDC) and changing tracking.

Change Data Capture

The CDC records data changes in the time, type, and actual impact of a DML operation by asynchronously reading the transaction log, and then logs the data to a table that is automatically created when the CDC is enabled. Detailed data changes can be obtained through CDC-related stored procedures. Because the data change is read asynchronously, it has little effect on the overall performance, much less than the data change record implemented by trigger.

The following figure is from SQL Server Books Online, which explains how the CDC can be used to get changes within different time periods.

Change tracking

Unlike change Data capture,change tracking only records the occurrence time, type, and affected fields of a DML operation, does not contain a specific variable value, and the client obtains the change record from the last sync to the current by passing the last synchronized version number. For applications where data caching is applied, this is a useful feature that allows you to get a record of the changes in the datasheet at any given time, and then refresh the cache with the updated data based on the primary key in the change record.

The change tracking gets the variance record by analyzing the DML statement to be executed, rather than reading the log. The change tracking response is faster than the change Data capture when the DML statement commits execution and the change tracking is available without waiting for the transaction log write to be available after the DML completes.

The change tracking records less data than the capture, and has little impact on server performance.