Change Tracking vs Change Data Capture

CT, like CDC tracks changes to a particular table in a particular database. In many ways, they are similar but their dissimilarities are what will interest you. So here is a quick look in to the similarities and dissimilarities:

How are they similar?

1.        Both needs to be enabled on per database basis

2.        Both needs to be enabled on per table basis to track changes in that particular table

3.        Both create system tables where the change information is recorded

4.        Both can track whether a column was changed

How are they dissimilar?

1.        CT only tracks whether a change has been done or not; does not track the data values (old and new). CDC also tracks the data values (old and new)

2.        CT only gives you the last change (final change) in case a series of changes has been done to the data. CDC gives you the complete history of the changes.

3.        CT adds a hidden column to the table being tracked to uniquely identify the rows. CDC doesn’t do that.

4.        CT records changes synchronously. CDC records asynchronously.

5.        CT uses TempDB. CDC uses transactional log.

6.        CT doesn’t need SQL Agent. CDC needs SQL Agent.

7.        CT is available on lower editions. CDC is only available in Enterprise Edition & Higher.