Gate it yourself

🧪

Hands-on lab

Deploy one SQL Server package to two databases on the same 2022 instance — compat 160 and compat 130 — and watch all three gating levers flip: a Modern/Legacy view folder, an index variant, and a self-skipping sentinel migration. Then prove the footgun: same binary, GENERATE_SERIES still parse-errors at compat 130. Plus the same folder-gate lever on PostgreSQL 12 vs 16.

Demos/Learn/course10-module-02 →

Last module, SchemaSmith read every target and emitted exactly the DDL that target could take. Felt like magic. So you write a view with GENERATE_SERIES, ship it to the old tenant, and expect the same treatment — SchemaSmith will just fix it, right?

Wrong. And it’s the most important “wrong” in this course.

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

Last time was answer number one from the arc — the engine adapts for you. But it adapts its own generated DDL. The moment you hand it a script you wrote, that magic stops cold. SchemaSmith will not reach into your view, your proc, your migration and rewrite it to fit an old target. That’s not a gap — it’s a promise. Your SQL is yours; the tool doesn’t second-guess it.

So when the SQL you wrote differs between two targets, you draw the line. This is answer number two — you gate explicitly — and ShouldApplyExpression is the seam. Let’s kindle the forge.

The boundary, said plainly

Two kinds of SQL flow through a deploy, and they play by different rules:

• DDL SchemaSmith generates from your table JSON — CREATE TABLE, indexes, constraints. This is what Module 1 adapts. Old target, unsupported feature? It degrades and records, or refuses.
• Scripts you author — views, procedures, functions, migrations. SchemaSmith runs these as written. It will not edit them to match a target.

That second rule is the whole reason this module exists. If a view you wrote won’t parse on the old tenant, the tool won’t quietly rewrite it — it’ll run it and let the engine throw. The fix isn’t to hope SchemaSmith patches your SQL. The fix is to tell it, in the package, when your script applies.

Three levers, coarsest to finest

ShouldApplyExpression is one idea with three grains. Same predicate language — a SQL condition the target evaluates at deploy time — bolted on at three different levels:

• Folder — gate a whole folder of scripts. Coarsest.
• Component — gate one index, column, or constraint inside a table.
• Sentinel — gate a single script from inside its own body. Finest.

Pick the grain that matches what actually differs. Let’s take them in order.

Lever one — the folder gate

The thing that differs here is an entire object’s implementation: one view built two ways. So we split it into two folders and gate each one. In Template.json:

"ScriptFolders": [
  {
    "FolderPath": "Programmability/Modern",
    "QuenchSlot": "Objects", "ObjectType": "Views",
    "ShouldApplyExpression": "{{CompatibilityLevel}} >= 160"
  },
  {
    "FolderPath": "Programmability/Legacy",
    "QuenchSlot": "Objects", "ObjectType": "Views",
    "ShouldApplyExpression": "{{CompatibilityLevel}} < 160"
  }
]

Programmability/Modern holds a dbo.vReadingCalendar built with GENERATE_SERIES. Programmability/Legacy holds the same view name, built with a recursive CTE. The two gates are mutually exclusive — exactly one folder applies per database. Deploy to the compat-160 tenant and the modern folder lands; the legacy one is skipped, and the log tells you so:

Skipping folder 'Programmability/Legacy' — ShouldApplyExpression evaluated false

Deploy the identical package to the compat-130 tenant and it’s the mirror image — legacy applies, modern is skipped. Same package, target decides. This is folder gating: the right lever when whole objects diverge.

The footgun — and it’s the reason this course names SQL Server

Look hard at that predicate. It gates on {{CompatibilityLevel}}, not server version. That is not an accident, and getting it wrong is the trap that catches nearly everyone.

On PostgreSQL, MySQL, and MariaDB, “what version is this server?” answers “what syntax can I use?” — one question. On SQL Server it’s two questions, and they can disagree. A modern 2022 binary can host a database left at an old compatibility level, and a pile of newer syntax parse-errors there even though the server is dead current.

So the gate your instinct reaches for is wrong:

-- WRONG for syntax. Returns 16 on a 2022 server no matter what compat level the
-- database wears — so it green-lights syntax that won't parse in a compat-130 DB.
SERVERPROPERTY('ProductMajorVersion') >= 16

Here’s the proof, and it’s brutal. On our sandbox, learn_2022 and learn_2016 are two databases on the same 2022 instance. SERVERPROPERTY('ProductMajorVersion') returns 16 for both. Yet run the modern view’s body against the compat-130 one:

Msg 208, Level 16, State 1
Invalid object name 'GENERATE_SERIES'.

Same binary. Version gate says yes. The syntax still doesn’t parse. A ProductMajorVersion gate can’t tell these two databases apart, and would have shipped a view that dies on deploy.

The rule that saves you:

Gate syntax on compatibility level. Gate features on server version. They’re different questions, and only SQL Server makes you ask both.

Features — data masking, temporal tables, columnstore — track the server binary. That’s what Module 1’s data-mask-on-a-compat-100-database showed: the 2016 feature landed fine because the binary was 2022. Syntax like GENERATE_SERIES tracks the compatibility level. Cross those two up and you’ll swear the tool is broken when it’s doing exactly what you told it.

And watch the trap inside the trap: “new function” does not mean “compat-gated.” Plain STRING_AGG is a 2017 feature that works at compat 100 — it’s binary-gated. But GENERATE_SERIES, and STRING_AGG’s own WITHIN GROUP (ORDER BY) clause, are compat-gated. You can’t guess from the version number alone which bucket a construct falls in — you gate on the axis that actually governs it.

The token idiom

Those raw predicates work, but a package full of SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME() reads like a wall of plumbing. SchemaSmith 2.4.0 gives you two tokens that expand to plain integers before the gate runs:

{{CompatibilityLevel}}   -->  gate SYNTAX   (160, 130, ...)
{{ServerMajorVersion}}   -->  gate FEATURES (16, 13, ...)

So {{CompatibilityLevel}} >= 160 is the idiom. SchemaSmith substitutes the target’s actual compatibility level — a plain integer — before the gate runs, so on a compat-160 database the expression becomes 160 >= 160. Written by hand without the token you’d reach for (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME()) >= 160 — the same result, but the token puts the intent right on the JSON. That’s the form the lab uses for its folder and component gates.

Lever two — the component gate

Sometimes a whole folder is too coarse. What differs is one thing inside a table — an index, a column, a constraint. Those carry ShouldApplyExpression directly, plus a VariantName that labels the intent and shows up in the log when the variant applies.

The lab’s dbo.Reading table declares two variants of one index:

"Indexes": [
  {
    "Name": "IX_Reading_TakenAt", "IndexColumns": "TakenAt, SensorID",
    "ShouldApplyExpression": "{{CompatibilityLevel}} >= 160",
    "VariantName": "Modern (compat 160+)"
  },
  {
    "Name": "IX_Reading_TakenAt", "IndexColumns": "TakenAt",
    "ShouldApplyExpression": "{{CompatibilityLevel}} < 160",
    "VariantName": "Legacy (compat < 160)"
  }
]

Same index name, two shapes, mutually-exclusive gates. SchemaSmith collapses them to the one that applies before it builds anything — and the deployment log prints the receipt:

... IX_Reading_TakenAt ... (variant: Modern (compat 160+))

The gated-off variant prints nothing at all. That (variant: …) tag is how you prove, from the log, which shape actually fired on which target — no guessing.

Lever three — the sentinel skip

The finest grain: a whole script that decides, from inside its own body, not to apply. You raise a specific string and SchemaSmith catches it:

IF (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME()) < 160
    RAISERROR('SCHEMASMITH: SHOULD NOT APPLY', 16, 1);
GO
-- the backfill only a compat-160 tenant should run
INSERT INTO dbo.DeploymentMarker (Marker) VALUES ('modern-tenant-backfill');

On the compat-130 tenant the RAISERROR fires, SchemaSmith recognizes its sentinel string, and records the script as skipped rather than failed:

SeedDeploymentMarker.sql ... Skipped (ShouldNotApply)

