SQL - Change Data Capture (CDC)

CDC - Change data capture helps us to track insert/update/delete on any table
CDC is not available in express edition, it is available in enterprise, developer & enterprise evaluation editions

Steps to enable CDC on one or more tables those you want to track

1. First enable CDC at database level
   
   sp_cdc_enable_db
2. Enable CDC at table level
   
   sp_cdc_enable_table
   @source_schema = 'dbo',
   @source_name = 'table1'
   
   
   @role_name = null
   
   
   sp_cdc_enable_table
   @source_schema = 'dbo',
   @source_name = 'table2'
   
   
   @role_name = null

Sql creates some 6 new tables and lots of procedures to track and store data of change.

For every table on which you on CDC it creates a table with name pattern for above example

cdc.dbo_table1_CT

cdc.dbo_table2_CT

Sql stores all changed data in their respective table with a column "_$operation" that have values as given below

1 - deleted record

2 - Inserted record

3 - Record before update

4 - Record after update