Using Change Data Capture (CDC) in SQL Server 2008

By:   |   Comments (37)   |   Related: 1 | 2 | 3 | 4 | > Change Data Capture


As we are looking through the new features in SQL Server 2008 we found a potentially interesting one called Change Data Capture. Can you give us a detailed explanation of how we go about using this one?


Change Data Capture is a new feature in SQL Server 2008 that records insert, update and delete activity in SQL Server tables. A good example of how this feature can be used is in performing periodic updates to a data warehouse. The requirement for the extract, transform, and load (ETL) process is to update the data warehouse with any data that has changed in the source systems since the last time the ETL process was run. Before CDC we might simply query a last updated DATETIME column in our source system tables to determine what rows have changed. While this is simple and pretty effective, it is of no use in determining any rows that were physically deleted. In addition we can't determine what was changed when; we can only access the current state of a row that has changed. CDC provides a configurable solution that addresses these requirements and more.

In this tip we are going to gain an understanding of CDC by walking through a simple code sample to demonstrate how to:

  • Setup and configure CDC
  • Use CDC to extract rows that have been inserted, updated, or deleted via T-SQL queries

Before we start reviewing the sample T-SQL code, let's discuss how CDC works at a high level. After performing some setup and configuration steps (which we will cover below), CDC will begin scanning the database transaction log for changes to certain tables that you specify, and will insert these changes into change tables. These change tables are created during the setup and configuration process. The setup and configuration process will also create table-valued functions which can be used to query for the changes. You use the table-valued functions in lieu of querying the underlying change tables directly. Based on this high level description, let's proceed to the demo.

The demo code below was only tested on the February, 2008 Community Technology Preview (CTP) of SQL Server 2008. Some of the function names and stored procedure names have changed from the earlier CTPs.

Setup and Configuration

CDC is a feature that must be enabled at the database level; it is disabled by default. To enable CDC you must be a member of the sysadmin fixed server role. You can enable CDC on any user database; you cannot enable it on system databases. Execute the following T-SQL script in the database of your choice to enable CDC:

declare @rc int
exec @rc = sys.sp_cdc_enable_db
select @rc
-- new column added to sys.databases: is_cdc_enabled
select name, is_cdc_enabled from sys.databases

The sys.sp_cdc_enable_db stored procedure will return 0 if successful and 1 if it fails. You can query whether CDC is enabled for any database by checking the new column is_cdc_enabled in the sys.databases table. You will see a value of 1 if CDC is enabled, a 0 otherwise.

The next step is to specify a table that you want to enable for CDC. Let's create a simple table called customer:

create table dbo.customer
id int identity not null
, name varchar(50) not null
, state varchar(2) not null
,constraint pk_customer primary key clustered (id)

Execute the following system stored procedure to enable CDC for the customer table:

exec sys.sp_cdc_enable_table 
    @source_schema = 'dbo', 
    @source_name = 'customer' ,
    @role_name = 'CDCRole',
    @supports_net_changes = 1

select name, type, type_desc, is_tracked_by_cdc from sys.tables

You must be a member of the db_owner fixed database role in order to execute the above system stored procedure and SQL Agent must be running. The sys.sp_cdc_enable_table system stored procedure has quite a few parameters; let's describe each one (only the first three parameters are required; the rest are optional and only the ones used are shown above):

  • @source_schema is the schema name of the table that you want to enable for CDC
  • @source_name is the table name that you want to enable for CDC
  • @role_name is a database role which will be used to determine whether a user can access the CDC data; the role will be created if it doesn't exist. You can add users to this role as required; you only need to add users that aren't already members of the db_owner fixed database role.
  • @supports_net_changes determines whether you can summarize multiple changes into a single change record; set to 1 to allow, 0 otherwise.
  • @capture_instance is a name that you assign to this particular CDC instance; you can have up two instances for a given table.
  • @index_name is the name of a unique index to use to identify rows in the source table; you can specify NULL if the source table has a primary key.
  • @captured_column_list is a comma-separated list of column names that you want to enable for CDC; you can specify NULL to enable all columns.
  • @filegroup_name allows you to specify the FILEGROUP to be used to store the CDC change tables.
  • @partition_switch allows you to specify whether the ALTER TABLE SWITCH PARTITION command is allowed; i.e. allowing you to enable partitioning (TRUE or FALSE).

