Learn how DML and DDL changes impact Change Data Capture in SQL Server

Problem

Enabling SQL Server Change Data Capture (CDC) will not prevent you from performing DDL changes in your database, but will CDC continue running after DDL changes are made? Will DDL changes be reflected in the CDC system tables? Read this tip to get a closer look at what does happens when DDL changes are made to tables using CDC.

Solution

The SQL Server CDC capture process is responsible for populating change tables when DML changes are applied to source tables, but it ignores any DDL changes such as adding or dropping columns. It only captures data changes based on the format of the table at the time the table was enabled for CDC.

In order to study DDL changes on a table with CDC enabled, we will create a table and enable CDC on the database and table level without going deeply into CDC which is covered in other tips.

Setup SQL Server Change Data Capture Example

The following creates a table and enables CDC for the database and table.

USE MSSQLTipsDemo
GO
-- Create CDCTEST table
CREATE TABLE [dbo].[CDCTest](
[ID] [int] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nchar](10) NULL,
 CONSTRAINT [PK_CDCTest] 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
-- Enable CDC on DB level
EXEC sys.sp_cdc_enable_db  
GO
-- Enable on the table level
EXEC sys.sp_cdc_enable_table   
@source_schema = N'dbo',
@source_name   = N'CDCTest',
@role_name     = NULL,
@filegroup_name = N'Primary',
@supports_net_changes = 0
GO

You can check that CDC is enabled on the table using the cdc.change_tables system table in your database. The result will be as follows:

SELECT * FROM cdc.change_tables

Also, the cdc.captured_columns table can show you which columns are captured as follows:

SELECT * FROM cdc.captured_columns

Let’s start the scenario by inserting two records into the CDCTest table:

USE [MSSQLTipsDemo]
GO
INSERT [dbo].[CDCTest] ([ID], [FirstName], [LastName]) VALUES (1, N'Ahmad', N'Yaseen')
GO
INSERT [dbo].[CDCTest] ([ID], [FirstName], [LastName]) VALUES (2, N'Michel', N'Fridd')
GO

These changes will now be found in the capture table under (cdc.dbo_CDCTest_CT) name, as shown below:

SELECT * FROM cdc.dbo_CDCTest_CT

So at this point, CDC is enabled for our table and DML changes are captured and logged in the capture table.

Adding a New Column to a SQL Server Table

The first DDL change to study is to add a new column to the source table. Let’s add Address as a new column:

ALTER TABLE dbo.CDCTest ADD Address nvarchar(50) NULL
GO

Nothing will prevent this change from being performed. But if we try to check whether the change is reflected to the captured columns table, sadly the column will not be within these columns:

SELECT * FROM cdc.captured_columns

Will CDC continue to capture the DML changes on our table? Let’s add a new record to the table and check the result from the capture table:

INSERT [dbo].[CDCTest] ([ID], [FirstName], [LastName],[Address]) VALUES (3, N'John', N'Claude','LONDON')
SELECT * FROM cdc.dbo_CDCTest_CT

As we can see from the capture table, CDC will keep tracking the changes, but it ignores the new column.

Capture New Data and Keep History with SQL Server Change Data Capture

To overcome this problem and without losing old captured data, we can create a new capture instance for the source table in order to reflect the table structure change, copy the captured data from the old capture instance to the new created one, and then disable the old capture instance. You can create up to two concurrently capture instances per source table.

To simulate this solution, we will start with creating the New_TestCDC capture instance which will create a new capture table associated with the CDCTest table:

EXEC sys.sp_cdc_enable_table   
@source_schema = N'dbo',
@source_name   = N'CDCTest',
@role_name     = NULL,
@capture_instance = N'New_TESTCDC' ,
@filegroup_name = N'Primary',
@supports_net_changes = 0
GO

You can get the name of the new capture table using Management Studio and browsing the list of system tables in your database, which will be [cdc].[New_TESTCDC_CT] for our example.

Also you can get the capture instances by executing the sys.sp_cdc_help_change_data_capture stored procedure. The result will be like:

EXEC sys.sp_cdc_help_change_data_capture

Before disabling the old capture instance, we should copy the captured data to the new capture table with a simple INSERT INTO … SELECT FROM statement as below:

INSERT INTO [cdc].[New_TESTCDC_CT] ([__$start_lsn]
           ,[__$end_lsn]
           ,[__$seqval]
           ,[__$operation]
           ,[__$update_mask]
           ,[ID]
           ,[FirstName]
           ,[LastName]) 
   SELECT [__$start_lsn]
           ,[__$end_lsn]
           ,[__$seqval]
           ,[__$operation]
           ,[__$update_mask]
           ,[ID]
           ,[FirstName]
           ,[LastName] FROM [cdc].[dbo_CDCTest_CT]

Once all the data are copied, we can disable the old capture instance which will drop the old capture table too:

-- Disable the CDC on the table level 
EXEC sys.sp_cdc_disable_table
@source_schema = N'dbo',
@source_name   = N'CDCTest',
@capture_instance = N'dbo_CDCTest'
GO

If you try to perform a new Insert statement, the capture table will track all columns as the following:

INSERT [dbo].[CDCTest] ([ID], [FirstName], [LastName],[Address]) VALUES (6, N'Moh', N'Cal','JED')
SELECT * FROM cdc.dbo_CDCTest_CT

Modify the Data Type for a Tracked SQL Server Table in CDC

The next type of DDL change that we will test is changing a tracked column’s datatype. In our previous example, assume that we need to change the Address column datatype to nchar(50). The change will be done successfully, and this change will be replicated to the change table to make sure that no data loss will be occurred to the tracked column.

ALTER TABLE [dbo].[CDCTest]ALTER COLUMN [Address] [NCHAR](50) NULL

The capture process will also posts these changes on the column structure of the tracked tables to the cdc.ddl_history system table as below:

Dropping a Tracked Column

For a different test, let’s say our CDCTest table now has the Address column and CDC was setup to capture all of the columns. If we run the below ALTER statement to drop the Address column, you will find that the statement will run successfully, but the change will not be reflected to the captured columns table as follows:

ALTER TABLE dbo.CDCTest DROP COLUMN Address
GO
SELECT * FROM cdc.captured_columns

If we perform a DML change on the table, NULL values will be inserted for the Address column for each change entry:

INSERT [dbo].[CDCTest] ([ID], [FirstName], [LastName]) VALUES (5, N'Jerry', N'Mug')
SELECT * FROM cdc.dbo_CDCTest_CT

CDC will continue capturing the changes from the source table, but ignores the DDL changes and just stores NULL values for the dropped columns.

Next Steps

• Check out these other Change Data Capture Tips