Change Data Capture In SQL Server

CREATE DATABASE CDC_DEMO  
GO  

USE CDC_DEMO  
GO  
  
CREATE TABLE CDC_DEMO_TABLE1  
(   
    ID      INT         IDENTITY(1,1) PRIMARY KEY,  
    Name        VARCHAR(50)     NOT NULL,  
    Age     INT         NOT NULL,  
);  
GO  

USE CDC_DEMO  
GO  
  
INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Akshay',34)  
GO  
INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Kaushal',38)  
GO  

USE CDC_DEMO  
GO  
  
INSERT INTO CDC_DEMO_TABLE1 (Name,Age) VALUES ('Jignesh',35)  
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.

USE CDC_DEMO  
GO  
  
UPDATE CDC_DEMO_TABLE1  
SET Name = 'Jigi'  
WHERE id = 3  
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 updated 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.

USE CDC_DEMO  
GO  
  
DELETE FROM CDC_DEMO_TABLE1  
WHERE id = 3  
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 a change in 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 in 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_history

   Provides 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:

   USE CDC_DEMO  
   GO  
     
   ALTER TABLE CDC_DEMO_TABLE1  
   ALTER COLUMN Name VARCHAR(100) NOT NULL  
   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_columns

   Provides the information if any of the index columns are changed.

   

5. Cdc.Isn_time_mapping

   Provides information about the start and end time for the operation done for changes.

   

6. Cdc.systranschemas

   Provides the information for the schema changes.

   

Step 9. Disable CDC on Table

Execute the following query to disable CDC on the table.

USE CDC_DEMO;  
GO  
  
EXECUTE sys.sp_cdc_disable_table  
    @source_schema = N'dbo',  
    @source_name = N'CDC_DEMO_TABLE1',  
    @capture_instance = N'dbo_CDC_DEMO_TABLE1'  
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.

USE CDC_DEMO  
GO  
EXEC sys.sp_cdc_disable_db  
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.

Conclusion

This article taught us how to enable Change Data Capture (CDC) in SQL Server for a database and table.