Lone Badger Tech

Temporal tables and partitions

System-versioned tables and partitioned tables in changes, reverts and fingerprints.

Both are ordinary schema, so they belong in changes: deploy creates or alters them, revert undoes it, and both run in the change's transaction. Badger SQL Migrate adds three things: a fingerprint that understands them, a lint rule, and a setting for partitions that a job maintains.

System-versioned (temporal) tables

changes/create-price/deploy.sql
CREATE TABLE dbo.Price (
    Id int NOT NULL CONSTRAINT PK_Price PRIMARY KEY,
    Amount money NOT NULL,
    ValidFrom datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo))
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PriceHistory, HISTORY_RETENTION_PERIOD = 2 YEARS));
changes/create-price/revert.sql
ALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.Price, dbo.PriceHistory;
  • Name the history table. Without HISTORY_TABLE, SQL Server calls it MSSQL_TemporalHistoryFor_<object id>, a different name on every server, and a revert script has to look the name up before switching versioning off.
  • Changing a temporal table. Most schema changes go to the table and its history table together. Adding a nullable column or a column with a default works while versioning is on. For anything else, switch it off, change both tables, and switch it on again in the same script, so the change stays atomic:
changes/price-amount-decimal/deploy.sql
ALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Price ALTER COLUMN Amount decimal(19, 4) NOT NULL;
ALTER TABLE dbo.PriceHistory ALTER COLUMN Amount decimal(19, 4) NOT NULL;
ALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PriceHistory, DATA_CONSISTENCY_CHECK = ON));

Lint rule BSM004 warns when a script switches versioning off for a table and neither switches it back on nor drops the table. The fingerprint records each table's period columns, versioning state, history table and retention, and the GENERATED ALWAYS and HIDDEN flags of period columns. A history table SQL Server named itself is recorded under the name of its table, so databases built from the same plan fingerprint the same. Reverting a change also reverts the schema; the rows in the history table go with it, so take a copy first if you need them.

Partitioned tables

changes/create-order/deploy.sql
CREATE PARTITION FUNCTION pfOrderDate (date) AS RANGE RIGHT FOR VALUES ('2026-01-01', '2026-04-01', '2026-07-01', '2026-10-01');
CREATE PARTITION SCHEME psOrderDate AS PARTITION pfOrderDate ALL TO ({OrderFilegroup});
CREATE TABLE dbo.[Order] (
    OrderDate date NOT NULL,
    Id bigint NOT NULL,
    CONSTRAINT PK_Order PRIMARY KEY (OrderDate, Id)) ON psOrderDate (OrderDate);
changes/create-order/revert.sql
DROP TABLE dbo.[Order];
DROP PARTITION SCHEME psOrderDate;
DROP PARTITION FUNCTION pfOrderDate;

Filegroups often differ per environment (Azure SQL Database only has PRIMARY), so keep them in a variable, for example OrderFilegroup: "[PRIMARY]" in Azure and "[FG_Orders]" on-premises. Adding a boundary in a change is a normal deploy, and its revert merges it back:

changes/orders-2027/deploy.sql
ALTER PARTITION SCHEME psOrderDate NEXT USED {OrderFilegroup};
ALTER PARTITION FUNCTION pfOrderDate() SPLIT RANGE ('2027-01-01');
changes/orders-2027/revert.sql
ALTER PARTITION FUNCTION pfOrderDate() MERGE RANGE ('2027-01-01');

Splitting or merging a partition that holds rows moves data and locks the table; split ahead of the data, while the partition is still empty. The fingerprint records each function's type, direction and boundary values, each scheme's filegroups, and the scheme and partitioning column of every partitioned table and index.

Partitions maintained by a job

With a sliding window, a scheduled job splits new partitions and switches out old ones outside bsm. Every split would show as schema drift. Tell the environment to leave boundaries out of the fingerprint:

bsm.yaml
environments:
  prod:
    schemaFingerprint:
      partitionBoundaries: ignore   # include (default) | ignore

The fingerprint then records each function's type and direction, and the set of filegroups each scheme uses, so moving a table to another scheme or function is still drift; adding a boundary is not. The setting applies to snapshots taken from then on: deploy once after changing it.

Upgrading from 0.1

Version 0.2 records these details for the first time, so a database with temporal or partitioned tables deployed by 0.1 has a fingerprint in the old format. bsm verify warns instead of reporting drift until the next deploy records a new fingerprint. Databases without them fingerprint exactly as before.