This article illustrates what happens to the Change Data Capture when the table structure is changed.

Step 1

Let’s create the database “CDCDB” as shown below.

USE [master]
GO
/*** Object:  Database [CDCDB]   Script Date: 01/07/2008 18:46:15 ***/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'CDCDB')
DROP DATABASE [CDCDB]
GO
USE [master]
GO
/*** Object:  Database [CDCDB]   Script Date: 01/07/2008 18:46:33 ***/
CREATE DATABASE [CDCDB] 
GO

Step 2

use [CDCDB]
go
/*** Object:  Table [dbo].[Employee]   Script Date: 01/07/2008 18:52:14 ***/
IF  EXISTS (SELECT * FROM sys.objects 
 WHERE object_id = OBJECT_ID(N'[dbo].[Employee]') 
 AND type in (N'U'))
DROP TABLE [dbo].[Employee]
GO
use [CDCDB]
go
/*** Object:  Table [dbo].[Employee]   Script Date: 01/07/2008 18:52:26 ***/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
 [ID] [int] NOT NULL,
 [Name] [varchar](100) NULL,
 CONSTRAINT [Employee_PK] 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

Step 3

Enable Change Data Capture on the “CDCDB” database, as shown below

USE [CDCDB]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO

Step 4

Now enable Change Data Capture on the “Employee” table on the “CDCDB” database, as shown below.

use [CDCDB]
go
GO
EXEC sys.sp_cdc_enable_table_change_data_capture 
@source_schema = 'dbo', 
@source_name = 'Employee', 
@role_name = 'cdc_Employee'
GO

Step 5

After enabling the CDC on the table, let’s add a few more columns to the “Employee” table, as shown below.

use [CDCDB]
go
GO
Alter Table Employee add Address varchar(500)
GO
Alter Table Employee add Salary money
GO
Alter Table Employee add Bonus money
GO

Step 6

Let’s add some data to the table:

use [CDCDB]
go
select  * from Employee
go
Insert into Employee values (1, 'Dancing Doll','221, West Broad st, 
 Greenbay, Wisconsin',60000,1000)
Insert into Employee values (2, 'Rainbow Dance','21, East st, 
 Denville, New Jersey',68000,1300)
Insert into Employee values (3, 'Water Dance','1, South Broad st, 
 Quincy, Massachusetts',76000,1600)
Insert into Employee values (4, 'Mickey Mouse','5, Main, 
 Greenbay, Wisconsin',120000,12000)
Insert into Employee values (5, 'Rat year','7, New road, 
 Danbury , Connecticut',45000,1600)
go
select  * from Employee
go

Result

ID, Name, Address, Salary, Bonus
home\sql2008(HOME\MAK): (0 row(s) affected)

home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)
ID, Name, Address, Salary, Bonus
1, Dancing Doll, 221,  West Broad st,  
 Greenbay,  Wisconsin, 60000.0000, 1000.0000
2, Rainbow Dance, 21,  East st,  
 Denville,  New Jersey, 68000.0000, 1300.0000
3, Water Dance, 1,  South Broad st,  
 Quincy,  Massachusetts, 76000.0000, 1600.0000
4, Mickey Mouse, 5,  Main,  
 Greenbay,  Wisconsin, 120000.0000, 12000.0000
5, Rat year, 7,  New road,  
 Danbury ,  Connecticut, 45000.0000, 1600.0000
home\sql2008(HOME\MAK): (5 row(s) affected)

Step 7

Let’s update and delete some data, as shown below.

use [CDCDB]
go
Update Employee set name='test' where id =5
go
Delete Employee where id in (3,4)
Go

Step 8

To see the DDL and DML changes being tracked, execute the following queries. [Refer Fig 1.0 and Fig 1.1]

use [CDCDB]
go
select * from cdc.ddl_history
go

Result

source_object_id, object_id, required_column_update, ddl_command, ddl_lsn, ddl_time
565577053, 597577167, 0, Alter Table Employee add Address varchar(500)
, 0x000000360000006B0022, 2008-02-09 15:03:00.000
565577053, 597577167, 0, Alter Table Employee add Salary money
, 0x000000360000007A0018, 2008-02-09 15:03:00.000
565577053, 597577167, 0, Alter Table Employee add Bonus money
, 0x00000036000000800018, 2008-02-09 15:03:00.000
home\sql2008(HOME\MAK): (3 row(s) affected)

use [CDCDB]
go
Select case __$operation when 1 then 'Deleting'
when 2 then 'Inserting'
when 3 then 'Value before Update'
when 4 then 'Value after Update'
when 5 then 'Merge' end ,__$update_mask,ID,Name
from cdc.dbo_Employee_CT
go

Result

, __$update_mask, ID, Name
Inserting, 0x03, 1, Dancing Doll
Inserting, 0x03, 2, Rainbow Dance
Inserting, 0x03, 3, Water Dance
Inserting, 0x03, 4, Mickey Mouse
Inserting, 0x03, 5, Rat year
Value before Update, 0x02, 5, Rat year
Value after Update, 0x02, 5, test
Deleting, 0x03, 3, Water Dance
Deleting, 0x03, 4, Mickey Mouse
home\sql2008(HOME\MAK): (9 row(s) affected)

From the above results, you can see that only the columns ID and Name are being tracked. Any columns added after enabling Change Data Capture on the table are not being tracked.

Step 9

Now let’s add all of the columns to the Change Data Capture. This can be done by disabling the current Change Data Capture and enabling it with new columns. Disable Change Data Capture with the following transact sql statement.

use [CDCDB]
go
EXEC sys.sp_cdc_disable_table_change_data_capture 
@source_schema = 'dbo', 
@source_name = 'Employee', 
@capture_instance = 'dbo_Employee'
Go

Step 10

Now let’s enable Change Data Capture on the “Employee” table. This time we are explicitly going to specify which columns we are going to track.

use [CDCDB]
go
EXEC sys.sp_cdc_enable_table_change_data_capture 
@source_schema = 'dbo', 
@source_name = 'Employee', 
@role_name = 'cdc_Employee',
@captured_column_list = N'ID, Name, Salary,Bonus' 
GO

Query the cdc Empolyee Change Table as shown below.

use [CDCDB]
go
select * from cdc.dbo_Employee_CT
go

The results show that all of the columns in the employee table are being tracked. [Refer Fig 1.2]

Conclusion

Part 3 of this series has illustrated how to disable and enable the Change Data Capture on the table in order keep up with the changes made to the table structure.