Microsoft has introduced Change Data Capture, a feature that tracks changes on a table, in SQL Server 2008. Part I and Part II of the article illustrated how to enable Change Data Capture on a database and on a table.
This article illustrates what happens to the Change Data Capture when the table structure is changed.
Note: This article is written based on the SQL Server 2008 – Nov CTP.
Step 1
Let’s create the database “CDCDB” as shown below.
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’CDCDB’)
DROP DATABASE [CDCDB]
GO
USE [master]
GO
CREATE DATABASE [CDCDB]
GO
Step 2
use [CDCDB]
go
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
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)
Fig 1.0
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)
Fig 1.1
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]
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.
» See All Articles by Columnist MAK
