1. Back To Blog

SQL Server interview questions and answers: - What is CDC (Change data capture) in SQL Server?

• By Shiv Prasad Koirala in SQL
• Dec 23rd, 2012
• 8820
• 0

This question was asked in Datamatics,Mumbai when one of our DBA  friends went for SQL Server interview.

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
  

Do see our video on (SQL Server interview question) How does index makes your search faster ?.

The above question is taken from the SQL Server interview question book written by Shivprasad Koirala.

For SQL Step by Step videos visit Questpond.com.

Get revised with most asked SQL Server Interview Questions with below 1 hour tutorial

Shiv Prasad Koirala

Visit us @ www.questpond.com or call us at 022-66752917... read more