How To Enable CDC On A Table In SQL Server

Recently, when I spoke to my client, they expected me to track and manage the data changes. So, as a DBA, I suggested to them why we can’t use the CDC feature of the SQL server, which we can enable at a database level and table level, etc. In this article, I will walk you through the steps to enable CDC on a database level and table name with practical examples and scenarios.

Change Data Capture (CDC) is an excellent feature in SQL servers that can help you track all the activities related to the insert, update, and delete operations applied to the tables in the SQL server.

How to enable CDC in SQL Server step by step

Before discussing this functionality, we should know the prerequisites needed here.

Check out How To Add A Column To A Table In SQL Server

Prerequisites

1. You must have sysadmin or db_owner permissions.
2. You must have SQL Server Enterprise, Developer, or Evaluation edition.
3. If you want to enable the CDC at the table level, you must first enable it in your database level.

Note that if your SQL server version is not Enterprise, Developer, or Evaluation edition, then if you try to enable the CDC, you will get the error message “This instance of SQL Server is the Express Edition (64-bit). Change data capture is only available in the Enterprise, Developer, Enterprise Evaluation, and Standard editions.” as shown in the screenshot below.

Enable CDC in SQL Server at the Database level.

You can execute the below SQL query to enable this feature at the database level.

USE Test;
GO
EXEC sys.sp_cdc_enable_db;
GO

Check out How to get table size in SQL Server

Enable CDC in SQL Server at the Table level.

You can use the below query to enable this feature at the table level.

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

You can use the query below to enable CDC for all tables in the SQL Server if you have multiple tables.

Check out How To Check If Table Exists In SQL Server

You can execute the query below to confirm if the CDC is enabled in your table.

GO
USE Test;
GO
SELECT name, is_tracked_by_cdc
FROM sys.tables
WHERE name = 'Vehicle';

After executing the above query, if the is_tracked_by_cdc value is 1, then the CDC is enabled, and if it is 0, then it is not enabled, as shown in the screenshot below.

Check out How To Check If CDC Is Enabled On A Table In SQL Server for more info.

Conclusion

Enabling CDC on a table in an SQL Server is an efficient way to track changes to various activities on your data in an SQL server. It provides valuable insights and helps support robust data management practices. You can utilize the information provided in this article to enable the CDC feature quickly.

You may also like following the articles below.

• How To Create A Table Variable In SQL Server
• How To Create A Table Valued Function In SQL Server
• How To Update Statistics On A Table In SQL Server