MS SQL Server 2008 Interview Question:

What is Change Data Capture (CDC) feature?

Submitted by: Administrator

Change Data Capture is a feature that is used for tracking the changes on a table. The process involves in steps.

Step 1 - Creation of a database
The database name is MyDataBase
USE [master]
GO

/*** Object: Database [MyDataBase] ***/

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')
DROP DATABASE [MyDataBase]
GO
USE [master]
GO

/*** Object: Database [MyDataBase] ***/
CREATE DATABASE [MyDataBase]
GO
Step 2 - Creation of a table in MyDataBase database
USE [MyDataBase]
GO

/*** Object: Table [dbo].[MyTable] ***/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO
USE [MyDataBase]
GO

CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,
CONSTRAINT [MyTable_PK] PRIMARY KEY
GO

Step 3 - Enabling Change Data Capture feature.
The Transact SQL command enables the Change Data Capture feature.
After enabling the Change Data Capture, a schema along with objects is created.
USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO

Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:

cdc.captured_columns
cdc.change_tables
cdc.ddl_history
cdc.index_columns
cdc.lsn_time_mapping
Submitted by: Administrator