SQL Server interview questions and answers: - What is CDC (Change data capture) in SQL Server?
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.
If you are going for DBA interview please see this article which covers SQL Server database design interview questions and answers.