Stop expression churn on re-deploy

You deploy a package. You deploy it again, unchanged. Exit 0 both times. You’d bet the second run did nothing.

It didn’t always. A check constraint got dropped and re-added. A computed column got dropped and re-added. A filtered index, the same. Nothing in the log said why, because as far as the tool could tell, something had changed.

On a PERSISTED or STORED column that’s a table rewrite. Every row. Every deploy. And “re-run and nothing changes” is the promise you built your pipeline on.

The engine never hands your text back

Every engine rewrites an expression when it stores it. You write RetentionDays <= 365; SQL Server keeps ([RetentionDays]<=(365)). You write starts_with(plan_code, 'P'); PostgreSQL keeps starts_with((plan_code)::text, 'P'::text). MySQL adds backticks and charset introducers to a generated column.

So compare what you wrote against what the catalog gives back, and they never match. Not after trimming whitespace, not after stripping parens — PostgreSQL added casts, MySQL added a charset introducer. The rewrite is a fixed point, and no normaliser catches every one. A text comparison was always going to lose that fight.

Ask what was applied, not what was written

From SchemaSmith 2.7.0, the tool stops asking the text. It kindles a table into every target, SchemaSmith.ExpressionMap (SchemaSmith_ExpressionMap on MySQL and MariaDB), with one row per expression: what your package authored, what the engine stored after applying it, and the engine version — plus, on SQL Server, the compatibility level — in force at the time. Let’s look at it:

../lab-sql.sh sqlserver cookbook_r15 "SELECT ObjectName, Slot, AuthoredText, CanonicalText, CompatLevel FROM SchemaSmith.ExpressionMap"
# → CK_Subscription_Retention   expression  RetentionDays <= 365   ([RetentionDays]<=(365))   160
# → CreatedAt                   default     SYSUTCDATETIME()       (sysutcdatetime())         160
# → IsActive                    default     1                      ((1))                      160
# → RetentionWeeks              computed    RetentionDays / 7      ([RetentionDays]/(7))      160
# → IX_Subscription_ActivePlan  filter      IsActive = 1           [IsActive]=(1)             160

Two columns that will never read the same, and that’s fine now. On the next deploy SchemaSmith asks one question per object: does the live text still match what I recorded for this declaration? It trusts neither the package text nor the catalog text; it checks both against the record.

What moved	What SchemaSmith does
Nothing	Leaves it alone, however differently the two texts read
Your declaration	Applies it and records the new pair
The live object, edited by hand	Puts your declaration back
The engine version or compatibility level	Re-baselines the record; no object is changed

Empty the map, see the old world

The map is safe to empty. The worst it costs is one more comparison — and it shows you exactly what 2.6.0 did, because no record means no opinion: the comparison falls back to the text.

../lab-sql.sh sqlserver cookbook_r15 "DELETE FROM SchemaSmith.ExpressionMap"
cd sqlserver && schemaquench --ConfigFile:deploy.settings.json --report:./norecord ; cd ..

Dropping index [dbo].[Subscription].IX_Subscription_ActivePlan
Dropping columns from [dbo].[Subscription] ([RetentionWeeks])
Dropping check constraint [dbo].[Subscription].CK_Subscription_Retention
Adding 1 new column(s) to [dbo].[Subscription]
Creating index [dbo].[Subscription].[IX_Subscription_ActivePlan]
Adding check constraint [dbo].[Subscription].CK_Subscription_Retention

Nothing in the package changed. The PERSISTED computed column was dropped and re-added, every row rewritten, and the filtered index and the check were rebuilt for the same reason — their stored text didn’t match what was written. That used to be every deploy. Now it happens at most once — that run re-recorded everything as it went, and the next one is back to quiet.

Going quiet on churn would be dangerous if it also went blind to drift. So the lab tries to fool it.

Change the check by hand, the way someone patches production at 2am, and leave the package alone. The live text no longer matches the record, so the declaration goes back:

Dropping check constraint [dbo].[Subscription].CK_Subscription_Retention
Adding check constraint [dbo].[Subscription].CK_Subscription_Retention

Edit the Expression in dbo.Subscription.json to RetentionDays <= 730 and re-quench, and you get the same two lines. Applied, recorded, and the next deploy is quiet again. An edit you make is always an edit.

Then the one that would’ve hurt most. SQL Server freezes an expression’s stored text at the compatibility level it was created under, so re-applying every expression-bearing object after a compat change would be a far bigger event than the churn it prevents. Drop cookbook_r15 to 150 and deploy:

Re-baselined 5 recorded expression(s): they were recorded under a different SQL Server version or compatibility
level, and their stored canonical text is now refreshed for version 16.0.4260.1 at compatibility level 150. No
object was changed.

It re-reads the live text, updates its record, and tells you so — with a count and the version. An engine upgrade shows up in the first deploy after it, instead of as a mystery.

The report is the evidence

Don’t take a skimmed log as proof. Deploy twice with --report: and read the summary it writes:

schemaquench --ConfigFile:deploy.settings.json                     # exit 0 -- builds the table
schemaquench --ConfigFile:deploy.settings.json --report:./rerun    # exit 0

## Object Changes
- Created: tables=0, columns=0, indexes=0, constraints=0, foreignKeys=0, procedures=0, views=0, functions=0
- Modified: tables=0, columns=0
- Dropped: tables=0, indexes=0, constraints=0, foreignKeys=0

Zeros, on every engine in the lab. That’s the claim, and that’s the receipt.

A note on nullability

None of the lab’s computed or generated columns declares Nullable, and that’s deliberate. On those columns an omitted Nullable leaves nullability to the engine — which makes it nullable — so SchemaSmith never narrows it. Declare "Nullable": false when you genuinely want NOT NULL (SQL Server on a PERSISTED column, PostgreSQL and MySQL on any generated column; MariaDB can’t declare it at all). Ask for NOT NULL where existing rows make the expression NULL and the deploy fails, which is the right answer.

Check yourself: After a maintenance window, your SQL Server's compatibility level moved from 160 to 150. The next deploy of an unchanged package prints a line starting 'Re-baselined 5 recorded expression(s)' and the report shows all zeros. Did anything in the database change, and why didn't SchemaSmith rebuild those objects?

No objects changed — the line itself ends with “No object was changed,” and the zeros in the report confirm it. SQL Server freezes an expression’s stored text at the compatibility level it was created under, and every record was made under the old level — so a different rendering from here on could mean drift, or could just mean the new level. Rather than re-apply every expression-bearing object to find out, SchemaSmith re-reads the live text and refreshes its record in SchemaSmith.ExpressionMap for the new version and level.

Contrast that with a hand edit: there the engine and compat level are unchanged but the live text no longer matches the record, so the declaration is put back. The recorded version and level are what let SchemaSmith tell “the engine moved” apart from “someone moved the object.”

A declarative tool lives or dies on the re-run. If the second quench strikes the same metal the first one did, you stop trusting the zeros, and a deploy log you don’t trust is one you stop reading. So SchemaSmith stopped arguing with the engine about spelling and started remembering what it did: an unchanged declaration is left alone, a changed one is applied, a hand-edited object is put back, and an engine upgrade is recorded rather than replayed.

No text games. No phantom rebuilds. Just a record of the work.

Got a deploy that still rebuilds something on every run? Email me at forgebarrett@schemasmith.com — I’d like to see the object. More’s coming from the forge.

Until then, may your second quench leave the metal exactly as the first one set it.

— Forge

Mark this lesson complete

Stop expression churn on re-deploy

The engine never hands your text back

Ask what was applied, not what was written

Empty the map, see the old world

Quiet isn’t blind

The report is the evidence

A note on nullability