What is Change Data Capture (CDC) in SQL Server?
Quick Answer
CDC records every insert, update and delete made to a table by reading the transaction log, and exposes them as queryable change tables — without writing triggers. It's used to feed a downstream system (a warehouse, a search index, a cache) with exactly what changed since the last sync.
CDC reads the transaction log in the background and populates change tables with every insert, update and delete on a tracked table, including the actual before/after column values. It's an alternative to writing triggers for auditing or for feeding downstream systems, and because it reads the log asynchronously it adds much less overhead to the original transaction than a trigger would. I've used it for incremental loads into a data warehouse — instead of reloading the whole table nightly, the ETL job just asks CDC for everything since the last LSN it processed. There's a lighter sibling called Change Tracking that only tells you which rows changed, not the values, when you don't need that detail.
- Enable per-database (
sys.sp_cdc_enable_db) then per-table; a capture job reads the log asynchronously, so it doesn't slow down the original transaction the way a trigger can. - Query with
cdc.fn_cdc_get_all_changes_...between two log sequence numbers (LSNs). - Change Tracking is the lighter-weight sibling — it tells you which rows changed, not the before/after values; cheaper, less detail.
- Common use: incremental ETL into a warehouse without full reloads.