Understanding Change Data Capture(CDC) in SQL Server

2025.04.26

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:

①  Create database and tables

CREATE DATABASE [cdcDB]
GO

USE [cdcDB]
GO

CREATE TABLE t1 (
id INT PRIMARY KEY,
c1 NVARCHAR(50),
c2 DATETIME
);

② Enable CDC at the database level

Enable Change Data Capture at the database level using the following command:
This will create the necessary system tables and the cdc schema required for CDC to function.

USE [cdcDB]
GO
EXEC sys.sp_cdc_enable_db;

③ Enable CDC on the target table

Now enable CDC on the table t1. This will create an associated change tracking table to store changes made to t1.

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N't1',
    @role_name = NULL;

Once this command is executed:

  • A capture instance (cdc.dbo_t1_CT) will be created to store change data.

  • System functions and metadata will also be created to allow querying of CDC data.

How to Use Change Data Capture

Once CDC is enabled on your table, you can retrieve the captured changes using SQL Server’s built-in system functions. Here’s how to do it step by step.

① Make Some Changes to the Source Table

Let’s insert, update, and delete a row in the t1 table to simulate real-world data changes:

-- INSERT a new row
INSERT INTO t1 (id, c1, c2) VALUES (1, 'initial', GETDATE());

-- UPDATE the inserted row
UPDATE t1 SET c1 = 'modified', c2 = DATEADD(HOUR, 1, c2) WHERE id = 1;

-- DELETE the row
DELETE FROM t1 WHERE id = 1;

These operations are now tracked in the CDC change table automatically.

② Retrieve the Change History with LSN

To read the captured changes, we need to define the range of time (actually, LSN: Log Sequence Number).

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

-- Get the earliest and latest LSN for the capture instance
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_t1');
SET @to_lsn = sys.fn_cdc_get_max_lsn();

SELECT @from_lsn AS from_lsn, @to_lsn AS to_lsn;

③ Query All Changes with CDC Function

Now that we have the LSN range, we can retrieve all changes (inserts, updates, deletes) using the function cdc.fn_cdc_get_all_changes_dbo_t1.

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

-- Get the earliest and latest LSN for the capture instance
SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_t1');
SET @to_lsn = sys.fn_cdc_get_max_lsn();

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

This function returns a detailed list of every change that occurred within the specified LSN range.

Key Points to Know :

  • 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).

  • The returned result includes system columns like __$operation:

    • 1 = Delete

    • 2 = Insert

    • 3 = Update (before image)

    • 4 = Update (after image)

④ Retrieve Changes by Specific Timestamp

In many real-world scenarios, you might want to retrieve change data based on a specific date and time—for example, all changes that occurred after the last data sync. Since CDC tracks changes using LSNs (Log Sequence Numbers) rather than timestamps, we need to first convert a datetime to its corresponding LSN.

Here’s how you can retrieve all changes that happened after a given timestamp:

DECLARE @from_time DATETIME;
DECLARE @from_lsn BINARY(10);
DECLARE @to_lsn BINARY(10);

-- Set from_time to midnight of one day ago
SET @from_time = DATEADD(DAY, -1, CAST(GETDATE() AS DATE)); -- e.g. if today is 2025-07-02, this becomes 2025-07-01 00:00:00

-- Convert timestamp to corresponding LSN
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @from_time);
SET @to_lsn = sys.fn_cdc_get_max_lsn();

-- Check for null LSN and retrieve data if valid
IF @from_lsn IS NOT NULL
BEGIN
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_t1(@from_lsn, @to_lsn, 'all');
END
ELSE
BEGIN
PRINT 'No CDC changes available for the specified time range.';
END

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.