Hi Friends,

I spent last weekend reading about Change Tracking (CT). I have worked with Change Data Capture (CDC) a couple of times but really never got the right scenario to implement Change Tracking.

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.

You are welcome add more similarities and dissimilarities in the comments section below.

Now, let me put out an obvious question; what are the right usage scenarios for CT & CDC? Hope to see some answers…