Microsoft SQL Server 2008 - Change Data Capture - Part 2

One of the new feature in Microsoft SQL Server 2008 is track the changes on a table. You can enable change tracking on a table using Change Data Capture feature.

Part 1 of this article discussed how to enable the new SQL Server Feature “Change Data Capture” on a database. It also illustrated how to enable the Change Data Capture on a table, how to keep track of Data Definition Language changes on a table and explained the CDC schema and changes happening in the objects of CDC schema.

Part 2 of this article illustrates how to enable Change Data Capture on a database, on a table and how SQL Server tracks the data changes of the CDC enabled table.

In Part 1 we created the database Mydatabase and created my table in the database Mydatabase. We altered the table by adding a couple of columns and saw how the DDL changes were captured.

Now let’s add some data to the table using the following SQL Statements:

Step 1

Execute the following Transact SQL Statement as shown below. [Refer Fig 1.1]

use MyDataBase
go
select  * from MyTable
go
Insert into Mytable values (1, 'Dance Doll','221, West Broad st, 
 Greenbay, Wisconsin',60000,1000)
Insert into Mytable values (2, 'Rainbow Colors','21, East st, 
 Denville, New Jersey',68000,1300)
Insert into Mytable values (3, 'River Dance','1, South Broad st, 
 Quincy, Massachusetts',76000,1600)
Insert into Mytable values (4, 'Mickey Mouse','5, Main, 
 Greenbay, Wisconsin',120000,12000)
Insert into Mytable values (5, 'Universal Studios','7, New road, 
 Danbury , Connecticut',45000,1600)
go

Results

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)

The lsn_time_mapping table in the CDC schema records the lsn name and the beginning and end of the transaction. Let’s query the CDC.lsn_time_mapping table as shown below. [Refer Fig 1.2]

select * from cdc.lsn_time_mapping

Result

start_lsn, tran_begin_time, tran_end_time, tran_id
0x0000001C0000018B002F, 2008-01-16 01:53:38.840, 2008-01-16 01:53:38.840, 0x00000000029C
0x0000001C000001920003, 2008-01-16 01:53:38.857, 2008-01-16 01:53:38.857, 0x00000000029F
0x0000001C000001930003, 2008-01-16 01:53:38.857, 2008-01-16 01:53:38.857, 0x0000000002A0
0x0000001C000001940003, 2008-01-16 01:53:38.857, 2008-01-16 01:53:38.857, 0x0000000002A1
0x0000001C000001950003, 2008-01-16 01:53:38.857, 2008-01-16 01:53:38.857, 0x0000000002A2
home\sql2008 (HOME\MAK): (5 row(s) affected)

For every table that SQL Server is tracking, a table is created in the CDC schema with the naming convention SourceSchema_SourceTable_CT. In this case there is a table called dbo_MyTable_CT. [Refer Fig 1.3]

Now Let’s query the table dbo_MyTable_CT as shown below. [Refer Fig 1.4]

select * from cdc.dbo_MyTable_CT

Result

__$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, ID, Name
0x0000001C0000018B002F, NULL, 0x0000001C0000018B002E, 2, 0x03, 1, Dance Doll
0x0000001C000001920003, NULL, 0x0000001C000001920002, 2, 0x03, 2, Rainbow Colors
0x0000001C000001930003, NULL, 0x0000001C000001930002, 2, 0x03, 3, River Dance
0x0000001C000001940003, NULL, 0x0000001C000001940002, 2, 0x03, 4, Mickey Mouse
0x0000001C000001950003, NULL, 0x0000001C000001950002, 2, 0x03, 5, Universal Studios
home\sql2008(HOME\MAK): (5 row(s) affected)

Step 2

Now Let’s update and delete some data from the table as shown below. [Refer Fig 1.5]

use MyDataBase
go
Update MyTable set salary = 125000 where id = 4
go
delete Mytable where Id =4
go

Result

home\sql2008(HOME\MAK): (1 row(s) affected)
home\sql2008(HOME\MAK): (1 row(s) affected)

Query the lsn_time_mapping table as shown below.

select * from cdc.lsn_time_mapping

Result

start_lsn, tran_begin_time, tran_end_time, tran_id
0x0000001C0000018B002F, 2008-01-16 01:53:38.840, 2008-01-16 01:53:38.840, 0x00000000029C
0x0000001C000001920003, 2008-01-16 01:53:38.857, 2008-01-16 01:53:38.857, 0x00000000029F
0x0000001C000001930003, 2008-01-16 01:53:38.857, 2008-01-16 01:53:38.857, 0x0000000002A0
0x0000001C000001940003, 2008-01-16 01:53:38.857, 2008-01-16 01:53:38.857, 0x0000000002A1
0x0000001C000001950003, 2008-01-16 01:53:38.857, 2008-01-16 01:53:38.857, 0x0000000002A2
0x0000001C000001BB0004, 2008-01-16 02:08:37.357, 2008-01-16 02:08:37.357, 0x0000000002B1
home\sql2008(HOME\MAK): (6 row(s) affected)

You can see there is a new lsn entry in the table.

Query the cdc.dbo_MyTable_CT as shown below.

select * from cdc.dbo_MyTable_CT

Result

__$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, ID, Name
0x0000001C0000018B002F, NULL, 0x0000001C0000018B002E, 2, 0x03, 1, Dance Doll
0x0000001C000001920003, NULL, 0x0000001C000001920002, 2, 0x03, 2, Rainbow Colors
0x0000001C000001930003, NULL, 0x0000001C000001930002, 2, 0x03, 3, River Dance
0x0000001C000001940003, NULL, 0x0000001C000001940002, 2, 0x03, 4, Mickey Mouse
0x0000001C000001950003, NULL, 0x0000001C000001950002, 2, 0x03, 5, Universal Studios
0x0000001C000001BB0004, NULL, 0x0000001C000001BB0002, 1, 0x03, 4, Mickey Mouse
home\sql2008(HOME\MAK): (6 row(s) affected)

Let’s try to update a row and do not delete that row.

Update MyTable set salary = 1200 where id = 1
Update MyTable set name ='abc' where name ='Dance Doll'

Query the cdc.dbo_MyTable_CT as shown below.

select * from cdc.dbo_MyTable_CT

Result

__$start_lsn, __$end_lsn, __$seqval, __$operation, __$update_mask, ID, Name
0x0000001C0000018B002F, NULL, 0x0000001C0000018B002E, 2, 0x03, 1, Dance Doll
0x0000001C000001920003, NULL, 0x0000001C000001920002, 2, 0x03, 2, Rainbow Colors
0x0000001C000001930003, NULL, 0x0000001C000001930002, 2, 0x03, 3, River Dance
0x0000001C000001940003, NULL, 0x0000001C000001940002, 2, 0x03, 4, Mickey Mouse
0x0000001C000001950003, NULL, 0x0000001C000001950002, 2, 0x03, 5, Universal Studios
0x0000001C000001BB0004, NULL, 0x0000001C000001BB0002, 1, 0x03, 4, Mickey Mouse
0x0000001C000001E40004, NULL, 0x0000001C000001E40002, 3, 0x02, 1, Dance Doll
0x0000001C000001E40004, NULL, 0x0000001C000001E40002, 4, 0x02, 1, abc
home\sql2008(HOME\MAK): (8 row(s) affected)

As you can see from the data, it is obvious that the data changes are being tracked.

Note: This article is written based on the SQL Server 2008 – Nov CTP

Conclusion

This article has illustrated how to enable Change Data Capture on a database, on a table and how SQL Server tracks the data changes of the CDC enabled table. In the next article, we will see how to get the change in data in a useful form.

» See All Articles by Columnist MAK