History the database keeps

Somebody asks what a price said last March. You’ve got an audit trigger somewhere, maybe, written by someone who left, writing to a table nobody’s checked in a year. Or you’ve got nothing, and the honest answer is “whatever it says now.”

Two engines can answer that question on their own. SQL Server keeps every old version of a row in a temporal table’s history. MariaDB does the same with system versioning, inside the table. Neither needs a trigger. And in both, how the history is kept is state you can declare: which table holds it, how long it lives, what the period columns are called and whether you see them.

MariaDB adds a second kind of time, and it answers a different question. System time records when a row was stored. Application time records when it was true: the dates a rate applied, whatever day someone typed it in. One table can carry both.

The SQL Server shape

A temporal table is three settings and two columns:

"IsTemporal": true,
"HistoryTableName": "[PriceHistory]",
"HistoryRetentionPeriod": "6 MONTHS",
"Columns": [
  { "Name": "[PriceId]", "DataType": "INT", "Nullable": false },
  { "Name": "[Amount]", "DataType": "DECIMAL(10,2)", "Nullable": false },
  { "Name": "[SysStart]", "DataType": "DATETIME2(2)", "Nullable": false, "GeneratedAlways": "RowStart", "Hidden": true },
  { "Name": "[SysEnd]",   "DataType": "DATETIME2(2)", "Nullable": false, "GeneratedAlways": "RowEnd",   "Hidden": true }
]

GeneratedAlways names each column’s role, so you choose the names and the precision. Hidden keeps them out of SELECT * and out of an INSERT with no column list, so an application written before the table was versioned doesn’t notice. Leave the pair out and you get ValidFrom / ValidTo DATETIME2(7).

One deploy builds the lot:

        Adding new table [dbo].[Price]
        Adding period columns to [dbo].[Price]
        Creating constraint [dbo].[Price].[PK_Price]
        Creating index [dbo].[Price].[IX_Price_SysStart]
        Turn ON Temporal Tracking for [dbo].[Price]

The period columns land before the indexes, so an index can name one. You don’t declare the history table: SQL Server creates PriceHistory the moment versioning comes on. Update a row and the old version is there:

SELECT PriceId, Amount FROM dbo.Price FOR SYSTEM_TIME ALL ORDER BY SysStart;
-- 1  10.00
-- 1  12.00

Changing a table that’s keeping history

A versioned table is a live recorder, and some changes can’t happen while it’s running. Here’s what a deploy does with each kind:

Change	What happens
Rename a period column (`OldName`)	`Rename period column [dbo].[Price].[SysStart] to [SysBegin]`. In place, and the history table’s column follows it.
Change `Hidden`	`ALTER COLUMN … ADD HIDDEN` or `DROP HIDDEN`, in place. The summary lists the column.
Change the precision	Left alone, with a warning that names the column and both types

That last one deserves a second look:

        WARNING: [dbo].[Price]: period column SysEnd is DATETIME2(2), declared DATETIME2(7). SQL Server cannot change the precision of a period column, so the deploy leaves it as it is; re-create the table to change it.

SQL Server won’t alter a period column while the table is versioned. A tool could switch versioning off, make the change and switch it back on, and every update in that window would write no history. That’s a decision about your audit trail, and it isn’t the deploy’s to make. So it doesn’t. It tells you, by name, and leaves the re-create to you.

The MariaDB shape

System versioning keeps the history inside the table. Declare the row-start and row-end columns by role, make them invisible, and opt a busy column out:

"IsSystemVersioned": true,
"Columns": [
  { "Name": "`Note`", "DataType": "VARCHAR(200)", "Nullable": true, "WithoutSystemVersioning": true },
  { "Name": "`rs`", "DataType": "TIMESTAMP(6)", "Nullable": false, "GeneratedAlways": "RowStart", "Invisible": true },
  { "Name": "`re`", "DataType": "TIMESTAMP(6)", "Nullable": false, "GeneratedAlways": "RowEnd", "Invisible": true }
]

WithoutSystemVersioning means an update that touches only that column writes no history row. In the lab, three writes produce two versions: the Amount change is kept, the Note change isn’t. Use it on a column that changes constantly and that nobody will ask about later.

MariaDB also adds the row-end column to every key on a versioned table, so PRIMARY KEY (PriceId) becomes (PriceId, re) in the catalog. Old versions have to share a key with the current row somehow. You declare the key without re, extraction writes it without re, and a redeploy leaves it alone.

When it was true

Application time is a period over two columns your application sets:

"Periods": [ { "Name": "Validity", "StartColumn": "ValidFrom", "EndColumn": "ValidTo" } ],
"Indexes": [
  { "Name": "ux_rate_sku_validity", "Unique": true, "IndexColumns": "`Sku`, Validity", "WithoutOverlaps": true }
]

WithoutOverlaps turns the unique key into a rule about time: two rates for the same SKU may meet, but they may not overlap. The engine enforces it. A rate from June to August, landing on top of one that ends in July, is refused:

ERROR 1062 (23000) at line 1: Duplicate entry 'anvil-2026-08-01-2026-06-01' for key 'ux_rate_sku_validity'

That’s a business rule you’d otherwise be writing as a trigger, and getting wrong at the boundaries.

One caveat for your own fleet: MariaDB periods work from 10.4.3, but the catalog only reports them from 11.4. Extract from anything in between and the periods come back empty. Deploying works on every version. Only the read is blind, so if you extract from an older MariaDB, check your periods made it.

Check yourself: Your team wants to change a temporal table's SysEnd column from DATETIME2(2) to DATETIME2(7) for finer timestamps. You make the edit, deploy, and the run exits 0 — but the column is still DATETIME2(2). Is that a bug, and what should you do?

It isn’t a bug. The deploy printed a warning naming SysEnd, its live type and the declared one. SQL Server refuses to change a period column’s precision while the table is versioned. Switching versioning off to make the change would leave a gap in the history, and every update in that window would go unrecorded, so SchemaSmith doesn’t do that for you.

The fix is a deliberate re-create: a new table with the new precision, the data and history moved across in a window you chose, and the package then describing the new table. The exit code is 0 because nothing failed; the warning is what tells you the declaration and the database disagree.

History used to be something you bolted on: a trigger, an audit table, a job that pruned it, all scripts nobody owned. Here it’s part of the casting. The package says the table remembers, how long it remembers, and what it calls the clock. The engine does the remembering.

Know which clock you’re asking. System time is when the metal went into the mold. Application time is when it was meant to be used. MariaDB lets you stamp both on the same piece.

Got a versioned table that a deploy didn’t treat the way you expected? Email me at forgebarrett@schemasmith.com. More’s coming from the forge.

Until then, may your history keep every strike, and your rates never overlap.

— Forge

Mark this lesson complete