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:

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

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
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.

Now Let’s query the table dbo_MyTable_CT as shown below. 
 
select * from cdc.dbo_MyTable_CT
 
Now Let’s update and delete some data from the table as shown below. 
 
use MyDataBase
go
Update MyTable set salary = 125000 where id = 4
go
delete Mytable where Id =4
go
 
Query the lsn_time_mapping table as shown below.
select * from cdc.lsn_time_mapping
 
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
 
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
 
As you can see from the data, it is obvious that the data changes are being tracked
 
you can take benifit of that in tracking users events. know about who make last update.