SQL Server CDC __$operation Values 1, 2, 3, and 4 Explained

DBA PARK may earn a commission from purchases through links in this article, at no extra cost to you.

SQL Server Change Data Capture records row changes in a change table whose metadata columns start with __$. The value that causes the most confusion is __$operation: 1, 2, 3, or 4. Those numbers describe the row image, not the order in which your application should apply changes.

What You’ll Learn: what each operation code means, when update code 3 appears, how to query changes by LSN, how to pair update rows, and how to design a reliable consumer.

1. Operation Code Reference

__$operation	Meaning	Row image
1	DELETE	Values before deletion.
2	INSERT	Values after insertion.
3	UPDATE (before)	Old values before the update; returned only when requesting old update rows.
4	UPDATE (after)	New values after the update.

The capture job writes change-table rows. Your query option decides whether an update is returned only as code 4 or as the code 3 + code 4 pair.

2. Reproduce the Four Values

USE YourDatabase;
GO
EXEC sys.sp_cdc_enable_db;
GO

CREATE TABLE dbo.CustomerStatus
(
    customer_id int PRIMARY KEY,
    status varchar(20) NOT NULL,
    modified_at datetime2(3) NOT NULL DEFAULT sysdatetime()
);

EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name   = N'CustomerStatus',
    @role_name     = NULL,
    @supports_net_changes = 1;
GO

INSERT dbo.CustomerStatus(customer_id,status) VALUES(1,'New');
UPDATE dbo.CustomerStatus SET status='Active', modified_at=sysdatetime() WHERE customer_id=1;
DELETE dbo.CustomerStatus WHERE customer_id=1;

Wait for the capture job, or confirm that SQL Server Agent and the CDC capture job are running. The generated capture instance is normally dbo_CustomerStatus.

3. Read All Changes With Old Update Values

DECLARE @from_lsn binary(10) = sys.fn_cdc_get_min_lsn('dbo_CustomerStatus');
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

SELECT __$start_lsn,
       __$seqval,
       __$operation,
       __$update_mask,
       customer_id,
       status,
       modified_at
FROM cdc.fn_cdc_get_all_changes_dbo_CustomerStatus
     (@from_lsn, @to_lsn, 'all update old')
ORDER BY __$start_lsn, __$seqval, __$operation;

Use all update old when an audit, comparison, or downstream system needs both sides of an update. Use all when only the final update row is required.

4. Understand the LSN Columns

Column	Purpose
__$start_lsn	Commit LSN shared by changes in the same transaction. Use it as the main window boundary.
__$seqval	Orders row changes within the transaction. It is not a wall-clock timestamp.
__$operation	Row-image type: delete, insert, update-before, or update-after.
__$update_mask	Bit mask indicating captured columns affected by an update.
__$command_id	Additional ordering metadata on supported versions; do not invent ordering from the primary key.

5. Map LSNs to Commit Time

SELECT sys.fn_cdc_map_lsn_to_time(__$start_lsn) AS commit_time,
       __$operation, customer_id, status
FROM cdc.fn_cdc_get_all_changes_dbo_CustomerStatus
     (@from_lsn, @to_lsn, 'all update old')
ORDER BY __$start_lsn, __$seqval, __$operation;

Commit time is useful for operations and lag monitoring, but the consumer checkpoint should use LSNs. Timestamps can collide and do not define a lossless extraction boundary.

6. Build a Lossless Consumer Window

Persist the last successfully processed ending LSN.
Calculate the next valid starting LSN with sys.fn_cdc_increment_lsn.
Capture one ending LSN for the batch.
Read and process changes in transaction/sequence order.
Commit the downstream work and the ending-LSN checkpoint atomically when possible.
Detect retention gaps before querying.

DECLARE @from_lsn binary(10) = sys.fn_cdc_increment_lsn(@last_success_lsn);
DECLARE @to_lsn   binary(10) = sys.fn_cdc_get_max_lsn();

IF @from_lsn < sys.fn_cdc_get_min_lsn('dbo_CustomerStatus')
    THROW 50001, 'CDC retention gap detected; reinitialize the consumer.', 1;

7. Common Misinterpretations

Code 3 is not “update failed”; it is the before image.
Codes 3 and 4 are not independent business events. Pair them using the change metadata and capture instance semantics.
The highest primary key is not the latest change.
Reading the physical cdc.*_CT table can be useful for diagnosis, but the generated CDC functions provide the supported extraction interface.
CDC is not an indefinite audit archive. Cleanup retention can remove unread rows.

8. Operational Checks

EXEC sys.sp_cdc_help_jobs;
EXEC sys.sp_cdc_help_change_data_capture;

SELECT session_id, start_time, end_time, tran_count, error_count
FROM sys.dm_cdc_log_scan_sessions
ORDER BY session_id DESC;

Summary

Operation values are simple once you separate event type from ordering: 1 delete, 2 insert, 3 update-before, and 4 update-after. Reliable CDC consumers checkpoint LSNs, request the correct update-row option, monitor retention, and treat update pairs as one logical change.

Continue learning: To get more comfortable querying and interpreting row changes, browse Udemy and search for T-SQL courses with exercises on data modification, joins, and transactions. For guided practice with relational database design and SQL, explore DataCamp.