Hello Folks,

You would have heard about this one. Well it was introduced first time with the arrival of SQL Server CDC in 2008.

I have noted down some keynotes about Change Data Capture (CDC), so please follow it carefully:

  • CDC can be used to capture insertions, updates, and deletes in an SQL table in a database and place the changes in another table.
  • 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.
  • Change data capture is available only on the Enterprise, Developer, and Evaluation editions of SQL Server 2008.
  • The following code will let you know, how to configure CDC for auditing of a table in a database:

First, you have to enable the Change Data Capture for a database.

Before a capture instance can be created for individual tables, a member of the sysadmin fixed server role must first enable the database for change data capture. Therefore, you the following stored procedure:

We can also see from the result set:


Now, it’s time to enable the CDC on the table. So execute the following stored procedure:

The result can be seen as:


  • To read the data from the CDC table, there are a series of system stored procedures and functions available, or you can query the tables directly.

System Stored Procedures:

  • sys.sp_cdc_add_job
  • sys.sp_cdc_generate_wrapper_function
  • sys.sp_cdc_change_ job
  • sys.sp_cdc_get_captured_columns
  • sys.sp_cdc_cleanup_change_table
  • sys.sp_cdc_get_ddl_history
  • sys.sp_cdc_disable_db
  • sys.sp_cdc_help_change_data_capture
  • sys.sp_cdc_disable_table
  • sys.sp_cdc_help_ jobs
  • sys.sp_cdc_drop_ job
  • sys.sp_cdc_scan
  • sys.sp_cdc_enable_db
  • sys.sp_cdc_start_ job
  • sys.sp_cdc_enable_table
  • sys.sp_cdc_stop_ job

System Functions:

  • cdc.fn_cdc_get_all_changes_<capture_instance>
  • sys.fn_cdc_has_column_changed
  • cdc.fn_cdc_get_net_changes_<capture_instance>
  • sys.fn_cdc_increment_lsn
  • sys.fn_cdc_decrement_lsn
  • sys.fn_cdc_is_bit_set
  • sys.fn_cdc_get_column_ordinal
  • sys.fn_cdc_map_lsn_to_time
  • sys.fn_cdc_get_max_lsn
  • sys.fn_cdc_map_time_to_lsn
  • sys.fn_cdc_get_min_lsn

You can also be able to view the templates for Change Data Capture in a Template Explorer-


Well this was about How to Configure Change Data Capture.

And also comments on this!!



Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook