Create Change Data Capture (CDC) in SQL Server 2008

How to create Change Data Capture in SQL Server 2008

One of the new features of SQL Server 2008 is Change Data Capture (CDC). CDC is used to track changes (inserts, updates and deletes) made on SQL Server tables.

Step 1: Enable the CDC in the database
EXEC sys.sp_cdc_enable_db
Suppose you want to delete the CDC means
EXEC sys.sp_cdc_disable_db

Step 2: Create a Table
CREATE TABLE tbl_CDC_1(SNo INT IDENTITY, SName VARCHAR(50), Sal INT)

Step 3: Enable CDC for the Table
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo',
@source_name = 'tbl_CDC_1'
,@role_name = NULL
, @supports_net_changes = 1

Or disabled means

EXEC sys.sp_cdc_disable_table @source_schema = 'dbo',
@source_name = 'tbl_CDC_1'
,@capture_instance = 'all'

Step 4: Insert / Delete / Update operation

INSERT INTO tbl_CDC_1 VALUES ( 'Ram', 5000)
INSERT INTO tbl_CDC_1 VALUES ( 'madhu', 52000)
INSERT INTO tbl_CDC_1 VALUES ( 'saran', 15000)
DELETE FROM tbl_CDC_1 WHERE sname = 'Ram'
INSERT INTO tbl_CDC_1 VALUES ( 'Ram', 9800)
UPDATE tbl_CDC_1 SET Sname = 'Rama' WHERE SName = 'Ram'

Step 5: Select the Table and CDCTable

SELECT * FROM tbl_CDC_1

SELECT * FROM cdc.dbo_tbl_CDC_1_CT

Step 6: Select CDC function
DECLARE @fromlsn VARBINARY(MAX), @tolsn VARBINARY(MAX)
SET @fromlsn = sys.fn_cdc_get_min_lsn('dbo_tbl_CDC_1')
SET @tolsn = sys.fn_cdc_get_max_lsn ()

--SELECT @fromLsn, @tolsn

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_tbl_CDC_1(@fromLsn,@tolsn,'all')

_$operation formats
1 – Delete
2 – Insert
3 – Before update
4 – After update

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_tbl_CDC_1(@fromLsn,@tolsn,'all with merge')