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?
- Both needs to be enabled on per database basis
- Both needs to be enabled on per table basis to track changes in that particular table
- Both create system tables where the change information is recorded
- Both can track whether a column was changed
How are they dissimilar?
- 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)
- 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.
- CT adds a hidden column to the table being tracked to uniquely identify the rows. CDC doesn’t do that.
- CT records changes synchronously. CDC records asynchronously.
- CT uses TempDB. CDC uses transactional log.
- CT doesn’t need SQL Agent. CDC needs SQL Agent.
- 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…