System-Versioned Temporal Tables, History Retention, Temporal Queries, and Audit Use Cases

Learning outcomes

ServiceHub support asks, “What was work order 1001’s status yesterday?” A normal current-state table cannot answer that after rows have been overwritten. The team proposes triggers and a custom audit table, but SQL Server provides system-versioned temporal tables: a current table plus a schema-aligned history table whose row-validity periods are managed by the Database Engine. Temporal history is powerful for time travel and change reconstruction, but it is not an immutable security audit ledger.

1. Temporal versioning is a pair of tables linked by system time

A temporal current table needs a PRIMARY KEY and exactly one PERIOD FOR SYSTEM_TIME defined by two datetime2 columns generated as ROW START and ROW END. With SYSTEM_VERSIONING = ON, SQL Server maintains a history table. Updates copy the previous row image to history; deletes move the previous current row to history. The period timestamps are engine-managed and represent transaction-time validity, not an arbitrary business effective date.

USE ServiceHubLab;GOIF SCHEMA_ID(N'lab04') IS NULL EXEC(N'CREATE SCHEMA lab04 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab04.WorkOrderState', N'U') IS NOT NULLBEGIN    IF EXISTS (SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID(N'lab04.WorkOrderState') AND temporal_type = 2)        ALTER TABLE lab04.WorkOrderState SET (SYSTEM_VERSIONING = OFF);    DROP TABLE lab04.WorkOrderState;END;DROP TABLE IF EXISTS lab04.WorkOrderStateHistory;GOCREATE TABLE lab04.WorkOrderState(    work_order_id bigint NOT NULL CONSTRAINT PK_WorkOrderState PRIMARY KEY,    status varchar(16) NOT NULL,    priority tinyint NOT NULL,    assigned_technician_id int NULL,    valid_from datetime2(7) GENERATED ALWAYS AS ROW START NOT NULL,    valid_to datetime2(7) GENERATED ALWAYS AS ROW END NOT NULL,    PERIOD FOR SYSTEM_TIME (valid_from, valid_to),    CONSTRAINT CK_WorkOrderState_Status CHECK (status IN ('new','assigned','onsite','closed','cancelled')),    CONSTRAINT CK_WorkOrderState_Priority CHECK (priority BETWEEN 1 AND 5))WITH(    SYSTEM_VERSIONING = ON    (        HISTORY_TABLE = lab04.WorkOrderStateHistory,        DATA_CONSISTENCY_CHECK = ON,        HISTORY_RETENTION_PERIOD = 6 MONTHS    ));GO

When SQL Server creates the history table, it keeps the columns schema-aligned but the history table has different constraints/index rules. Microsoft’s default history-table design includes a period-oriented clustered rowstore index. If you supply your own history table, design its indexes for your temporal query and retention workload rather than copying the current table blindly.

2. Updates and deletes automatically create historical row versions

Period columns cannot be directly edited like ordinary business timestamps. SQL Server manages them. To make the behavior visible in a small lab, use separate autocommit statements with short waits so the system-time intervals are distinct enough to query.

USE ServiceHubLab;GODECLARE @t0 datetime2(7), @t1 datetime2(7), @t2 datetime2(7);INSERT lab04.WorkOrderState(work_order_id, status, priority, assigned_technician_id)VALUES (1001,'new',3,NULL);SET @t0 = SYSUTCDATETIME();WAITFOR DELAY '00:00:01';UPDATE lab04.WorkOrderStateSET status = 'assigned', assigned_technician_id = 1WHERE work_order_id = 1001;SET @t1 = SYSUTCDATETIME();WAITFOR DELAY '00:00:01';UPDATE lab04.WorkOrderStateSET status = 'onsite', priority = 4WHERE work_order_id = 1001;SET @t2 = SYSUTCDATETIME();SELECT @t0 AS after_insert,       @t1 AS after_assigned,       @t2 AS after_onsite;SELECT work_order_id, status, priority, assigned_technician_id, valid_from, valid_toFROM lab04.WorkOrderState FOR SYSTEM_TIME ALLWHERE work_order_id = 1001ORDER BY valid_from;GO

The ALL query combines current and history versions. The exact timestamps come from your server, so do not hard-code expected period values. What matters is the ordered sequence of versions and non-overlapping validity intervals.

3. Temporal query syntax reconstructs state without querying history manually

FOR SYSTEM_TIME AS OF @time asks SQL Server for the version valid at one system-time point. BETWEEN, FROM ... TO, CONTAINED IN, and ALL express different interval semantics. Use the documented boundary rules carefully rather than assuming every operator is inclusive on both ends.

USE ServiceHubLab;GODECLARE @after_first_insert datetime2(7);-- Derive a safe point inside the earliest recorded version.SELECT TOP (1)       @after_first_insert = DATEADD(millisecond, 1, valid_from)FROM lab04.WorkOrderState FOR SYSTEM_TIME ALLWHERE work_order_id = 1001ORDER BY valid_from;SELECT work_order_id, status, priority, assigned_technician_idFROM lab04.WorkOrderStateFOR SYSTEM_TIME AS OF @after_first_insertWHERE work_order_id = 1001;GO

The query is routed across current/history data according to temporal semantics. A normal SELECT FROM lab04.WorkOrderState reads current rows only. Directly querying the history table is sometimes useful for administration, but temporal query syntax preserves the intended time semantics and can span temporal joins/views in later designs.

4. Retention is lifecycle policy, not a guarantee that cleanup happens immediately

