Two shapes of one query

Two DBAs argue about the same index. One says put the customer column in the key; the other says keep it lean. Here’s the thing nobody wants to hear: they’re both right — just about different servers.

Hey folks. I’m Forge Barrett, master of the Content Forge here at SchemaSmith.

Last module you gated because the old engine couldn’t. GENERATE_SERIES won’t parse at compat 130, so the gate kept broken SQL off the old target — one shape was legal, the other threw. This module is the case where both shapes are legal. Two variants of one query, both valid on both engines, and you’re choosing the better one per tier — not dodging an error. Then we turn the harder screw: gating on something the target can’t detect at all. Let’s kindle the forge.

One question runs this whole module

Before you reach for any gate, ask one thing:

Can the target answer this question itself?

That single question sorts every gate you’ll ever write into two piles:

It can. The fact is detectable — server version, compatibility level, whether some object exists. Gate on the answer and walk away. As each server upgrades, the gate re-answers itself and the fleet converges. No human in the loop.
It can’t. The fact lives outside the database — a rollout got approved, a maintenance window got signed off, a customer opted in. The server has no column to read for that. So you give it one: a state gate, a row someone flips per tenant.

Lead with the first. It’s cheaper, it’s the common case, and it costs you nothing over time. Reach for the second only when the first genuinely can’t answer.

Part A — two shapes, converging on their own

The dbo.OrderSummary table declares two variants of one index, same name, mutually-exclusive gates:

"Indexes": [
  {
    "Name": "IX_OrderSummary_Placed", "IndexColumns": "PlacedAt, CustomerId",
    "ShouldApplyExpression": "{{CompatibilityLevel}} >= 160",
    "VariantName": "Modern (compat 160+)"
  },
  {
    "Name": "IX_OrderSummary_Placed", "IndexColumns": "PlacedAt",
    "ShouldApplyExpression": "{{CompatibilityLevel}} < 160",
    "VariantName": "Legacy (compat < 160)"
  }
]

Look hard at the difference from Module 2. There, the legacy target would have parse-errored on the modern form — the gate was keeping illegal SQL off an engine that couldn’t take it. Here both shapes build fine on both databases. The wider key gives the modern tier’s optimizer a covering index it can exploit; the narrower key is the leaner, better fit for the legacy tier. Neither is wrong. You’re not avoiding a failure — you’re picking the right shape for the metal in front of you.

And because the gate is {{CompatibilityLevel}} and nothing else, it converges for free. Raise a database’s compatibility level next quarter and the very same package quietly switches it to the modern shape. Nobody edits the package. Nobody approves anything. The fleet catches up on its own.

One setup note before the first deploy: the lab’s Step 0 stands up the dbo.RolloutControl table (you’ll meet it properly in Part B). Because both parts ship as one package, every deploy — Part A included — reads that table’s gate, so it has to exist before the first schemaquench or the gate fails closed with Invalid object name 'dbo.RolloutControl'. Run the bootstrap once and all three tiers are ready. (That fail-closed behavior is itself the lesson: a state gate needs its table pre-stood-up — see Part B.)

Deploy to the compat-160 tier and the log prints the receipt:

cd sqlserver
schemaquench --ConfigFile:quench.settings.2022.json --LogPath:"$PWD/logs"

[localhost,11433].[learn_2022]         Creating index [dbo].[OrderSummary].[IX_OrderSummary_Placed] (variant: Modern (compat 160+))
[localhost,11433].[learn_2022] Successfully Quenched

Deploy the identical package to the compat-130 tier and it’s the mirror image — same server, same binary, only the compat level differs:

schemaquench --ConfigFile:quench.settings.2016.json --LogPath:"$PWD/logs"

[localhost,11433].[learn_2016]         Creating index [dbo].[OrderSummary].[IX_OrderSummary_Placed] (variant: Legacy (compat < 160))
[localhost,11433].[learn_2016] Successfully Quenched

That (variant: …) tag is the receipt. The gated-off variant prints nothing at all, so the log proves — straight-faced, no guessing — which shape actually fired on which tier.

The receipt only comes off a component

