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.