There is a new column named is_tracked_by_cdc in sys.tables; you can query it to determine whether CDC is enabled for a table.

Enabling CDC at the database and table levels will create certain tables, jobs, stored procedures and functions in the CDC-enabled database. These objects will be created in a schema named cdc and a cdc user is also created. You will see a message that two SQL Agent jobs were created; e.g. cdc.sql2008demo_capture which scans the database transaction log to pickup changes to the tables that have CDC enabled, and cdc.sql2008demo_cleanup which purges the change tables periodically. The naming convention is cdc.[databasename]_task. We will discuss some of the schema objects created as we use them in the demo that follows. You can examine the schema objects created by running the following T-SQL script:

select, o.type, o.type_desc from sys.objects o
join sys.schemas  s on s.schema_id = o.schema_id
where = 'cdc'

You can disable CDC on a particular table by executing the following T-SQL script:

exec sys.sp_cdc_disable_table 
  @source_schema = 'dbo', 
  @source_name = 'customer',
  @capture_instance = 'dbo_customer' -- or 'all'

You can disable CDC at the database level by executing the following T-SQL script:

declare @rc int
exec @rc = sys.sp_cdc_disable_db
select @rc
-- show databases and their CDC setting
select name, is_cdc_enabled from sys.databases

Disabling CDC at the table and/or database level will drop the respective tables, jobs, stored procedures and functions that were created in the database when CDC was enabled.

Now that we have enabled CDC for a database and a table, let's proceed to a demo.

CDC Demo

We will perform the following steps to demonstrate the CDC functionality:

  • Perform a couple of inserts, update, and deletes to the customer table
  • Show T-SQL code samples to query the changes

Execute the following T-SQL script to perform some inserts, an update, and a delete on the customer table:

insert customer values ('abc company', 'md')
insert customer values ('xyz company', 'de')
insert customer values ('xox company', 'va')
update customer set state = 'pa' where id = 1
delete from customer where id = 3

Now let's take a look at a query that will show us a record of the above changes:

declare @begin_lsn binary(10), @end_lsn binary(10)
-- get the first LSN for customer changes
select @begin_lsn = sys.fn_cdc_get_min_lsn('dbo_customer')
-- get the last LSN for customer changes
select @end_lsn = sys.fn_cdc_get_max_lsn()
-- get net changes; group changes in the range by the pk
select * from cdc.fn_cdc_get_net_changes_dbo_customer(@begin_lsn, @end_lsn, 'all'); 
-- get individual changes in the range
select * from cdc.fn_cdc_get_all_changes_dbo_customer(@begin_lsn, @end_lsn, 'all');

To extract the changes for a table that has CDC enabled, you have to supply the relevant LSNs. An LSN is a log sequence number that uniquely identifies entries in the database transaction log. If this is the first time you are querying to extract changes, you can get the minimum LSN and the maximum LSN using the functions sys.fn_cdc_get_min_lsn() and sys.fn_cdc_get_max_lsn(). If you set @supports_net_changes = 1 when enabling CDC on the table, you can query for the net changes using cdc.fn_cdc_get_net_changes_dbo_customer(). This will group multiple changes to a row based on the primary key or unique index you specified when enabling CDC. You can always invoke cdc.fn_cdc_get_all_changes_dbo_customer() to retrieve every change to the table within the LSN range. The dbo_customer portion of the function name is the capture instance; this is the default - schema_tablename. Here's the output from the above query:

query 1