One honest limit, because it’s the thing people trip on. Only a component — an index, a column, a stat, a check, an FK, a view — carries a VariantName and prints that (variant: …) tag. If your two shapes are two stored procedures, there’s no VariantName to hang on them. A procedure is a script, and you gate a script by the folder it lives in — a Modern folder and a Legacy folder, each with its own ShouldApplyExpression. That works exactly as well; its receipt is just a different line:

Skipping folder 'Programmability/Legacy' — ShouldApplyExpression evaluated false

So: two index shapes prove which one fired with a variant tag. Two procedure shapes prove it with a folder-skip line. Same idea, different receipt — don’t go looking for a variant name on a proc, because it was never there to find.

Part B — the state gate, for facts the server can’t see

Now change one word in the question. “Which compat level is this?” — the server answers that all day. “Has this tenant been approved to take the new index in tonight’s window?” — the server has no idea. That answer lives in a change ticket, a customer email, a line on someone’s rollout spreadsheet. It is not a fact SQL Server can look up.

So you put it somewhere the deploy can look it up. A control table:

CREATE TABLE dbo.RolloutControl (
  feature VARCHAR(64) NOT NULL PRIMARY KEY,
  status  VARCHAR(16) NOT NULL
);

Then you gate the construct on a status it reads at deploy time. The dbo.CustomerActivity index has no version floor — it would build on any tier in the fleet. The only thing holding it back is state:

{
  "Name": "IX_CustomerActivity_LastSeen", "IndexColumns": "LastSeenAt, CustomerId",
  "ShouldApplyExpression": "EXISTS (SELECT 1 FROM dbo.RolloutControl WHERE feature = 'CustomerActivityIndex' AND status = 'Ready')"
}

RolloutControl is operational state the DBA owns, not part of the schema package — you stand it up once, seeded to Pending, before the deploy runs. Deploy while the row says Pending and the EXISTS gate is false, so the index simply doesn’t appear:

schemaquench --ConfigFile:quench.settings.stategate.json --LogPath:"$PWD/logs"

[localhost,11433].[learn_2008]         Creating index [dbo].[OrderSummary].[IX_OrderSummary_Placed] (variant: Legacy (compat < 160))
[localhost,11433].[learn_2008] Successfully Quenched

# IX_CustomerActivity_LastSeen is absent — the EXISTS gate read status='Pending' and skipped it:
1> SELECT name FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.CustomerActivity') AND name IS NOT NULL;
name
--------------------
PK_CustomerActivity

The compat level here is 100 — ancient — and it doesn’t matter one bit. State is the only variable. Now someone signs off on the window. You flip the row and re-run the same package:

UPDATE dbo.RolloutControl SET status = 'Ready' WHERE feature = 'CustomerActivityIndex';

[localhost,11433].[learn_2008]         Creating index [dbo].[CustomerActivity].[IX_CustomerActivity_LastSeen]
[localhost,11433].[learn_2008] Successfully Quenched

# The state-gated index now exists; OrderSummary was untouched (no create line = idempotent):
1> SELECT name FROM sys.indexes WHERE object_id = OBJECT_ID('dbo.CustomerActivity') AND name IS NOT NULL;
name
-----------------------------
PK_CustomerActivity
IX_CustomerActivity_LastSeen

The index lands. And notice what didn’t happen: the OrderSummary index already matched its declared shape, so SchemaSmith left it alone. You didn’t have to write AND NOT EXISTS (SELECT 1 FROM sys.indexes …) to stop a needless rebuild — that guardrail is implicit in the state-based engine. Same package, one row flipped, one construct added, nothing else disturbed.

The anti-pattern — don’t gate a detectable fact on a control table

Here’s where people hurt themselves. The RolloutControl row is the right tool for exactly one reason: tenant approval is a fact the server can’t see. Point that same machinery at something the server can answer — “is this compat 160?”, “does this column exist?”, “what version is the binary?” — and you’ve built a trap:

Two sources of truth. The real server state, and your hand-maintained row. They drift. A tenant gets upgraded; the control row still says the old thing; now your gate lies.
A human back in a loop the engine could close itself. The detectable gate converges for free as servers move. The control-table version needs someone to flip rows forever.

