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.sqlCREATE 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.sqlALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = OFF);
DROP TABLE dbo.Price, dbo.PriceHistory;
- Name the history table. Without
HISTORY_TABLE, SQL Server calls itMSSQL_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:
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.sqlCREATE 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.sqlDROP 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:
ALTER PARTITION SCHEME psOrderDate NEXT USED {OrderFilegroup};
ALTER PARTITION FUNCTION pfOrderDate() SPLIT RANGE ('2027-01-01');
changes/orders-2027/revert.sqlALTER 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.yamlenvironments:
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.