History you can't rewrite

The auditors don’t want to know what the balance used to be. They want to know nobody changed it. Not the app, not a support engineer with a quick fix, not the DBA with sysadmin and a deadline.

A history table can’t answer that, because whoever can write the table can write its history. A ledger table can. SQL Server 2022 chains every change to it into cryptographic blocks, and verifying the database recomputes every block against a digest you stored somewhere else. Change a row behind the ledger’s back and verification names the table.

There’s a catch, and it’s the reason this module exists. A ledger table is close to permanent. No ALTER turns a table into one or out of one, and DROP doesn’t remove it. Get the declaration wrong and you live with it. So this is where you learn exactly what a deploy will and won’t do to one.

Module 8 kept old versions so you could ask what a row said. This one keeps them so you can prove what it said.

Two kinds

"Ledger": "Updatable",
"LedgerView": { "Name": "[AccountHistory]" },
"Columns": [
  { "Name": "[TxStart]", "DataType": "BIGINT", "Nullable": false, "GeneratedAlways": "TransactionIdStart", "Hidden": true }
]

Updatable rows change, and every old version is kept. The ledger view shows each update as a pair in one transaction, the new row in and the old row out:

1  100.00  INSERT
1   75.00  INSERT
1  100.00  DELETE

Append-only rows go in and never change. "Ledger": "AppendOnly" is the whole declaration, and the engine enforces it:

Updates are not allowed for the append only Ledger table 'dbo.AuditEntry'.

Every ledger table has generated columns: four on an updatable table, two on an append-only one. Declare a column by its role in GeneratedAlways and it takes your name. Leave it out and it takes the engine’s (ledger_start_transaction_id and so on), hidden. LedgerView names the view the same way. Below SQL Server 2022 the table deploys as an ordinary one, and the run records a downgrade. That’s deliberate: an ordinary table created by mistake is easy to fix, a ledger table isn’t.

What changes in place, and what doesn’t

You change	The deploy
A ledger column’s name (`OldName`)	`Renaming ledger column dbo.Account.TxStart to TxBegin`
The view’s name	`Renaming ledger view dbo.AccountHistory to AccountLedger`
A column’s `Hidden`	`ALTER COLUMN … DROP HIDDEN`, in place
The view’s schema, or its column names	Leaves them, with a warning per item: SQL Server can’t do either
`Ledger` itself	Refuses, by name, before it touches anything
A change under a `RebuildPolicy`	Refuses the rebuild; without the policy, the same column change runs in place

The two refusals are the ones to understand. Changing the kind would mean a new table, and the old one can’t be dropped, only retired under a generated name. The refusal says so:

Table [dbo].[Account] declares Ledger AppendOnly, but is currently deployed as Updatable. SQL Server has no ALTER that converts a table to or from a ledger table, and DROP does not remove one (it is retained as MSSQL_DroppedLedgerTable_<name>_<guid>), so SchemaSmith will not attempt it -- correct the declared Ledger to match, or migrate the data to a new table.

A rebuild copies the table into a shadow and swaps it in. The shadow is an ordinary table, so the swap would quietly turn your ledger table into an ordinary one and retire the original. That’s refused too, and an ALTER that can run in place still does.

Data delivery is insert-only

An append-only table refuses UPDATE and DELETE, so a data delivery to one can only insert. In the lab the content file changes row 1 and adds row 2, with MergeType set to Insert/Update/Delete. Row 2 goes in, and row 1 stays exactly as it was. On an append-only table the history wins, even over your package.

Nothing really leaves

Drop a ledger table with DropTablesRemovedFromProduct and it’s still there afterwards, as MSSQL_DroppedLedgerTable_AuditEntry_…, with its rows and its place in the chain. Later deploys leave it alone. Put the table back in the package and you get a new, empty one beside it.

Drop a column and it stays too, hidden, as MSSQL_DroppedLedgerColumn_Owner_…, so the ledger still verifies. Extraction leaves it out of the package, so the next database you deploy to doesn’t get the engine’s leftovers. The deploy leaves it out of the comparison the same way: SQL Server won’t drop a retained column, so the next deploy doesn’t try, and exits 0.

Check yourself: A teammate created an Updatable ledger table and now wants it AppendOnly, since rows should never change. They edit the package and deploy, and the deploy refuses with exit 2. Why won't SchemaSmith just drop and re-create the table, and what are the real options?

Dropping it wouldn’t get rid of it. SQL Server keeps a dropped ledger table under a generated name, rows and all, and no ALTER converts one kind of ledger table into the other. A drop-and-recreate would leave the old table retired beside a new one, and the “change” would really be a migration that nobody planned.

The options are the ones the refusal names. Put the declaration back to Updatable, which leaves things as they are, or migrate on purpose: create a new append-only table with its own name, move the rows across in a migration script, point the application at it, and accept that the old table stays in the ledger for good.

Most metal you can reheat and work again. Ledger tables are the other kind: once the quench sets, the shape is permanent, and every strike you ever made is in the record. That’s the whole point of them, and it’s why the deploy treats them so carefully. It changes what can be changed in place, says so plainly where SQL Server won’t, and refuses the moves that would quietly break the chain.

So declare a ledger table the way you’d sign something. Read it twice first.

Running ledger tables and hit something a deploy didn’t handle? Email me at forgebarrett@schemasmith.com.

Next up: Module 10 · Indexes beyond the B-tree, where the spatial, XML and columnstore indexes in your post-restore script become declarations.

Until then, may your digests always verify, and your ledgers outlast every audit.

— Forge

Mark this lesson complete