SQL Server 2008 introduced a powerful and an efficient way to track the DML changes, know as Change Tracking(CT) and Change Data Capture (CDC). Both of them seem to be synonyms but they do have differences, making them useful in different scenarios. Lets try to understand what CT and CDC are.
Change Tracking(CT) – Change Tracking when enabled for an application, informs that rows in a table were changed, but does not capture the data that was changed. That means it gives information on net change made to data, we can’t conclude how many rows were effected and their intermediate values. Therefore, change tracking is lightweight in nature and has less storage overhead, due to the limited historical data it provides and the intermediate changed data not being captured in comparison to change data capture (CDC).
Change Data Capture (CDC) – Change data capture provides much greater historical data of the change for a user table. Changes are captured and are maintained in separate change tables. The change table is a copy of the user table, along with additional columns that contain metadata. For each DML operation, new row/rows are added to the change table. In case of Insert operation one row with new data is added, delete operation adds one row with old data and updates adds two rows old and new data.
Here is the complete set of Similarities and differences between them.
- Both track DML changes
- Both track whether column data has changes or not
- Required to enable them on table as well as on database
Change Data Capture
|It tells only whether the data has been changed or not, but never captures the intermediate changed data.||It creates a change table, i.e a copy of the user table along with few additional metadata columns to track the changes along with intermediate changed values.|
|Tracking is done in a synchronous manner, as part of the transaction.||Tracking is done in an asynchronous manner i.e after the transaction|
|We can only enable it on a table which has a primary key in it.||No such restrictions to enable it.|
|Table can be truncated, when CT is enabled on the table.||Table can’t be truncated, when CT is enabled on the table.|
|We can turn off auto clean up.||We can’t turn off auto clean up.|
|Can only be enabled by SYSADMIN.||Can only be enabled by DBOwner.|
|Tracking is done with the help of temp db.||Tracking is done with the help of transaction log.|
|Works in all the editions of SQL Server.||Works only in Enterprise and DataCenter editions only.|
|Change Tracking is set up with ALTER DATABASE / ALTER TABLE.||Change Data Capture is configured using stored procedure.|
|Change Tracking does not need SQL Agent||Change Data Capture needs SQL Agent|
|Change Tracking has minimal overhead on the system.||It has almost nil impact as it asynchronous mechanism reads from the transaction log.|