Articles → SQL SERVER → CDC In SQL Server

CDC In SQL Server

In this article, we will discuss CDC (Change Data Capture) in SQL server.

What Is CDC?

CDC is a feature in SQL server that records the insert, update and delete activity in a table.

Software Requirement

SQL server 2012 or above. Please note that CDC is not available in the SQL express edition.

Table

To test CDC, we will create a table "TestTable".

set ansi_nulls on 
go 
set quoted_identifier on 
go 
set ansi_padding on 
go 
create table [dbo].[testtable](
    [id] [int] identity(1, 1) not null, 
    [name] [varchar](50) null, 
    [address] [varchar](50) null, 
    constraint [pk_testtable] primary key clustered ([id] asc) with (
      pad_index = off, statistics_norecompute = off, 
      ignore_dup_key = off, allow_row_locks = on, 
      allow_page_locks = on
    ) on [primary]
  ) on [primary] 
go 
set ansi_padding off 
go

In "TestTable", we have 3 columns i.e., "id", "name" and "address".

Enabling CDC

The first step is to enable CDC at the database level. To accomplish this, we need to execute the following stored procedure.

exec sys.sp_cdc_enable_db  
GO

It is important to note that only a user having "sysadmin" role can execute the above command.

Once the above command is executed, "CDC" schema is created and some system defined tables are also created as shown in figure below

Picture showing CDC schema created after enabling CDC

Click to Enlarge

The next step is to enable CDC at the table level. To accomplish this, we need to execute following stored procedure.

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'TestTable',  
@role_name     = NULL,  
@supports_net_changes = 1  
GO

In the above command, we are enabling CDC for "TestTable". Once you execute the above stored procedure, a new table with the name "dbo_TestTable_CT" will be created. Please see the screenshot below.

Picture showing the tables created after CDC is enabled for TestTable

Click to Enlarge

Now let us investigate the schema of "dbo_TestTable_CT" table. See the screenshot below.

Picture showing the dbo_TestTable_CT table schema

Click to Enlarge

In the above schema, we have following columns: -

Meta columns – Columns from _@start_lsn to _$update_mask (except _$operation) used for CDC internally
_$operation column - Contains following values: -
1. 2 for insert
2. 3 for before update
3. 4 for after update
4. 1 for delete
"TestTable" table columns – all columns of "TestTable" are added in this table

Scripts

Now execute a script to insert, update and delete data in "TestTable"

-- Insert Query
Insert into dbo.TestTable Values('Karan', 'India')

-- Update Query
Update dbo.TestTable 
Set name = 'Sumit' where address = 'India'

-- Delete Query
delete from dbo.TestTable where address = 'India'

Output

After you run the scripts, you will see following data in "CDC.dbo_TestTable_CT table".

Picture showing the table data in CDC tables once CRUD operation is performed on TestTable

Click to Enlarge

Posted By -	Karan Gupta

Posted On -	Saturday, May 27, 2017

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**