So if you came in wanting two versions of a procedure gated by a control table — you almost certainly don’t need the table. Ask the question. If the difference is detectable, gate on the answer and let it converge (Part A). Reserve the state gate for the fact that genuinely lives outside the database. Manufactured toil, drift, a human in the loop — that’s the bill for gating on a control table what the metal already knows.

An honest word on “faster”

This lab shows the capability, not a benchmark. Two shapes are valid, a gate picks one per tier, a receipt proves which fired — that’s the whole point, and it stands on its own. What it does not do is hand you a stopwatch reading that says the modern key is X percent faster. On our sandbox all three tiers run on the one 2022 binary, so the only place a real “measurably different here” boundary lives is the compatibility level itself — the axis Module 2 proved with GENERATE_SERIES. I’m not going to fake latency numbers to dress this up. When two shapes are both valid, the gate lets the target choose, and the log tells you it did. That’s true whether or not I ran a benchmark.

The state gate travels

One more thing, because it matters for the mixed fleet: the state gate isn’t a SQL Server trick. It’s a table and an EXISTS predicate — nothing engine-specific in it. The same RolloutControl pattern ports to PostgreSQL, MySQL, and MariaDB — a table and an EXISTS predicate, give or take each engine’s identifier conventions (drop the dbo. schema prefix on PostgreSQL and MySQL); only the SQL Server package is wired up in the lab to keep the module tight. Wherever your fleet lives, a fact the server can’t detect gets the same answer: give it a row to read.

What just happened

You sorted your gates by one question — can the target answer this itself? When it can, you gated on the answer and let the fleet converge on its own: two valid index shapes, each printing its variant receipt, the modern one taking over automatically as tiers upgrade. When it can’t, you gave the server a row to read — a RolloutControl state gate for approval the engine has no way to detect. And you learned the trap between them: a control table for a detectable fact is toil that drifts, so you keep the state gate for facts that truly live outside the database.

Detect what you can. Gate what you can’t. Never build a table to hold an answer the metal already knows.

Subscribe and stick around. Next module — the oldest tier. You’ve let the engine adapt, gated what it couldn’t, and gated on state it couldn’t detect. Next we go all the way down to the laggard that’s furthest back — and watch the whole gating scheme retire itself the day it finally catches up.

Until then, may both your shapes ring true, and may you never forge a table to hold an answer the metal already knows.

— Forge

Check yourself: You need two shapes of one index — a wider covering key on your compat-160 tenants, a leaner key on the compat-130 ones — and you want the log to tell you which shape landed on each. Both shapes build fine on both databases. How do you declare it, and what proves which fired?

Declare two variants of the same index name on the table, each with a mutually-exclusive ShouldApplyExpression on {{CompatibilityLevel}} (>= 160 for the wide key, < 160 for the lean one) and a VariantName labeling each. SchemaSmith collapses them to the one that applies before it builds anything, and the applied variant prints its receipt in the log — (variant: Modern (compat 160+)) or (variant: Legacy (compat < 160)); the gated-off variant prints nothing. That variant tag only comes off a component (index, column, stat, check, FK, view). Unlike Module 2, neither shape is illegal on either target — you’re choosing the better shape per tier, not avoiding a parse error, and the gate converges automatically: raise a database’s compat level and the same package switches it to the modern shape with no edit.

Check yourself: A teammate wants to gate a new index on a RolloutControl table with two rows: one that records whether the tenant is at compatibility level 160, and one that records whether the tenant's change window has been approved. Which of those two belongs in a control table, and what goes wrong with the other?

Only the approval belongs in the control table. Change-window approval is a fact SQL Server can’t detect — it lives in a ticket or a sign-off, outside the database — so a RolloutControl row someone flips is exactly the right seam, gated with EXISTS (SELECT 1 FROM dbo.RolloutControl WHERE feature = '…' AND status = 'Ready'). Compatibility level is the opposite: the server can answer it directly, so gate on {{CompatibilityLevel}} >= 160 and let it converge for free. Putting the compat level in a control row creates two sources of truth that drift — upgrade the tenant and the row still says the old value, so the gate lies — and it forces a human to flip rows forever for something the engine would re-answer itself. Gate detectable facts on the answer; reserve the state gate for facts that live outside the database.

Mark this lesson complete