What is CDC (Change data capture) in SQL Server?

Change data capture helps to capture insert, update and delete activity in SQL Server.

 Enabling CDC is a two-step process:-

The first step is to fire exec "sp_cdc_enable_db" and enable CDC on database level.


Once CDC is enabled on a database level, we need to also specify which tables needs to be enabled for CDC. Below is a simple code snippet which shows how "Sales" table has been enabled for CDC.


Once CDC is enabled, you will find the below tables created for CDC. The most important table is _CT table. For example you can see the below image, for the sales table it has created "dbo_Sales_CT" table.





Now if we modify any data in the "Sales" table the "Sales_CT" table will be affected. After any modification on the "Sales" table, in "Sales_CT" table we will get two rows one with the old value and the other with new value. Below image shows that "Rajendra" has been modified to "Raju" in the "Sales" table.



If you see the _CT table it has a column called as"_$operation". This field will help us identify what kinds of transactions are done with the data. Below are the possible values depending on operation done on the data:-

• Delete Statement = 1
  
  
• Insert Statement = 2
  
  
• Value before Update Statement = 3
  
  
• Value after Update Statement = 4