SQL Server Change Data Capture

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

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

View table Information in Object Explorer 1

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

Records inside a Employee Table 2

Execute SQL Server Change Data Capture

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

Implement SQL Server CDC on Database

Below code snippet will enable SQL CDC on the database level.

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.

Implement on Database Table4

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

SQL Server Change Data Capture System stored Procedures list 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.

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 Change Dat Capture CDC on the Employee table.

Suppose you go to the 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 Audit Information Table 7

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

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 CDC record information 9

Next, we will perform an update using the UPDATE Statement

UPDATE [dbo].[Employee]
    SET [FirstName] = 'Dave'
    WHERE [EmpID] = 3
Messages
-------
(1 row(s) affected)

Let me show you the Employee table

Records inside Normal Employee Table 11

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

SQL Server Change Data Capture 12

Lastly, we will perform a Delete operation using DELETE Statement

DELETE FROM [dbo].[Employee]
	WHERE [EmpID] = 4
Messages
-------
(1 row(s) affected)

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

Deleting record from enabled table 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 must follow the same procedure to disable the Change Data Capture 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 CDC change data capture.

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.

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 Change Data Capture on Database

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

EXEC sys.sp_cdc_disable_db
Messages
-------
Command(s) completed successfully.

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

Removes generated system tables 20

Enable CDC on multiple Columns

Instead of enabling 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.

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.

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 only captures information about the Emp Id and First Name.

SQL Server Change Data Capture 23

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

SQL Server Change Data Capture 16
Categories SQL