Change Data Capture (CDC)

In order to enable it we have to enable on 2 level. 1.       Database Level - EXEC SYS.SP_CDC_ENABLE_DB Once CDC enables, it creates some tables and stored procedures to keep track of records.

2.       Table Level - Before running below query first make sure that SQLServerAgent is running. EXEC SYS.SP_CDC_ENABLE_TABLE @SOURCE_SCHEMA=N'dbo', @SOURCE_NAME=N'Sales', @ROLE_NAME=NULL Once CDC enables on table, it creates one more table.

Insert, update and delete operations on dbo.Sales table will insert log record in cdc.dbo_Sales_CT. 1.       Blank tables

2.       Insert new records to dbo.Sales will make an entry in cdc.dbo_Sales_CT table to keep log of insert with flag 2 in __$operation column.

3.       Update a record will make 2 entry in cdc.dbo_Sales_CT to indicate old as well as new row with flag 3 and 4 respectively in __$operation column.

4.       Delete a record will make one entry in cdc.dbo_Sales_CT to indicate deleted row with flag 1 in __$operation column.