How to maintain a history of all data modifications made to a table, including the type of modification and modified values?

Change data capture

Change data capture (CDC) is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.

CDC minimizes the resources required for ETL ( extract, transform, load ) processes because it only deals with data changes. 

Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server.

How to check if you have enabled Change data capture on your database?

The following code will check  if you have enabled Change data capture on your database:
SELECT [name], database_id, is_cdc_enabled  
FROM sys.databases   
WHERE [name] =’AdventureWorks’

Enabling Change data capture on database level

The following code will enable Change data capture on database level:

EXEC sys.sp_cdc_enable_db 

After enabling Change data capture on database level you will notice that new schema ‘cdc’ was created.

Also the following tables are created under cdc schema:

  • cdc.captured_columns – This table returns result for list of captured column.
  • cdc.change_tables – This table returns list of all the tables which are enabled for capture.
  • cdc.ddl_history – This table contains history of all the DDL changes since capture data enabled.
  • cdc.index_columns – This table contains indexes associated with change table.
  • cdc.lsn_time_mapping – This table maps LSN number (for which we will learn later) and time.

Enabling Change Data Capture on Database Tables

First, we can check if CDC has been enabled to a table with the following query:
SELECT [name], is_tracked_by_cdc  
FROM sys.tables 
We can enable change data capture on a table with the following stored procedure:

EXEC sys.sp_cdc_enable_table 
@source_schema = N’Person’, 
@source_name   = N’Contact’, 
@role_name     = NULL 

Before executing the stored procedure make sure that SQL Agent is enabled since enabling CDC on tables will cause creation of two agent jobs:
  • cdc.AdventureWorks_capture 
  • cdc.AdventureWorks_cleanup 
After everything is completed successfully you will notice new table cdc.Person_Contact_CT where all changes will be tracked.

In that table two columns are really important, __$operation and __$update_mask.

Column _$operation contains value which corresponds to DML Operations.

This is a list of values and its meaning:
Delete Statement = 1
Insert Statement = 2
Value before Update Statement = 3
Value after Update Statement = 4

The column _$update_mask shows, via a bitmap, which columns were updated in the DML operation that was specified by _$operation. If this was a DELETE or INSERT operation, all columns are updated and so the mask contains value which has all 1’s in it. This mask is contains value which is formed with Bit values.

2 thoughts on “How to maintain a history of all data modifications made to a table, including the type of modification and modified values?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.