SQL Server – Comparison between CT and CDC

Please visit SQLVERSITY.COM for more details.

If you have already gone through my previous articles, Change Tracking (CT) and Change Data Capture (CDC), you also need to know the comparison between CT and CDC.

When I was writing articles on CT and CDC, I noticed some similarities and differences between CT and CDC.

Although they look similar, I noticed more differences than similarities. So I would like to share my findings with you guys.

Similarities:

• Both Track DML changes
• Both Track whether column data has changes or not
• Required to enable them on table as well as on database

Differences:

S.No.	Change Tracking	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

I am very happy to say we are completely done with CT and CDC. I have not decided about the next article yet 🙂

Very soon I will be back with a nice & interesting article.

ALL THE BEST