Skip to content

0009: Read the change table directly

Status: accepted. Supersedes the read path described in invariants 2–4 of CLAUDE.md before this change.
Date: 2026-09-28T17:39:20-03:00
Amended: 2026-09-28T18:11:50-03:00, the one extra grant and the gating-role trade-off

Context

The reader queried cdc.fn_cdc_get_all_changes_<ci>(from, to, N'all update old') and, by default (includeCommandId=true), selected and ordered by __$command_id. On SQL Server 2022 the function does not return that column: every default read failed with Invalid column name '__$command_id' (CI lab t3, and a strict xfail in tests/integration).

__$command_id is not optional detail. The change table documentation says __$seqval "should not be used for ordering. Instead, use the __$command_id column", and that __$start_lsn, __$command_id and __$operation together preserve the commit order. The column exists in cdc.<ci>_CT (added by a cumulative update in 2012–2016, KB 3030352), just not in the function's result.

The function also did one thing for us: it rejects a range below the capture instance's low watermark (Msg 313). A range that CDC cleanup purged therefore failed the read.

Decision

SqlCdcClient.iter_changes reads cdc.[<ci>_CT] with WHERE __$start_lsn BETWEEN from AND to, joined to cdc.lsn_time_mapping for the commit time, ordered by (__$start_lsn, __$command_id, __$seqval, __$operation). 'all update old' needs no filter: the change table holds operations 1–4.

A purged range now reads as empty instead of failing, so the retention guard runs twice:

  1. on the driver, in partitions(), as before;
  2. on the executor, after a range is read: if fn_cdc_get_min_lsn(ci) is now past the range's from_lsn, raise DataLossError (unless failOnDataLoss=false).

The second check is sound because sys.sp_cdc_cleanup_change_table first moves the capture instance's start_lsn (what fn_cdc_get_min_lsn returns) and only then deletes change rows below it. Any deletion that could have touched the range is visible as a moved low watermark by the time the read finishes.

Consequences

  • includeCommandId=true works (verified on SQL Server 2022) and ordering follows the documented key. Servers without the column need includeCommandId=false.
  • Microsoft recommends the query functions over the change tables. We depend on the documented change-table columns and on the documented cleanup order. t3 and tests/integration check the columns, and that cleanup moves min_lsn and deletes the rows below it; the move-before-delete order is taken from the documentation (see docs/REFERENCES.md) and cannot be observed from a test.
  • One extra grant. The query functions reach the change table through ownership chaining, so a login with SELECT on the source columns (plus the gating role) can call them without any right on cdc.<ci>_CT. Reading the table directly needs GRANT SELECT ON cdc.[<ci>_CT] TO <user>, per capture instance; nothing else in the cdc schema (cdc.lsn_time_mapping and the sys.fn_cdc_* functions are readable without it). A denied read raises PermissionError naming that grant. Checked with a least-privilege login against SQL Server 2022 (tests/integration).
  • That grant sidesteps the gating role: the function checks the role, the table does not. The data exposed is the same, but whoever relied on the role now controls access with the grant.
  • One more fn_cdc_get_min_lsn query per task.
  • FakeCdcDatabase.cleanup now also deletes the rows below the watermark, like the real procedure, and FakeCdcClient.iter_changes no longer emulates Msg 313.