How to identify it is one update operation for table which has enable cdc

Question

We know that we can use change data capture to monitor the data change on sql database table. I tried to run UPDATE TABLENAME SET PKColumn=NewValue where PKColumn=OldValue

We can see that there will be one row marking the delete operation and one row marking the insert operation for above sql statement in change data capture system table.

My question is:

If the values of __$start_lsn and __$seqval for 2 rows in change data capture system table are same, and the __$operation is 1(delete) and 2(insert), we can say it actually is one update operation for the row?

If not, may I know if any other query criteria we can use to identify it's one update operation on the primary key column?

Thanks

Answer 1

Yes, you can identify a single update operation using Change Data Capture (CDC) in SQL Server by analyzing the __$start_lsn and __$seqval values, along with the __$operation values. When a row is updated, CDC captures this as two operations: a delete (operation type 1) followed by an insert (operation type 2). These operations will have the same __$start_lsn and __$seqval values.

Verify that the __$start_lsn and __$seqval values are the same for both the delete and insert operations and that there is one row with __$operation = 1 (delete) and another row with __$operation = 2 (insert).

Then, compare the primary key values of the deleted row and the inserted row. Since the primary key is being updated, the primary key values will be different.

WITH CDC_Delete AS (

    SELECT *

    FROM cdc.<capture_instance>_CT

    WHERE __$operation = 1

),

CDC_Insert AS (

    SELECT *

    FROM cdc.<capture_instance>_CT

    WHERE __$operation = 2

)

SELECT 

    CDC_Delete.__$start_lsn,

    CDC_Delete.__$seqval,

    CDC_Delete.<PrimaryKeyColumn> AS OldPrimaryKey,

    CDC_Insert.<PrimaryKeyColumn> AS NewPrimaryKey,

    CDC_Delete.<OtherColumns>,

    CDC_Insert.<OtherColumns>

FROM 

    CDC_Delete

JOIN 

    CDC_Insert

ON 

    CDC_Delete.__$start_lsn = CDC_Insert.__$start_lsn

    AND CDC_Delete.__$seqval = CDC_Insert.__$seqval

    AND CDC_Delete.<OtherColumns> = CDC_Insert.<OtherColumns> -- Ensure non-primary key columns match

WHERE 

    CDC_Delete.<PrimaryKeyColumn> <> CDC_Insert.<PrimaryKeyColumn>;

• CDC_Delete: CTE to fetch rows marked as delete operations.
• CDC_Insert: CTE to fetch rows marked as insert operations.
• JOIN Condition: Joins the delete and insert operations on __$start_lsn, __$seqval, and other columns that are not primary keys to ensure they belong to the same transaction.
• PrimaryKey Comparison: Filters rows where the primary key values differ, indicating an update to the primary key.