The first result set shows the net changes; the second result set shows the individual changes. Since there was an insert and a delete, the first result set doesn't show that row since it was added and deleted in the LSN range; i.e. it no longer exists when you group the changes. The __$operation column values are: 1 = delete, 2 = insert, 3 = update (values before update), 4 = update (values after update). To see the values before update you must pass 'all update old' to the cdc.fn_cdc_get_all_changes_dbo_customer() function. The __$update_mask column is a bit mask column that identifies the columns that changed. For __$operation = 1 or 2, all columns are indicated as changed. For __$operation = 3 or 4, the actual columns that changed are indicated. The columns are mapped to bits based on the column_ordinal; execute the stored procedure sys.sp_cdc_get_captured_columns passing the capture instance as a parameter to see the column_ordinal values; for example:

captured columns

Let's extend this example to handle periodically extracting changed rows. We will add a new table to log the ending LSN and a new function to retrieve the ending LSN from the table. This will allow us to pick up just what changed since the last time we ran our ETL process.

create table dbo.customer_lsn (
last_lsn binary(10)
create function dbo.get_last_customer_lsn() 
returns binary(10)
 declare @last_lsn binary(10)
 select @last_lsn = last_lsn from dbo.customer_lsn
 select @last_lsn = isnull(@last_lsn, sys.fn_cdc_get_min_lsn('dbo_customer'))
 return @last_lsn

We'll modify the code above adding a call to the get_last_customer_lsn() function and an insert or update to save the ending LSN:

declare @begin_lsn binary(10), @end_lsn binary(10)
-- get the next LSN for customer changes
select @begin_lsn = dbo.get_last_customer_lsn()
-- get the last LSN for customer changes
select @end_lsn = sys.fn_cdc_get_max_lsn()
-- get the net changes; group all changes in the range by the pk
select * from cdc.fn_cdc_get_net_changes_dbo_customer(@begin_lsn, @end_lsn, 'all'); 
-- get all individual changes in the range
select * from cdc.fn_cdc_get_all_changes_dbo_customer(@begin_lsn, @end_lsn, 'all'); 
-- save the end_lsn in the customer_lsn table
update dbo.customer_lsn
set last_lsn = @end_lsn
if @@ROWCOUNT = 0
  insert into dbo.customer_lsn values(@end_lsn)

After running the above query once, insert a row, then run the query again. Your output will look like this, showing only a single row that has changed:

query 2
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Tuesday, June 25, 2019 - 6:01:40 AM - Nagaraj Back To Top (81577)

Excellent article, to the point very informative.

Thanks a lot.

Thursday, September 18, 2014 - 6:51:09 AM - Ahmad Back To Top (34584)

Nice article. Do check for a CDC GUI application. It automates CDC and automates incremental data load based on CDC.




Wednesday, November 20, 2013 - 1:09:25 PM - Raymond Barley Back To Top (27554)

If you do a google search on "sp_cdc_enable_table_internal, Line 623" you will find a number of different resolutions for this problem; you will have to scan through these and see if one of them will work.  Some depend on which version of SQL Server, installing .NET framework, etc.

Wednesday, November 20, 2013 - 4:11:53 AM - Arul Back To Top (27543)

I am getting the following error message while trying to execute the below query... Please advice on the way to rectify this...


EXEC sys.sp_cdc_enable_table

@source_schema = N'dbo',

@source_name   = N'customer',

@role_name     = NULL,

@supports_net_changes = 1



Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 623

Could not update the metadata that indicates table [dbo].[customer] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database test to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_jobstep_internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.


Tuesday, March 12, 2013 - 5:40:28 AM - Ray Barley Back To Top (22729)

I've never used CDC on a database that is a subscriber to an Oracle publication.  I don't see anything in books on line that talks about this scenario.

SQL Server does support a republishing scenario where a subscriber can also be a publisher; e.g.

Monday, March 11, 2013 - 10:00:09 PM - Dhanu Back To Top (22720)

Hi Ray,

Good document... I have a question on CDC.

I have setup the heterogeneous replication from Oracle publisher to SQL subscription.

I want to enable the CDC for Subscription database and all associated replicated tables on sql server subscription database.

Is Enabling the CDC is same as the normal database or should I have to consider any other aspects when enabling the CDC on Subscription database?

Thank you very much in advance!!!!!

Thursday, February 28, 2013 - 3:37:27 PM - Mark Back To Top (22489)


I did see that. However I was looking at using the CDC data to ammend my tables in the dou just get the delete. ata warehouse.


I was scratching my head over how to load in a load of Updates, Deletes and Inserts. I think I have the solutions in that I get only Net Changes in the CDC. This then allows me to take the changes and Insert all Inserts, Delete all delets, Update all updates. I no longer care about the order it is done in as the CDC net changes takes care of that.


So if there is an insert for one row and it is deleted with net CDC you just get the Delete.Or at least I hope this is right.




Thursday, February 28, 2013 - 1:51:56 PM - Raymond Barley Back To Top (22484)

SSIS in SQL 2012 has built-in support for consuming CDC; I haven't used it yet but here's the link:

If you're using an earlier version of SQL Server I did another tip that shows consuming CDC from SSIS:

Thursday, February 28, 2013 - 10:43:06 AM - Mark Back To Top (22480)

Very very impressed! Gtreat feature for us to use and an excellent article.

Rather selfishly I would be very interested to see the other end of this work. I am looking at using CDC alongsode SSIS to help me track changes in the operational data store and provide just these changes to the data warehouse.

So I am expecting to get 20 to 30 tables that have a list of changes. In there will be three fact tables with 100,000s of rows that are just changes.

Is there a good (fast) way to get these changes into the data warehouse?

I am assuming we cant put the Inserts in frist then the updates then the deletes as it did not happen in that order! I am expecting to have to go through row by row and either Insert / Update / Delete


Any help greatly appreciated as this looks like an excellent way to populate large data warehouse.



Saturday, February 9, 2013 - 12:05:47 PM - Ray Barley Back To Top (22028)

For simplicity let's say you want to capture the changes that occur in a single table in master.  Create a table with the same schema in another database (let's call it target).  Periodically execute a MERGE T-SQL command where you use the table in master as the source and your target table as the target.  MERGE will allow you to capture the inserts, updates and deletes by using the OUTPUT clause.  In addition MERGE will update your target table so that it now matches the table in master.

