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.
Note: This article is written based on the SQL Server 2008 Nov CTP
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 lets 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)
Fig 1.1
The lsn_time_mapping table in the CDC schema records the lsn name and the beginning and end of the transaction. Lets 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)
Fig 1.2
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]
Fig 1.3
Now Lets 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)
Fig 1.4
Step 2
Now Lets 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)
Fig 1.5
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)
Lets 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.