Change Data Capture in SQL Server

Change Data Capture (CDC) captures the data of insert, update and delete activity. When you insert or delete the data in the table it maintains a record for the same data. When you update the data it maintains records for before updating the data and after updating the data.

To understand the change data capture we go through the following process.

Process

Step 1: Create DB 

1. CREATE DATABASE CDC_DEMO  
2. GO  

Step 2: Create Table

Create one table in the preceding database.

Execute the following query and the "CDC_DEMO_TABLE1" table is created. 

1. USE CDC_DEMO  
2. GO  
3.   
4. CREATE TABLE CDC_DEMO_TABLE1  
5. (   
6.     ID      INT         IDENTITY(1,1) PRIMARY KEY,  
7.     Name        VARCHAR(50)     NOT NULL,  
8.     Age     INT         NOT NULL,  
9. );  
10. GO  

You can check the table in the Object Explorer. 

Step 3: Insert Rows

Insert some rows into the table "CDC_DEMO_TABLE1".

Here we inserted two rows into the table. 

1. USE CDC_DEMO  
2. GO  
3.   
4. INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Akshay',34)  
5. GO  
6. INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Kaushal',38)  
7. GO  

Step 4: Enable CDC on DB

We have a database, table and some rows in the table, now we need to enable CDC on the database.

1. Execute the following query and it will show whether CDC is enabled or not for the database.
   
   1. USE CDC_DEMO  
   2. GO  
   3.   
   4. SELECT name, database_id, is_cdc_enabled    
   5. FROM SYS.DATABASES  
   6. WHERE name = 'CDC_DEMO'  
   
   
   "is_cdc_enabled" has the value "0", that means it is not enabled for the database.
2. Execute the following query to enable CDC on the database. We need to execute the "sys.sp_cdc_enable_db" Stored Procedure to enable CDC on the database. It is necessary to execute it before we know any tables are enabled for the CDC.
   
   1. USE CDC_DEMO  
   2. GO  
   3. EXEC sys.sp_cdc_enable_db  
   4. GO  
   This will create some system tables.
   
   
3. Check again and verify that CDC is enabled on the database.
   
   1. USE CDC_DEMO  
   2. GO  
   3.   
   4. SELECT name, database_id, is_cdc_enabled    
   5. FROM SYS.DATABASES  
   6. WHERE name = 'CDC_DEMO'  
   
   
   Now "is_cdc_enabled" has the value 1, in other words it is enabled.

Step 5: Enable CDC on Table

Enable CDC on the "CDC_DEMO_TABLE1" table.

1. Before enabling CDC, we need to check whether it is enabled already or not. Execute the following query and we have a list of all tables with CDC status.
   
   1. USE CDC_DEMO   
   2. GO   
   3. SELECT [name], is_tracked_by_cdc  FROM SYS.TABLES   
   4. GO    
   
   
   The value of "is_tracked_by_cdc" is "0" for the "CDC_DEMO_TABLE1" table, in other words CDC is not enabled for this table.
2. Execute the following query to enable CDC on the table.
   
   1. USE CDC_DEMO;  
   2. GO  
   3. EXECUTE sys.sp_cdc_enable_table  
   4.   @source_schema = N'dbo'  
   5.   , @source_name = N'CDC_DEMO_TABLE1'  
   6.   , @role_name = NULL  
   7. GO  
   8.           
   We can check in the Object Explorer that one more table is created under the system tables, "cdc.dbo_CDC_DEMO_TABLE1_CT".
   
   
3. Check again and verify that CDC is enabled on the table.
   
   1. USE CDC_DEMO   
   2. GO   
   3. SELECT [name], is_tracked_by_cdc  FROM SYS.TABLES   
   4. GO    
   
   
   Now "is_tracked_by_cdc" has the value 1, that represents that CDC is enabled for the table.

Step 6: Insert Operation

We have enabled CDC for the database and table. Now let's check where SQL Server persists the change log when we insert the data in the table.

