SQL Server › Change Data Capture (CDC)

How do you query the change data captured for a table to find out exactly what changed within a specific time range?

Intermediate

Answer

You use the cdc.fn_cdc_get_all_changes or cdc.fn_cdc_get_net_changes table valued functions, passing in the starting and ending log sequence numbers that correspond to your desired time range, which returns every captured change along with an indicator showing whether each row was inserted, updated, or deleted.

DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_Orders');
DECLARE @to_lsn binary(10) = sys.fn_cdc_get_max_lsn();

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from_lsn, @to_lsn, 'all');

Real-world example An integration process pulls all order changes from the past hour using CDC's change functions, feeding only the actual modified records into a downstream reporting system instead of reprocessing the whole orders table.

Common follow-ups: What do the different operation codes in the CDC result set actually mean?;How do you convert a specific date and time into the log sequence number CDC functions require?

Query Optimization & Plans;Data Types & Schema Design