This week I had to learn more about the Change Tracking (CT) and Change Data Capture (CDC) that was introduced in SQL Server 2008.
To learn more about the subject I read the 3 articles written by Chandra Sekhar.
Background: Prior to SQL Server 2008, developers had to create some custom tracking solutions using DML Trigger and additional tables to track the data which we have modified.
- DML Triggers: These are the part of our transaction which contains the DML by which it is triggered. As we all know that, triggers are very expensive and we are using them in our transaction, the execution time will increase so that the performance of our project will be affected.
- Additional tables: By running the above DML triggers, we are able to track the data. But there is nothing to store these changes. To store this changed data, we need to create these additional tables. These tables will have similar columns as the tables which we need to track.
- Takes much time to develop/create DML triggers and additional tables
- Performance hit
- Very complex process
SQL Server 2008To overcome the above drawbacks, SQL Server 2008 introduced powerful and efficient tracking mechanisms called Change Tracking (CT) and Change data Capture (CDC)
- 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
1 It tracks only whether the data has been changed or not. But never captures the modified values It captures values also 2 It follows synchronous tracking mechanism to track the data It follows asynchronous tracking mechanism which reads data changes from ldf file 3 It works in all the editions of SQL Server like Express, Workgroup, Web, Standard, Enterprise, DataCenter It works only in Enterprise and DataCenter editions 4 Does not hold historical data Holds historical data 5 No need to access ldf file of that particular database Needs to access ldf file of that particular database 6 Uses temp db Uses Transaction log (ldf file) 7 Returns less information regarding the changes in the data Returns more information compared to CT 8 Little bit difficult to get the full data of the table for which we have to join CHANGETABLE with the table based on primary key We can query directly from tables 9 SQL Server agent is not required to track the data SQL Server Agent should be enabled to capture the data 10 We can enable it on a table when the table has primary key on it No restriction as it is in the CT 11 User can truncate the table when it is enabled on the table User can not truncate when it is enabled on the table 12 We can turn off auto clean up We can’t turn off 13 Only SYSADMIN can enable CT Only DBOwner can enable CDC