Saturday, February 9, 2013 - 9:51:50 AM - Dinesh Back To Top (22027)

I WANT keep trap in master database but it showing error.Please let some solution.



Exec sys.sp_cdc_enable_db


Msg 22989, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 27

Could not enable Change Data Capture for database 'master'. Change data capture is not supported on system databases, or on a distribution database.


Monday, January 28, 2013 - 6:25:42 AM - Peter Elzinga Back To Top (21745)

Great article Ray,

It will help me to tune our CDC process which causes some problems now because there are BLOBS in the database.

Kind regards



Monday, December 17, 2012 - 12:21:00 AM - Rama Krishna Back To Top (20976)

Hi Ray,

Thank you very much for your reply.  It's a Nice and useful Article on CDC.  Good



Rama Krishna


Friday, December 14, 2012 - 8:02:02 AM - Raymond Barley Back To Top (20957)

I don't think there is any built-in support in CDC to give you user or ipaddress.  You will have to add columns to your tables so that CDC can give you these values; e.g lastmodifiedby, lastmodifiedbyipaddress.  You will need to set these columns' values yourself.

To get the user you could use SYSTEM_USER - see:

To get the ipaddress you could use CONNECTIONPROPERTY - see:


Friday, December 14, 2012 - 5:22:02 AM - Rama Krishna Back To Top (20955)

Hi Ray,

Good Article. 

How to capture the name or ipaddress of the user who ins/modified/dele in the CDC table?






Wednesday, November 28, 2012 - 10:07:13 AM - Ray Barley Back To Top (20592)

According to

supports_net_changes is bit with a default of 1 if the table has a primary key or the table has a unique index that has been identified by using the @index_name parameter. Otherwise, the parameter defaults to 0.

Wednesday, November 28, 2012 - 8:13:08 AM - Ash Back To Top (20586)

Thank you for ur prompt reply!!

I did not face any such problems for the other tables i enabled cdc for.