On the compat-160 tenant the condition is false, the RAISERROR never fires, and the backfill runs. Any work committed in an earlier batch of the script stays put — the sentinel just stops the script at that line. One caveat worth carrying: a sentinel skip is recorded as completed, so if that tenant is later upgraded and redeployed, the script won’t re-run. It’s a one-shot decision per script, not a standing condition — reach for it when a folder split is too coarse and a component gate doesn’t fit.

The levers are cross-engine — the footgun is not

Be honest about what’s SQL-Server-only here. The footgun is: compatibility level is a SQL Server concept, and no other engine splits “what syntax parses” from “what version is the server.” PostgreSQL, MySQL, and MariaDB answer both with one number. That’s not a SchemaSmith parity gap — it’s a real difference in the engines.

But the three levers are not SQL-Server-only. Here’s the same folder gate on PostgreSQL, splitting a view across the real PG12-to-PG16 jump:

{
  "FolderPath": "Programmability/Modern",
  "QuenchSlot": "AfterTablesObjects", "ObjectType": "Views",
  "ShouldApplyExpression": "{{ServerMajorVersion}} >= 16"
}

The modern view uses any_value(), a PostgreSQL 16 aggregate that doesn’t exist on 12; the legacy one uses min(), which resolves everywhere. Deploy to PG16 and modern applies; deploy to PG12 and legacy does. And notice: off SQL Server, {{CompatibilityLevel}} falls back to {{ServerMajorVersion}}, so the same gate shape is portable — you write the condition once and it means the right thing on every engine.

What just happened

You crossed the boundary. Module 1’s engine adapted the DDL it generated — but the scripts you wrote are yours, and SchemaSmith runs them as written. So when your SQL diverges across a mixed fleet, you gate it yourself: a folder when whole objects differ, a component when one index or column does, a sentinel when a single script has to bow out. One seam, three grains.

And the thing to carry out of here above all else: on SQL Server, gate syntax on compatibility level and features on server version. A current binary hosting an old-compat database will deploy a 2016 feature at full fidelity and still parse-error on GENERATE_SERIES. That’s not the tool breaking. That’s the tool asking the question you told it to ask.

Subscribe and stick around. Next module — two shapes of one query. Here you gated on the engine; next we gate on two shapes that are both right, converging on their own as each server upgrades — and then on something the target can’t detect at all.

Until then, may you gate your syntax by the level and your features by the metal, and never once mistake the one question for the other.

— Forge

Check yourself: You author a view using GENERATE_SERIES and deploy the package to a database sitting at compatibility level 130 on a SQL Server 2022 instance. Your folder gate reads SERVERPROPERTY('ProductMajorVersion') >= 16. What happens, and what should the gate have been?

The gate evaluates true — ProductMajorVersion returns 16 on the 2022 binary regardless of the database’s compatibility level — so SchemaSmith deploys the modern folder. But GENERATE_SERIES is gated by compatibility level, not the binary, so the view fails to parse with “Msg 208 … Invalid object name ‘GENERATE_SERIES’”. The gate should have tested compatibility level: {{CompatibilityLevel}} >= 160 — the token substitutes the database’s actual compatibility level as a plain integer (160 >= 160 on a compat-160 target) before the gate runs; the hand-written equivalent is (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME()) >= 160. The rule: gate syntax on compatibility level, gate features on server version — they’re different questions, and only SQL Server makes you ask both.

Check yourself: You need one script to run only on tenants at compatibility level 160 and be cleanly skipped everywhere else. Which of the three levers fits, and how does SchemaSmith know the difference between a skip and a failure?

That’s the sentinel skip — the finest lever, for gating a single script from inside its own body. The script raises RAISERROR('SCHEMASMITH: SHOULD NOT APPLY', 16, 1) under the condition where it should not apply (here, compatibility level below 160). SchemaSmith recognizes that exact sentinel string and records the script as Skipped (ShouldNotApply) rather than a failure — any work committed in an earlier batch stays put. Note it’s recorded as completed, so it won’t re-run if that tenant is later upgraded; it’s a one-shot decision per script. Reach for it when a folder split is too coarse and a component gate doesn’t fit.

Mark this lesson complete