Change Data Capture

-- CDC = Change Data Capture

-- check id field is_cdc_enables = 1
select * from sys.databases

-- id field is_cdc_enables = 0 then run the next command in the target DB.
exec sys.sp_cdc_enable_db

-- enable CDC on table person.
-- after running the next command a new table will be created by the name [cdc].[dbo_person_CT].
-- Two new job will be created too. [cdc.Test_capture],[cdc.Test_cleanup]
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'person',
@role_name     = N'NULL'

-- insert a row to activate CDC.
insert person(id,name) select 666666666,'Sendy'

-- the row was inserted to the main table and CDC capture the change to CT table.
select * from [cdc].[dbo_person_CT]

-- start_lsn = __$start_lsn from previous query.
select * from cdc.lsn_time_mapping

-- update statement
update person
set name='Sendy my dog'
where name='Sendy'

-- the update statement insert 2 rows to the CT table (before and after the update).
select * from person
select __$start_lsn start_lsn,__$end_lsn  end_lsn,__$seqval sequence_value,
       case
            when __$operation = 1 then 'Delete'
            when __$operation = 2 then 'Insert'
            when __$operation = 3 then 'Before update'
            when __$operation = 4 then 'After update'
       end Operation,
       id,name
  from cdc.dbo_person_CT order by __$start_lsn desc
select * from cdc.lsn_time_mapping order by start_lsn desc

-- delete row.
delete from person where name='Sendy my dog'

-- to disable the CDC in the target DB.
-- the 2 job will be deleted.
-- the system tables will be deleted too.
exec sys.sp_cdc_disable_db