Home » Articles » SQL Server – Comparison between CT and CDC

SQL Server – Comparison between CT and CDC

Our Categories

Blog Stats

  • 32,852 hits

Calendar

January 2013
M T W T F S S
    Feb »
 123456
78910111213
14151617181920
21222324252627
28293031  

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:

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: