docs: describe which SQL Server constraint Materialize records as a key by peterdukelarsen · Pull Request #40201 · MaterializeInc/materialize

peterdukelarsen · 2026-10-26T20:19:39Z

Documents the behavior from #40131: a SQL Server table records at most one key, the PRIMARY KEY or UNIQUE constraint behind the capture instance's index. Replaces the "Dropping a UNIQUE constraint puts the table in an error state" row, which did not match the replication path even before that change.

Also adds the v26.49.0 release notes for the change: an Improvements bullet, and an Advisory that re-creating a SQL Server table (blue/green deployment, source versioning) can change the plans of dependent objects, for example a DISTINCT that used to be removed is now kept. Results do not change.

Merge after v26.49 has rolled out.

Pages for review:

🤖 Generated with Claude Code

Co-Authored-By: Claude Fable 5.1 <[email protected]>

Co-Authored-By: Claude Opus 5.5 <[email protected]>

…lans Co-Authored-By: Claude Fable 5.1 <[email protected]>

…'s index Co-Authored-By: Claude Fable 5.1 <[email protected]>

Co-Authored-By: Claude Fable 5.1 <[email protected]>

val-materialize

Thanks, this covers the docs and release-note asks. Approving once the Advisory line is in.

val-materialize · 2026-11-03T16:28:33Z

+
+### Advisory {#v26.49-advisory}
+
+- **Query plans may change when a SQL Server source table is re-created**: Starting with this release, a table created from a SQL Server source records at most one key, the `PRIMARY KEY` or `UNIQUE` constraint behind its CDC capture instance's index (see [Unique keys](/ingest-data/sql-server/#unique-keys)). Existing tables are not affected, but re-creating the table in Materialize, for example through the [source versioning](/ingest-data/sql-server/source-versioning/) procedure or a blue/green deployment that also re-creates the source's tables, creates it with the new key set. If the table had other `PRIMARY KEY` or `UNIQUE` constraints, views, indexes, and materialized views that depend on it can get a different plan, for example a `DISTINCT` over the columns of a no longer recorded constraint is kept instead of removed, and may use more memory or CPU as a result. Query results do not change.


"Existing tables are not affected" is accurate for plan changes, but a reader could take it to mean existing tables are safe overall. They still carry every key recorded at creation, so dropping one of those constraints upstream can still return wrong results (SS-371). That's covered in the schema-change docs but not here. Could we add a line to the Advisory, something like:

Tables created before this release keep all the keys they recorded at creation. If you plan to drop a PRIMARY KEY or UNIQUE constraint upstream that the capture instance does not use, re-create the table in Materialize first.

If there's an easy way for users to see which keys a table has recorded, linking it here would help too.

val-materialize · 2026-11-03T16:28:33Z

+
+- **Query plans may change when a SQL Server source table is re-created**: Starting with this release, a table created from a SQL Server source records at most one key, the `PRIMARY KEY` or `UNIQUE` constraint behind its CDC capture instance's index (see [Unique keys](/ingest-data/sql-server/#unique-keys)). Existing tables are not affected, but re-creating the table in Materialize, for example through the [source versioning](/ingest-data/sql-server/source-versioning/) procedure or a blue/green deployment that also re-creates the source's tables, creates it with the new key set. If the table had other `PRIMARY KEY` or `UNIQUE` constraints, views, indexes, and materialized views that depend on it can get a different plan, for example a `DISTINCT` over the columns of a no longer recorded constraint is kept instead of removed, and may use more memory or CPU as a result. Query results do not change.
+
+  **Upgrade impact**: Before promoting a deployment that re-creates SQL Server source tables, compare the plans of the objects that depend on them with [`EXPLAIN PLAN`](/sql/explain-plan/). To keep a `UNIQUE` constraint other than the primary key as the table's key, create the capture instance with [`@index_name`](/ingest-data/sql-server/#unique-keys) set to that constraint.


Small one: maybe add "in SQL Server" to the @index_name sentence so it's clear this is an upstream change and not something done in Materialize.

Decided to delete this line because I don't think we want to recommend tweaking the unique key. I changed the recommendation to explain the plan and then to upside if needed.

Co-Authored-By: Claude Fable 5.1 <[email protected]>

val-materialize

Thanks, the Advisory reads well now. Agree on dropping the @index_name recommendation. LGTM.

Empty on purpose. The final snapshot of the v26.49.0 train carries no entries: MaterializeInc/release#635 states "No PRs in v26.49.0 require review", the compare v26.49.0-rc.3...v26.49.0 is the single version-bump commit a14e1a0, and the agent-skills window (2026-11-03T20:34:06Z, 2026-11-04T19:10:38Z] is empty. The real dates (Cloud 2026-11-05, Self-Managed 2026-11-06) are already on this branch: the v26.49.1 patch train reused this branch in 39e6b85 and produced the combined "## v26.49.1" section the house convention calls for when a patch ships in its parent's rollout week, with the Cloud version in parentheses. The compatibility YAML likewise already carries the v26.49.1 row dated 2026-11-06. This commit therefore touches no files; it exists so the branch's commit-subject ledger records that the final snapshot was applied. Two items still need the release owner, and are described in the PR body: the conflict with main's own "## v26.49.0" section from #40201, and the duplicate {#v26.49-improvements} anchor.

Keep the SQL Server Advisory and Improvements bullet from #40201 under the v26.49.1 heading, and drop the duplicate SQL Server bug fix line. Co-Authored-By: Claude Opus 5.5 <[email protected]>

Empty on purpose. The final snapshot of the v26.49.0 train carries no entries: MaterializeInc/release#635 states "No PRs in v26.49.0 require review", the compare v26.49.0-rc.3...v26.49.0 is the single version-bump commit a14e1a0, and the agent-skills window (2026-11-03T20:34:06Z, 2026-11-04T19:10:38Z] is empty. The real dates (Cloud 2026-11-05, Self-Managed 2026-11-06) are already on this branch: the v26.49.1 patch train reused this branch in 39e6b85 and produced the combined "## v26.49.1" section the house convention calls for when a patch ships in its parent's rollout week, with the Cloud version in parentheses. The compatibility YAML likewise already carries the v26.49.1 row dated 2026-11-06. This commit therefore touches no files; it exists so the branch's commit-subject ledger records that the final snapshot was applied. Two items still need the release owner, and are described in the PR body: the conflict with main's own "## v26.49.0" section from #40201, and the duplicate {#v26.49-improvements} anchor.

Co-Authored-By: Claude Opus 5.5 <[email protected]>

