SQL Server CDC: Setup, Query, and Verify

2025.04.26

DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.

Quick answer: SQL Server Change Data Capture (CDC) reads committed INSERT, UPDATE, and DELETE activity from the transaction log and writes the row changes to relational change tables. Consumers query those tables through CDC table-valued functions, which makes CDC useful for incremental ETL and downstream synchronization without adding triggers to the source table.

Before you enable it: CDC must be enabled first at the database level and then for each source table. On SQL Server and Azure SQL Managed Instance, the capture process requires SQL Server Agent to be running. CDC is asynchronous, so a committed change may not appear immediately.

What is Change Data Capture?

In SQL Server, Change Data Capture (CDC) records committed row changes—INSERT, UPDATE, and DELETE—from the transaction log. A capture process copies the relevant values and metadata into a change table, and SQL Server creates table-valued functions that applications can use to read a valid LSN range.

CDC helps you with:

  • Incremental ETL loads instead of full table reloads
  • Sending ordered row changes to downstream systems
  • Retaining before-and-after values for updates when querying all changes

Important: CDC records database row changes, but it does not by itself identify the application user who made each change. If your audit requirement includes user identity or business context, capture that information separately.

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.
  • Change-history analysis
    → Review which row values changed and when they committed. CDC does not automatically record the application user responsible for the change.
  • 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.

Verify That CDC Is Working

Run these checks before consuming changes. They confirm that CDC is enabled at both levels and show the capture instance that determines the generated function name.

-- Database-level status
SELECT name, is_cdc_enabled
FROM sys.databases
WHERE name = DB_NAME();

-- Table-level status
SELECT
    s.name AS schema_name,
    t.name AS table_name,
    t.is_tracked_by_cdc
FROM sys.tables AS t
JOIN sys.schemas AS s
  ON s.schema_id = t.schema_id
WHERE s.name = N'dbo'
  AND t.name = N't1';

-- Capture-instance details
EXEC sys.sp_cdc_help_change_data_capture;

On SQL Server and Azure SQL Managed Instance, also verify the CDC jobs and their configuration:

EXEC sys.sp_cdc_help_jobs;

The capture job reads the transaction log asynchronously. The cleanup job enforces the configured retention period; the default is 4,320 minutes (three days). A consumer must process an LSN range that still falls between sys.fn_cdc_get_min_lsn and sys.fn_cdc_get_max_lsn.

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

Official References

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.

Continue learning: To strengthen the foundations used when consuming captured changes, browse Udemy and search for advanced T-SQL courses with exercises on queries, transactions, and data modification. For guided practice with relational database design and SQL, explore DataCamp.