AboutMicrosoft 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 /*** 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)
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.