Dates in RC snapshots are provisional until the release ships. One commit is added per snapshot; the final snapshot sets the real dates. ## ⚠️ Needs the release owner before this can merge **1. This PR conflicts with `main`, and the conflict is substantive.** While this PR sat open, [#40201](#40201) ("docs: describe which SQL Server constraint Materialize records as a key") merged to `main` on 2026-11-05 and independently added its own `## v26.49.0` section — an `### Advisory {#v26.49-advisory}` plus an `### Improvements {#v26.49-improvements}` bullet. Both sides edit the same region of `_index.md`, so `mergeable` is `CONFLICTING`. Two problems ride along with the conflict: - **The same PR is described twice.** [#40201](#40201) documents the behavior of [#40131](#40131), which `rc.1` already published here as a Bug Fix ("Reduced the risk of new SQL Server source tables returning incorrect results after a unique constraint or primary key is dropped upstream…"). *Recommendation: keep `#40201`'s Advisory and Improvement and drop the `rc.1` bug-fix bullet* — the hand-written pair is more complete, carries the upgrade-impact guidance, and links `/ingest-data/sql-server/#unique-keys`. - **Anchor collision.** `main`'s `## v26.49.0` carries `{#v26.49-advisory}` and `{#v26.49-improvements}`; this branch's combined section carries `{#v26.49-improvements}`, `{#v26.49-agent-skills}`, and `{#v26.49-bug-fixes}`. Merging both defines `#v26.49-improvements` twice on one page. *Recommendation: rename this branch's three anchors to `{#v26.49.1-improvements}`, `{#v26.49.1-agent-skills}`, `{#v26.49.1-bug-fixes}`* — which is what the `v26.49.1` snapshot's review notes intended — *and leave `{#v26.49-advisory}` and `{#v26.49-improvements}` to `#40201`'s section.* `#40201`'s review links already point at `/releases/#v26.49-advisory`, and the compatibility YAML's `/releases/#v26491` target is unaffected. **2. Wording drift against the merged `v26.49.1` snapshot.** Commit `39e6b854a` landed here on 2026-11-05, before [MaterializeInc/mz-skills#355](MaterializeInc/mz-skills#355) merged on 2026-11-06. Two entries read differently on the branch than in the merged snapshot — the hydration improvement is "Investigate hydration on the cluster details page" here versus "See how a cluster hydrated, and why it did not" there, and the Freshness and Disk-column bullets differ likewise. Resolving the conflict above is the natural moment to decide which wording ships. ## Note on the section heading The `v26.49.1` patch train reused this branch (commit `39e6b854a`) rather than opening its own, so this PR now publishes a single combined `## v26.49.1` section — this train's `rc.1`–`rc.3` entries plus the three `v26.49.1` entries, with the Cloud version in parentheses. That is the house convention for a patch that ships in its parent's rollout week; compare the published `## v26.48.2` (`*Released to Materialize Cloud: 2026-10-29 (v26.48.0)*`). The compatibility YAML accordingly carries one `v26.49.1` row dated 2026-11-06, which is what actually shipped to Self-Managed that day. The `final` commit for this train is therefore **empty**: its increment carries no entries ([MaterializeInc/release#635](MaterializeInc/release#635) states "No PRs in `v26.49.0` require review", `v26.49.0-rc.3...v26.49.0` is the lone version-bump commit, and the agent-skills window is empty), and the real dates — Cloud **2026-11-05**, Self-Managed **2026-11-06** — were already set by `39e6b854a`. It exists so the branch's commit-subject ledger records that the snapshot was applied. ## Decisions required (carried over from `rc.1`) - **`DROP INDEX`** ([#39098](#39098)): it now rejects drops that used to succeed. Call it out as a breaking change rather than an Improvement? - **Console on large clusters** ([#40306](#40306)): the Cloud Console deploys separately, so this may already be live. Keep it in v26.49? - **Maintained Objects page** ([#40105](#40105)): omitted because it is behind a LaunchDarkly flag. Is it generally available? - **`mz profile init` OAuth** ([#40319](#40319)): omitted because the PR lists open items. Which `mz` release carries it? - **Upsert v2** ([#40189](#40189)): still omitted because `enable_upsert_v2` is `false`. Publish it? ## Snapshot drafts (with PR links) in mz-skills - rc.1: [release_notes.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.1/release_notes.md), [omitted.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.1/omitted.md), [MaterializeInc/mz-skills#351](MaterializeInc/mz-skills#351) - rc.2: [release_notes.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.2/release_notes.md), [omitted.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.2/omitted.md), [MaterializeInc/mz-skills#352](MaterializeInc/mz-skills#352) - rc.3: [release_notes.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.3/release_notes.md), [omitted.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.3/omitted.md), [MaterializeInc/mz-skills#353](MaterializeInc/mz-skills#353) - final: [release_notes.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/final/release_notes.md), [omitted.md](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/final/omitted.md) **Borderline PRs omitted:** rc.1: 10 — [rc.1/omitted.md#borderline](https://github.com/MaterializeInc/mz-skills/blob/main/data/mz-release-notes/v26.49.0/rc.1/omitted.md#borderline), rc.2: 0, rc.3: 0, final: 0 🤖 Generated with [Claude Code](https://claude.com/claude-code) --------- Co-authored-by: Claude <[email protected]> Co-authored-by: Valerie Chiang <[email protected]> Co-authored-by: Pranshu Maheshwari <[email protected]>

peterdukelarsen and others added 3 commits November 2, 2026 17:49

docs: describe which SQL Server constraint Materialize records as a key

c8c439c

Co-Authored-By: Claude Fable 5.1 <[email protected]>

docs: drop the SQL Server error number from the constraint note

23505df

Co-Authored-By: Claude Opus 5.5 <[email protected]>

docs: tighten SQL Server key docs after self-review

ac8eecb

Co-Authored-By: Claude Opus 5.5 <[email protected]>

peterdukelarsen force-pushed the plarsen/sqlserver-key-docs branch from fbd7dcd to ac8eecb Compare November 2, 2026 22:57

peterdukelarsen and others added 2 commits November 2, 2026 18:04

docs: title the SQL Server key section Unique keys

9da049d

Co-Authored-By: Claude Opus 5.5 <[email protected]>

docs: scope the SQL Server constraint-drop note to v26.49 tables

1071b68

Co-Authored-By: Claude Opus 5.5 <[email protected]>

peterdukelarsen marked this pull request as ready for review November 2, 2026 23:07

peterdukelarsen requested a review from a team as a code owner November 2, 2026 23:07

peterdukelarsen requested a review from val-materialize November 2, 2026 23:13

peterdukelarsen mentioned this pull request Nov 2, 2026