Execute the following query to insert one row into the table. 

1. USE CDC_DEMO  
2. GO  
3.   
4. INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Jignesh',35)  
5. GO  

Open the table "CDC_DEMO_TABLE1" and we can see that one row is inserted with the ID 3. 

The change log is captured in the table "cdc.dbo_CDC_DEMO_TABLE1_CT". You can see the entire row that we have created. One more thing you can observe here is that the _$operation value is 2, in other words for Insert values.

Step 7: Update Operation

Now let's check by updating any of the rows in the table. Execute the following script that will update the value of the name field where id = 3.

1. USE CDC_DEMO  
2. GO  
3.   
4. UPDATE CDC_DEMO_TABLE1  
5. SET Name = 'Jigi'  
6. WHERE id = 3  
7. GO  

Open the table and verify that the value is changed. 

Open the "cdc.dbo_CDC_DEMO_TABLE1_CT" table and you can see that the update data is captured in two rows. One is with operation 3 and the other with operation 4. Operation value 3 means before updating and value 4 means after updating.

Step 8: Delete Operation

To check the captured data after the delete operation, execute the following script that deletes the record with id=3. 

1. USE CDC_DEMO  
2. GO  
3.   
4. DELETE FROM CDC_DEMO_TABLE1  
5. WHERE id = 3  
6. GO  

Open the table and verify that the record is deleted from the table.

Open the "cdc.dbo_CDC_DEMO_TABLE1_CT" table and you can see that the deleted row is captured with operation value 1.

We have seen change data capture for insert, update and delete operations and for those only one system table is used, "cdc.dbo_CDC_DEMO_TABLE1_CT". But there are more than six tables that were created when enabling CDC on the database. So let's see the schema and values for those tables:

1. Cdc.captured_columns
   Provides the information of columns that are tracked for the changed data capture.
   
   
2. Cdc.change_tables
   Provides the information of the table. It shows the default value for "capture_instance" since we have not provided a parameter when enabling CDC on the table.
   
   
3. Cdc.ddl_historyProvides the information for any schema changes. Currently this table doesn't have any value since we did not change any schema for the table. So let's change the schema and check the values. Execute the following query to change the schema for the table:
   
   1. USE CDC_DEMO  
   2. GO  
   3.   
   4. ALTER TABLE CDC_DEMO_TABLE1  
   5. ALTER COLUMN Name VARCHAR(100) NOT NULL  
   6. GO  
   We have changed the datatype from varchar(50) to varchar(100) for the name field.
   
   
   
   Open the "cdc.ddl_history" table and we can see that the ddl_command is captured as in the following:
   
   
4. Cdc.index_columnsProvides the information if any of the index column is changed.
   
   
5. Cdc.Isn_time_mappingProvides the information about the start and end time for the operation done for changes.
   
   
6. Cdc.systranschemasProvides the information for the schema changes.
   
   

Step 9: Disable CDC on Table

Execute the following query to disable CDC on the table.

1. USE CDC_DEMO;  
2. GO  
3.   
4. EXECUTE sys.sp_cdc_disable_table  
5.     @source_schema = N'dbo',  
6.     @source_name = N'CDC_DEMO_TABLE1',  
7.     @capture_instance = N'dbo_CDC_DEMO_TABLE1'  
8. GO  

We can observe in the Object Explorer that one table is removed under the system tables, "cdc.dbo_CDC_DEMO_TABLE1_CT". That means CDC is disabled for this table.

Step 10: Disable CDC on Database
Execute the following query to disable CDC on the database. 

1. USE CDC_DEMO  
2. GO  
3. EXEC sys.sp_cdc_disable_db  
4. GO   

We can observe in the Object Explorer that all the tables are removed under the system tables. That means CDC is disabled on the database. 

Note : Change data capture is only available in the Enterprise, Developer, Enterprise Evaluation, and Standard editions.