What is Change Data Capture?

In SQL Server, Change Data Capture (CDC) is a feature that automatically captures and records changes (INSERT, UPDATE, DELETE) made to table data.

Unlike triggers or manual log monitoring, CDC reads from the transaction log.
This allows capturing data changes without directly impacting applications or the source tables.

CDC helps you with:

• Tracking data change history
• Handling only delta (changed) data during ETL processes
• Detecting data changes for replication or auditing purposes

Use Cases for Change Data Capture

Change Data Capture is commonly used in the following scenarios:

• Delta loading into a Data Warehouse (DWH)
  → Instead of full reloads, only changes are extracted and loaded efficiently.
• Audit log generation
  → Track who changed what and when.
• System integrations (such as ETL tools)
  → Synchronize data between systems in near real-time using CDC.
• Batch processing optimization
  → Only process records that have actually changed, saving time and resources.

How to Set Up Change Data Capture

To use CDC in SQL Server, you need to follow these steps:

① Enable CDC at the database level

USE [YourDatabaseName];
EXEC sys.sp_cdc_enable_db;

② Enable CDC on a specific table

USE [YourDatabaseName];
EXEC sys.sp_cdc_enable_table  
    @source_schema = N'dbo',  
    @source_name = N'YourTableName',  
    @role_name = NULL;  -- (Optional) Role for access control

After this, CDC will automatically start recording changes for the specified table.

How to Use Change Data Capture

You can retrieve the captured changes using built-in system functions.

For example, to get changes between two points in time:

DECLARE @from_lsn binary(10), @to_lsn binary(10);

-- Get the starting and ending LSN (Log Sequence Number)
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_YourTableName');
SET @to_lsn = sys.fn_cdc_get_max_lsn();

-- Retrieve the changes
SELECT *  
FROM cdc.fn_cdc_get_all_changes_dbo_YourTableName(@from_lsn, @to_lsn, 'all');

Key Points:

• fn_cdc_get_all_changes_YourTableName returns all changes (Insert/Update/Delete).
• fn_cdc_get_net_changes_YourTableName returns only the final net changes (e.g., for each primary key).

Summary

Change Data Capture (CDC) is a feature in SQL Server that allows you to track changes efficiently without heavy application overhead.
It is especially useful for data integration, real-time synchronization, auditing, and ETL processes.