History can grow much faster than the current table in update-heavy workloads. SQL Server supports finite HISTORY_RETENTION_PERIOD values and database-level TEMPORAL_HISTORY_RETENTION. A background task identifies/removes eligible history rows; cleanup is asynchronous. A point-in-time restore can turn the database retention flag off, so post-restore runbooks should verify it rather than assuming it stayed enabled.

USE ServiceHubLab;GOSELECT DB_NAME() AS database_name,       d.is_temporal_history_retention_enabledFROM sys.databases AS dWHERE d.database_id = DB_ID();SELECT    s.name AS current_schema,    t.name AS current_table,    hs.name AS history_schema,    h.name AS history_table,    t.temporal_type_desc,    t.history_retention_period,    t.history_retention_period_unit_descFROM sys.tables AS tJOIN sys.schemas AS s ON s.schema_id = t.schema_idLEFT JOIN sys.tables AS h ON h.object_id = t.history_table_idLEFT JOIN sys.schemas AS hs ON hs.schema_id = h.schema_idWHERE t.object_id = OBJECT_ID(N'lab04.WorkOrderState');GO

A retention period describes eligibility policy, not an exact deletion timestamp and not a substitute for capacity planning. If regulations require longer preservation, archive/export or partitioning strategies may be needed before cleanup.

5. Deliberately wrong approach: treat temporal history as immutable audit evidence

An administrator tells compliance that “SQL Server temporal means nobody can alter history.” A privileged user can run SYSTEM_VERSIONING = OFF, at which point current and history become ordinary independent tables; history capture stops and sufficiently privileged users can modify/delete historical rows. Retention can also remove old versions by design.

6. Schema evolution and SYSTEM_VERSIONING OFF need explicit history-table handling

Some maintenance operations require temporarily stopping system versioning. Microsoft recommends doing coordinated OFF/maintenance/ON changes inside a transaction where appropriate. When re-enabling versioning, specify the original HISTORY_TABLE; omitting it can associate a newly created history table and leave the old one detached.

USE ServiceHubLab;GOBEGIN TRANSACTION;    ALTER TABLE lab04.WorkOrderState    SET (SYSTEM_VERSIONING = OFF);    -- Example maintenance would occur here.    -- Do not modify history casually; preserve your retention/audit contract.    ALTER TABLE lab04.WorkOrderState    SET    (        SYSTEM_VERSIONING = ON        (            HISTORY_TABLE = lab04.WorkOrderStateHistory,            DATA_CONSISTENCY_CHECK = ON,            HISTORY_RETENTION_PERIOD = 6 MONTHS        )    );COMMIT;GO

Turning versioning off does not itself delete the period or history table, but capture stops. Track the maintenance window, validate consistency, and verify the history-table ID after re-enabling.

7. Hands-on lab: temporal evidence card and cleanup

USE ServiceHubLab;GOSELECT    SERVERPROPERTY('ProductVersion') AS product_version,    DATABASEPROPERTYEX(DB_NAME(), 'Updateability') AS database_updateability,    t.temporal_type_desc,    OBJECT_SCHEMA_NAME(t.history_table_id) AS history_schema,    OBJECT_NAME(t.history_table_id) AS history_table,    t.history_retention_period,    t.history_retention_period_unit_descFROM sys.tables AS tWHERE t.object_id = OBJECT_ID(N'lab04.WorkOrderState');SELECT work_order_id, status, priority, valid_from, valid_toFROM lab04.WorkOrderState FOR SYSTEM_TIME ALLORDER BY work_order_id, valid_from;GO

Verification checklist

• You can identify current and history tables through catalog metadata.
• You observed multiple versions after UPDATE without writing a trigger.
• You used temporal query syntax rather than treating the history table as a normal current-state table.
• You inspected both table-level retention and database-level retention enablement.
• You can explain why privileged OFF/maintenance operations mean temporal history is not tamper-proof audit evidence.

USE ServiceHubLab;GOIF EXISTS(    SELECT 1    FROM sys.tables    WHERE object_id = OBJECT_ID(N'lab04.WorkOrderState')      AND temporal_type = 2)    ALTER TABLE lab04.WorkOrderState SET (SYSTEM_VERSIONING = OFF);GODROP TABLE IF EXISTS lab04.WorkOrderState;DROP TABLE IF EXISTS lab04.WorkOrderStateHistory;GOIF NOT EXISTS(    SELECT 1 FROM sys.objects WHERE schema_id = SCHEMA_ID(N'lab04'))    EXEC(N'DROP SCHEMA lab04;');GO

8. Production judgment: temporal is a data-history feature with an operational lifecycle

Temporal tables are appropriate when applications need convenient system-time reconstruction, historical analytics, slowly changing state, or recovery from accidental logical changes. They add storage, write activity, history indexing, retention, schema-evolution, backup/restore, permissions, and monitoring obligations. Choose retention from business/legal requirements and measured storage behavior; do not assume INFINITE history is free or finite retention deletes on an exact schedule.

Chapter 04 closes the schema-design layer: namespaces govern where objects live and who can use them; constraints make invariants executable; identifiers separate technical identity from business numbering; computed columns centralize derived expressions; temporal tables preserve row history. Chapter 05 can now focus on querying these structures correctly with joins, subqueries, APPLY, common table expressions, and set operations.

Authoritative references

• Temporal tables — current/history mechanics and system-time concepts
• Create a system-versioned temporal table — PERIOD, primary-key and history-table requirements
• Manage historical data retention — finite retention and asynchronous cleanup
• Stop system-versioning — safe OFF/ON maintenance and history-table reassociation risk
• Temporal table considerations and limitations — indexing and feature limitations
• Temporal table security — permission implications for current/history access
• SQL Server 2025 build versions — current servicing baseline