SQL Server › Change Data Capture (CDC)

What is Change Data Capture in SQL Server, and what problem does it solve?

Beginner

Answer

Change Data Capture, often called CDC, automatically records insert, update, and delete activity on a table into a separate set of change tables, letting other systems or processes easily identify exactly what data has changed since the last time they checked, without needing to compare entire tables manually.

EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'Orders',
  @role_name = NULL;

Real-world example A data warehouse team enables CDC on their orders table so their nightly synchronization process only needs to pull the handful of orders that actually changed that day, instead of reprocessing the entire orders table every single night.

Common follow-ups: What is the difference between CDC and Change Tracking?;Does enabling CDC require any changes to the application code that writes to the table?

Replication;Query Optimization & Plans