Also i used the same query to enable cdc for the other y is it necessary to set @supports_net_changes = 1 for this table.


Wednesday, November 28, 2012 - 7:16:32 AM - Raymond Barley Back To Top (20584)

I just went through the steps to create the table and enable it for CDC.  I didn't get any errors.  I went back and read your initial post and now I see the issue.  In order for the net changes function to be generated, you need to add the parameter @supports_net_changes = 1 to sys.sp_cdc_enable_table .  In addition your table has to have a primary key defined or you have to specify the @index_name parameter which must be a unique index.  Check out the details here:


Wednesday, November 28, 2012 - 6:01:32 AM - Ash Back To Top (20583)

The return code is 0.

there was no error while enabling it..

this is what i used to enable it:





@source_schema ='dbo',






and this is my table structure:

USE [Mediae2e]




CREATE TABLE [dbo].[DASUploadingReason](
 [ReasonID] [int] IDENTITY(1,1) NOT NULL,
 [Reason] [varchar](100) NULL,
 [StatusID] [int] NULL





Tuesday, November 27, 2012 - 9:29:49 AM - Ray Barley Back To Top (20565)

When you enable CDC on the table what is the return code; e.g. what's the value of @rc; it should be 0; 1 would be an error; e.g.

declare @rc int

exec @rc = sys.sp_cdc_enable_table 

    @source_schema = 'dbo', 

    @source_name = 'customer' ,

    @role_name = 'CDCRole',

    @supports_net_changes = 1

select @rc


Were there any error messages displayed when you executed sys.sp_cdc_enable_table?


Can you post your create table statement and the sys.sp_cdc_enable_table command?  Maybe someone can figure something out. 

Tuesday, November 27, 2012 - 5:09:07 AM - Ash Back To Top (20558)

I have enabled CDC for some tables in my DB.. While enbaling 2 functions fn_cdc_get_net_changes_dbo_Table_name and fn_cdc_get_net_changes_dbo_Table_name gets created automatially. But for one particular table the fn_cdc_get_net_changes_dbo_Table_name does not get created.. i have tried disabling and enabling but the fn_cdc_get_net_changes_dbo_Table_name  but still the prob exisits.. Please provide a soliton

Monday, October 22, 2012 - 11:18:26 AM - Raymond Barley Back To Top (20039)

Change Data Capture allows you to log inserts, updates and deletes on selected tables.  SQL Audit allows you to capture events at the database level or database server level.  You can get further details on SQL Audit here:

Here are the "events" that SQL Audit can capture:

Monday, October 22, 2012 - 6:16:17 AM - Santy Back To Top (20029)

Can you please tell me the diffence between Change Data Capture and SQl Audit.

Wednesday, May 9, 2012 - 6:49:43 AM - Raymond Barley Back To Top (17352)

You'll need to refer to the cdc.fn_cdc_get_all_changes_; you can get the full details here:

This function gets created for you wjhen you setup CDC for a table.  The trick is that when you use the function in a query you have to specify 'all update old' for the row filter option parameter.  When you do this you get all of the change details; i.e. every insert, update and delete.  For updates you actually get 2 rows - the row values before the update and the row values after the update.

The result set from the cdc.fn_cdc_get_all_changes_ has a $__operation column with the following values:

1 = delete

2 = insert

3 = update (captured column values are those before the update operation). This value applies only when the row filter option 'all update old' is specified.

4 = update (captured column values are those after the update operation)


So you will have to join the 3s and 4s based on the $__start_lsn and $__seq_val to get the output you're looking for.  The result set from the query has all of the columns and their values that were configured for CDC.

Below is one of the T-SQL scripts from the tip with the  row filter option parameter set to 'all update old'




@begin_lsn binary(10), @end_lsn binary(10




-- get the first LSN for customer changes




@begin_lsn =sys.fn_cdc_get_min_lsn('dbo_customer'




-- get the last LSN for customer changes




@end_lsn =sys.fn_cdc_get_max_lsn()ange by the pk


-- get individual changes in the range




*from cdc.fn_cdc_get_all_changes_dbo_customer





, @end_lsn,'all update old');

Monday, May 7, 2012 - 3:01:14 AM - Vidhyut Back To Top (17290)


Very nice article. Could you please share how to know the name of columns which have been saved? It should show Column Name, Old Value, New Value, Type of Operation performed.

Thanks in advance.

Wednesday, April 25, 2012 - 12:30:28 PM - clement Back To Top (17108)


Wednesday, April 25, 2012 - 11:43:01 AM - Ray Barley Back To Top (17107)

You can find the details here: 

CDC creates a SQL Agent job that scans the transaction log and extracts the details of inserts, updates, and deletes to CDC-enabled tables.  The details are written to another set of tables (i.e. the change tables) that you can then query using some table-valued functions.    

As a general statement CDC does not cause a big impact on performance as the change tables are populated asynchronously (not part of your transactions).

Wednesday, April 25, 2012 - 7:16:25 AM - Clement Back To Top (17097)

in terms of performance, what is CDC underneath?

A transactional trigger? an asynchroneous message to a broker? How does it affect performance?

Tuesday, March 27, 2012 - 2:36:03 PM - Paul Back To Top (16648)

Thanks Ray!

Tuesday, March 27, 2012 - 1:52:26 PM - Ray Barley Back To Top (16645)

Inserts and deletes will always be reflected in the CDC table; an update will only be there if one of the columns in the captured column list changes

Tuesday, March 27, 2012 - 12:35:27 PM - Paul Back To Top (16644)

Thanks, I tested it out yesterday and received the same message.  Looks like I will have to enable CDC at the column level.  One more question: By default, if you enable CDC on a table, change tracking is set on each column in that table. I also see a parameter within the enable CDC command for specific columns - @captured_column_list where you are to list out the columns to enable CDC. By listing out the specific columns under the @captured_column_list, will I see a changes in the _CT table ONLY if there has been a change to that specific column and not any column in the table?



Monday, March 26, 2012 - 10:35:07 PM - Ray Barley Back To Top (16624)

You can only enable CDC on a table.  When I tried to do it on a view in SQL Server 2008 R2 I got this error:

Msg 22931, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 100

Source table 'dbo.vw_customer' does not exist in the current database. Ensure that the correct database context is set. Specify a valid schema and table name for the database.



Monday, March 26, 2012 - 5:09:45 PM - Paul Back To Top (16619)


Thanks for the walk through and explanation of CDC.  Can CDC be set up on a view directly or will I have to go through each involved table and enable CDC on the specific columns that make up the view?





Tuesday, March 20, 2012 - 4:52:33 AM - Srinath Back To Top (16527)

What a great piece of information about CDC...Want to see more articles from you, Ray!! Thank you!

Wednesday, March 14, 2012 - 6:28:08 PM - Mallikarjun Back To Top (16401)


Nice to work with this article. when i'm trying to enable the database for CDC i'm receiving the following error. where as i have done R&D and they all didn't work.

a) i have changed my database to owner as admin. Still it's not working.



Msg 3930, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 178The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.


Msg 22830, Level 16, State 1, Procedure sp_cdc_enable_db_internal, Line 186


Could not update the metadata that indicates database CDC_DBA is enabled for Change Data Capture. The failure occurred when executing the command 'create user cdc'. The error returned was 916: 'The server principal "sa" is not able to access the database "DBAdmin" under the current security context.'. Use the action and error to determine the cause of the failure and resubmit the request.


Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db_internal, Line 0


Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.


Msg 266, Level 16, State 2, Procedure sp_cdc_enable_db, Line 0


Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.


Msg 3998, Level 16, State 1, Line 1


Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.







Monday, January 3, 2011 - 3:31:29 PM - Ray Barley Back To Top (12479)

Good point.

I use the developer edition (which is the same feature set as Enterprise) so I sometimes forget to point out the edition requirement.


Monday, January 3, 2011 - 2:38:16 PM - Sue Back To Top (12478)

Change Data Capture is Enterprise Edition

get free sql tips
agree to terms