Change Tracking and Change Data Capture

Records which rows changed, but not the old values.

29.1 Change Tracking (Lightweight)

Records which rows changed, but not the old values.

ALTER DATABASE ShopDB SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);
ALTER TABLE dbo.Customers ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

SELECT ct.CustomerID, ct.SYS_CHANGE_OPERATION, ct.SYS_CHANGE_VERSION
FROM CHANGETABLE(CHANGES dbo.Customers, @last_sync_version) ct;
SELECT CHANGE_TRACKING_CURRENT_VERSION();

29.2 Change Data Capture (CDC)

Reads the transaction log and records full before and after values. Uses SQL Agent jobs.

EXEC sys.sp_cdc_enable_db;
EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', @source_name = 'Orders', @role_name = NULL;

DECLARE @from BINARY(10) = sys.fn_cdc_get_min_lsn('dbo_Orders'), @to BINARY(10) = sys.fn_cdc_get_max_lsn();
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Orders(@from, @to, N'all update old');
-- __$operation: 1 = delete, 2 = insert, 3 = before update, 4 = after update

29.3 Change Event Streaming (SQL Server 2025)

Streams changes directly to Azure Event Hubs (Kafka-compatible) in real time, without CDC staging tables.