Unlock the Power of Change Data Capture (CDC) in SQL Server


In this article, we will learn about Change Data Capture (CDC) in SQL Server. which records database activity when tables and rows are changed. Change data capture is often accessible in SQL Server, Azure SQL Managed Instance, and Azure SQL Database.

Change Data Capture (CDC) in SQL Server

CDC captures inserts, updates, and deletes activity on SQL tables. CDC contains a column structure that is the same as the column structure of the source table; in other words, it mirrors the column structure of the tracked source table along with the metadata required to understand changes done in the table's data.

CDC is for providing information about the DML (Data Manipulation Language) changes on the table and database. It helps us to remove expensive techniques like a trigger, timestamp column, and complex join queries.

To use CDC, it must be configured.

Setup & Configure CDC in SQL Server 

To use CDC, it must be enabled at the database level; by default, it is disabled. To allow CDC to, you must be a member of "SYSADMIN" (Fixed role of SQL Server). You can enable CDC only on a user Database, not a system database.

To determine whether a database is CDC-enabled, run the following T-SQL.

select name, is_cdc_enabled from sys.databases


To enable CDC on a database, use the system-stored procedure called "SYS.SP_CDC_ENABLE_DB."Execute the following T-SQL.

USE AdventureWorks

EXEC sys.sp_cdc_enable_db


The following CDC tables are created under the CDC schema.

  1. cdc.captured_columns
  2. cdc.change_tables
  3. cdc.ddl_history
  4. cdc.index_columns
  5. cdc.lsn_time_mapping

The next step is to enable CDC on a table to track changes in the table data.


USE AdventureWorks

EXEC sys.sp_cdc_enable_table  
@source_schema = 'dbo', 
@source_name = 'Customer',
 @role_name = 'cdc_Customer'


The sys.sp_cdc_enable_table system stored procedure has a few parameters.


While Enabling CDC will create certain stored produce, SQL job, and function.




Example of CDC

Perform a couple of insert statements on the "Customer" table.

Insert into Customer values('Jignesh Trivedi','Address 1','Address 2','Address 3','City 1','State 1')
Insert into Customer values('Tejas Trivedi','Address 1','Address 2','Address 3','City 3','State 4')

Now let us check the effect of the above query when run on the database.

To determine the change in table data, SQL has two functions.

  1. fn_cdc_get_net_changes_dbo_TableName.

  2. fn_cdc_get_all_changes_dbo_TableName.

And these functions have a couple of parameters.

Parameter Description
from_lsn LSN No that lowest or start result set.
to_lsn LSN No that highest or result set.
row_filter_option The Option governs the metadata column's content, and the row rows return in the result set.

Refer to MSDN for more information about the fn_cdc_get_net_changes_dbo_TableName function.


declare @begin_lsn binary(10), @end_lsn binary(10)
-- get the first LSN for customer changes

select @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer')
-- get the last LSN for customer changes

select @end_lsn = sys.fn_cdc_get_max_lsn()
-- get net changes; group changes in the range by the pk|

select * from cdc.fn_cdc_get_net_changes_dbo_customer(@begin_lsn, @end_lsn, 'all');
-- get individual changes in the range

select * from cdc.fn_cdc_get_all_changes_dbo_Customer(@begin_lsn, @end_lsn, 'all');


To determine the difference between the above two functions, do some more DML on the "Customer" table.

UPDATE Customer
SET AddressLine1 = 'Address 5'

WHERE CustomerID = 1
Insert into Customer values('Rakesh Trivedi','Address 4','Address 4','Address 4','City 4','State 4')
DELETE Customer WHERE CustomerID = 3

Now we run the above two functions.


Function "fn_cdc_get_all_changes_dbo_TableName" contains all changes from LSN To LSN. And function "fn_cdc_get_net_changes_dbo_TableName" has only net changes on the table.


This article taught us about Change Data Capture (CDC) in SQL Server. For reference, Continue learning about Change Tracking in SQL Server.

Similar Articles