docs: describe which SQL Server constraint Materialize records as a key #40433

Closed

peterdukelarsen and others added 3 commits November 2, 2026 18:22

release: advise that re-created SQL Server tables can get different p…

cfde2ab

…lans Co-Authored-By: Claude Fable 5.1 <[email protected]>

release: note that SQL Server tables key only on the capture instance…

00e42be

…'s index Co-Authored-By: Claude Fable 5.1 <[email protected]>

docs: scope the re-create advice to the constraint that was recorded

91e1524

Co-Authored-By: Claude Fable 5.1 <[email protected]>

val-materialize reviewed Nov 3, 2026

View reviewed changes

pr feedback

94779ca

Co-Authored-By: Claude Fable 5.1 <[email protected]>

val-materialize approved these changes Nov 3, 2026

View reviewed changes

val-materialize merged commit 4589239 into MaterializeInc:main Nov 5, 2026
11 checks passed

bosconi mentioned this pull request Nov 6, 2026

v26.49.0 release notes #40469

Merged

val-materialize added a commit that referenced this pull request Nov 6, 2026

Restore Peter's v26.49 SQL Server Advisory wording from #40201

f7252c6

Co-Authored-By: Claude Opus 5.5 <[email protected]>

Navigation Menu

Repository navigation

docs: describe which SQL Server constraint Materialize records as a key - #40201

docs: describe which SQL Server constraint Materialize records as a key#40201
val-materialize merged 9 commits into
MaterializeInc:mainfrom
peterdukelarsen:plarsen/sqlserver-key-docs

peterdukelarsen commented Oct 26, 2026 •

edited by github-actions Bot

Loading

Uh oh!

val-materialize left a comment

Uh oh!

val-materialize Nov 3, 2026

Uh oh!

val-materialize Nov 3, 2026

Uh oh!

peterdukelarsen Nov 3, 2026

Uh oh!

val-materialize left a comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants


		### Advisory {#v26.49-advisory}

		- Query plans may change when a SQL Server source table is re-created: Starting with this release, a table created from a SQL Server source records at most one key, the `PRIMARY KEY` or `UNIQUE` constraint behind its CDC capture instance's index (see [Unique keys](/ingest-data/sql-server/#unique-keys)). Existing tables are not affected, but re-creating the table in Materialize, for example through the [source versioning](/ingest-data/sql-server/source-versioning/) procedure or a blue/green deployment that also re-creates the source's tables, creates it with the new key set. If the table had other `PRIMARY KEY` or `UNIQUE` constraints, views, indexes, and materialized views that depend on it can get a different plan, for example a `DISTINCT` over the columns of a no longer recorded constraint is kept instead of removed, and may use more memory or CPU as a result. Query results do not change.


		- Query plans may change when a SQL Server source table is re-created: Starting with this release, a table created from a SQL Server source records at most one key, the `PRIMARY KEY` or `UNIQUE` constraint behind its CDC capture instance's index (see [Unique keys](/ingest-data/sql-server/#unique-keys)). Existing tables are not affected, but re-creating the table in Materialize, for example through the [source versioning](/ingest-data/sql-server/source-versioning/) procedure or a blue/green deployment that also re-creates the source's tables, creates it with the new key set. If the table had other `PRIMARY KEY` or `UNIQUE` constraints, views, indexes, and materialized views that depend on it can get a different plan, for example a `DISTINCT` over the columns of a no longer recorded constraint is kept instead of removed, and may use more memory or CPU as a result. Query results do not change.

		Upgrade impact: Before promoting a deployment that re-creates SQL Server source tables, compare the plans of the objects that depend on them with [`EXPLAIN PLAN`](/sql/explain-plan/). To keep a `UNIQUE` constraint other than the primary key as the table's key, create the capture instance with [`@index_name`](/ingest-data/sql-server/#unique-keys) set to that constraint.

Conversation

peterdukelarsen commented Oct 26, 2026 • edited by github-actions Bot Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

val-materialize left a comment

Choose a reason for hiding this comment

Uh oh!

val-materialize Nov 3, 2026

Choose a reason for hiding this comment

Uh oh!

val-materialize Nov 3, 2026

Choose a reason for hiding this comment

Uh oh!

peterdukelarsen Nov 3, 2026

Choose a reason for hiding this comment

Uh oh!

val-materialize left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Reviewers

Uh oh!

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

peterdukelarsen commented Oct 26, 2026 •

edited by github-actions Bot

Loading