SQL Server Change Data Capture

SQL Server Change Data Capture, shortly called SQL Server CDC, is used to capture the changes made to the SQL table. For example, if you want to store the audit information about the UPDATE, INSERT, DELETE operations, enable the SQL CDC on that table.

In this chapter, we will explain to you, How to enable SQL Server CDC, disable CDC, and enable CDC on individual columns with example. For this SQL Server Change Data Capture demonstration, we are going to use the CDC Testing Data in our SQL Server. As you can see from the below screenshot, it has only one table, i.e., Employee.

SQL Server Change Data Capture 1

And the below screenshot will show you the data inside the Employee table.

SQL Server Change Data Capture 2

Execute SQL Server Change Data Capture

Implementing the SQL Server change data capture is a two-step approach. First, you have to enable SQL CDC on Database, and then you have to enable it on Table.

Implement SQL Server CDC on Database

Below code snippet will enable SQL CDC on database level.

--SQL Server CDC Example
USE [CDC Testing]
GO
EXEC sys.sp_cdc_enable_db
Messages
--------
Command(s) completed successfully.

If you observe the below screenshot, enabling SQL change data capture automatically created six tables inside the system tables.

SQL Server Change Data Capture 4

and also some system stored procedures to implement the change data capture in Sql Server

SQL Server Change Data Capture 5

Implement SQL Server CDC on Table

Below code snippet will enable SQL Change Data Capture on the table level. Here it is the Employee table.

--SQL Server CDC Example
USE [CDC Testing]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo', -- Schema Name. Example, HR, Sales etc
@source_name = N'Employee', -- Table Name
@role_name = NULL -- Any specified roles
GO
Messages
-------
Job 'cdc.CDC Testing_capture' started successfully.
Job 'cdc.CDC Testing_cleanup' started successfully.

From the above SQL Server screenshot, you can see that we successfully implemented the CDC on the Employee table. Now, if you go to the SQL CDC Testing database. Under the System Tables, you will find the new table cdc.dbo.Employee_CT (in your case, Employee will replace with table name).

It is the table that will store the Audit information (or Capture the table changes)

SQL Server Change Data Capture 7

Let me insert a new row into the table using the INSERT Statement.

--SQL Server CDC Example
USE [CDC Testing]
GO
 
INSERT INTO [dbo].[Employee] (
	[FirstName], [LastName], [Occupation], [YearlyIncome])
VALUES ('Tutorial', 'Gateway', 'Admin', 10000)
Messages
-------
(1 row(s) affected)

If you check the dbo_Employee_CT table, you can see the record with operation number 2 (insert).

SQL Server Change Data Capture 9

Next, we will perform an update using UPDATE Statement

--SQL Server CDC Example
USE [CDC Testing]
GO
UPDATE [dbo].[Employee]
    SET [FirstName] = 'Dave'
    WHERE [EmpID] = 3
Messages
-------
(1 row(s) affected)

Let me show you the Employee table

SQL Server Change Data Capture 11

If you check the dbo_Employee_CT table, you can see the Old record with operation number 3 (value before the update), and new record with operation number 4 (updated).

SQL Server Change Data Capture 12

Lastly, we will perform a Delete operation using DELETE Statement

--SQL Server CDC Example
USE [CDC Testing]
GO
DELETE FROM [dbo].[Employee]
	WHERE [EmpID] = 4
Messages
-------
(1 row(s) affected)

You can from the below screenshot, there is no record with Emp Id 4

SQL Server Change Data Capture 14

If you check the dbo_Employee_CT table, you can see the deleted record with operation number 1 (deleted).

SQL Server Change Data Capture 15

Disable SQL Server Change Data Capture

You have to follow the same procedure to disable the CDC in SQL Server.

Disable SQL Server CDC on Table

If you know the capture instance (Schema.table_name), Please skip this step and apply the next code. The below-stored procedure will provide information about the SQL change data capture.

--SQL Server CDC Example
USE [CDC Testing]
GO
EXEC sys.sp_cdc_help_change_data_capture 
GO
SQL Server Change Data Capture 17

Below code snippet will disable SQL change data capture at the database level. And we need that capture_instance to disable it.

--SQL Server CDC Example
USE [CDC Testing]
GO
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@capture_instance = dbo_Employee
GO
Messages
-------
Command(s) completed successfully.

Disable SQL Server CDC on Database

After you disable CDC at table level, you have to disable SQL change data capture on the Database level. The below code will do the same.

--SQL Server CDC Example
USE [CDC Testing]
GO
EXEC sys.sp_cdc_disable_db
Messages
-------
Command(s) completed successfully.

Now you can see, all the system generated (CDC generated system tables) are gone.

SQL Server Change Data Capture 20

Enable SQL Server CDC on multiple Columns

Instead of enabling SQL Change Data Capture on the whole table, SQL Server allows you to enable CDC on individual columns. In this example, we will enable CDC on Emp ID, and First Name columns

--SQL Server CDC Example
USE [CDC Testing]
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'Employee',
@role_name = NULL,
@captured_column_list = '[EmpID], [FirstName]'
GO
Messages
-------
Job 'cdc.CDC Testing_capture' started successfully.
Job 'cdc.CDC Testing_cleanup' started successfully.

Let me insert a new record into the employee table.

--SQL Server CDC Example
USE [CDC Testing]
GO
 
INSERT INTO [dbo].[Employee] (
	[FirstName], [LastName], [Occupation], [YearlyIncome])
VALUES ('SQL', 'Server', 'Tutorial', 20000)
Messages
--------
(1 row(s) affected)

As you can see from the below screenshot, we inserted a new record for First name, last name, occupation, and yearly income. However, it is only capturing information about the Emp Id, and First Name.

SQL Server Change Data Capture 23

Before we close this SQL Server Change Data Capture article let me show you the data inside the Captured_Columns. As you can see, it stored all the columns that are captured by the SQL Server change data capture.

SQL Server Change Data Capture 16