Temporal / History Tables

Status: Reviewed (deep-dive v1)
Stack note: System-versioned temporal tables give point-in-time reads for config, allowlists, and exception rows without a home-grown audit table — Blazor still goes through usp_* (SQL-12); history is queried inside those procs.
Pairs with: SQL-05 / SPIKE-06 (security exceptions), SQL-10 / SPIKE-07 (alert rules), SQL-15 (don’t break versioning mid-tran).
Goal: When to use temporal, how to create/query/retain, and estate fit (config & exceptions — not every fact table).

Quick chooser

Need	Prefer	Avoid
“What did this config look like last Tuesday?”	System-versioned temporal	Manual `ValidFrom`/`ValidTo` you forget to maintain
Who changed it / why	Temporal plus app columns (`ModifiedBy`, reason) or change log	Temporal alone for “who” (it tracks when, not identity)
High-churn metrics (perf samples, backup heat)	Regular tables + retention job	Temporal on hot insert streams
Soft-delete with full row history	Temporal (delete still versions)	Orphan history hacks
Cross-DB / linked history	Plan carefully — temporal is per-table	Assuming history follows restores magically

What you get

Current table + history table (auto-managed)
Period columns: ValidFrom / ValidTo (datetime2) — hidden or exposed
Queries: FOR SYSTEM_TIME AS OF / BETWEEN / FROM…TO / ALL
Updates/deletes on current → prior row lands in history

Not a substitute for: CDC/CT for sync, or Append-only event streams (AlertEvent).

Create pattern (config example)

CREATE TABLE dbo.AlertRule (
  AlertType varchar(64) NOT NULL CONSTRAINT PK_AlertRule PRIMARY KEY,
  SeverityCode varchar(16) NOT NULL,
  IsEnabled bit NOT NULL,
  RunbookUrl nvarchar(512) NULL,
  Description nvarchar(256) NOT NULL,
  ModifiedBy nvarchar(128) NULL,  -- app-supplied
  ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START NOT NULL,
  ValidTo   datetime2(0) GENERATED ALWAYS AS ROW END   NOT NULL,
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.AlertRuleHistory));

Convert existing:

ALTER TABLE dbo.SecurityException ADD
  ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
    CONSTRAINT DF_SE_ValidFrom DEFAULT SYSUTCDATETIME(),
  ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
    CONSTRAINT DF_SE_ValidTo DEFAULT CONVERT(datetime2(0), '9999-12-31'),
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE dbo.SecurityException
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.SecurityExceptionHistory));

Query from procs (Blazor-facing)

-- point-in-time board snapshot
CREATE OR ALTER PROC dbo.usp_AlertRule_GetAsOf
  @AsOf datetime2(0)
AS
BEGIN
  SET NOCOUNT ON;
  SELECT AlertType, SeverityCode, IsEnabled, RunbookUrl, Description, ModifiedBy, ValidFrom, ValidTo
  FROM dbo.AlertRule FOR SYSTEM_TIME AS OF @AsOf;
END;

-- full lineage for one key
CREATE OR ALTER PROC dbo.usp_AlertRule_GetHistory
  @AlertType varchar(64)
AS
BEGIN
  SET NOCOUNT ON;
  SELECT *
  FROM dbo.AlertRule FOR SYSTEM_TIME ALL
  WHERE AlertType = @AlertType
  ORDER BY ValidFrom;
END;

UI ideas: “As of” date picker on config/exceptions pages; history drawer — still read procs only; no direct table access from Blazor.

Estate fit

Candidate	Why temporal helps
`AlertRule`	Rule toggles / severity changes over time
Security exception allowlist	Who was excepted when (pair with `ModifiedBy`)
Patch target matrix / build targets	“What was the target build last month?” (SPIKE-08)
App settings / collector thresholds	Reconstruct after bad deploy
Not: `Alert` open/close stream	Use `AlertEvent` (already append-only)
Not: Per-minute perf samples	Too chatty; retention cost

Retention & ops

ALTER TABLE dbo.AlertRule SET
(
  SYSTEM_VERSIONING = ON
  (
    HISTORY_TABLE = dbo.AlertRuleHistory,
    HISTORY_RETENTION_PERIOD = 2 YEARS  -- version-dependent; confirm edition
  )
);

Checklist:

[ ] History on same filegroup or dedicated — watch growth (SQL-06)
[ ] Index history for FOR SYSTEM_TIME filters you actually use (often key + ValidFrom)
[ ] Schema changes: some ALTER require versioning OFF briefly — schedule maintenance window
[ ] Backup/restore: current + history restored together; test AS OF after restore drill (SQL-02)
[ ] Permissions: grant on current; history access usually via temporal queries if rights allow
[ ] Stretch/archival: prefer retention period over manual deletes on history

App / PowerShell habits

Collectors: update current config tables normally — engine versions rows
Always set ModifiedBy (or similar) from authenticated user / PS job name
Don’t dual-write a parallel audit table unless you need payload temporal can’t store
Merges (SQL-19 shortlist): one UPDATE path; avoid delete+insert that balloons history unnecessarily

Common failure patterns

Symptom	Likely cause
History empty after UPDATE	Versioning off / wrong table
Huge history	Temporal on high-churn fact table
Can’t ALTER column	Need turn versioning off → alter → on
`AS OF` wrong day	Local vs UTC; store/query UTC
“Who changed it?” unknown	No `ModifiedBy`; temporal ≠ login capture

Tiny lab

Enable temporal on a small config table; UPDATE twice; FOR SYSTEM_TIME ALL
Proc AS OF yesterday — match expected row
Measure history size after simulated exception churn

Tie-ins

SPIKE-06 / SQL-05: Exception roster history
SPIKE-07: Rule changes over time
SQL-15: Schema flips with versioning off need careful tran/windows

Done when

[ ] 1–2 config/exception tables chosen for temporal (not the whole warehouse)
[ ] GetAsOf / GetHistory procs for Blazor
[ ] Retention period set and growth watched
[ ] This note linked from Knowledge Base list

SQL Dude — SQL-22 Temporal / History Tables v1