System-Versioned (Temporal) Tables in SQL Server

calendar Jun 19, 2026 / · 6 min read · sql server temporal tables system versioning for system_time history table sys.tables point in time query data audit catalog views database structure metadata scripts  ·
Share on: linkedin copy

Every UPDATE and DELETE on a system-versioned table leaves behind a complete, queryable history of the previous row — automatically, with no triggers and no audit-table plumbing. SQL Server records the period each row version was valid and answers point-in-time questions through a single FOR SYSTEM_TIME clause. This is the temporal-tables feature, available since SQL Server 2016.

Purpose and Overview

A system-versioned temporal table is a user table that keeps a full history of data changes so you can analyze the data as it existed at any point in the past, not just as it is now. The feature is implemented as a pair of tables: the current table holds the present rows, and a linked history table stores prior versions. The Database Engine manages the relationship — on every update or delete, it closes the old row in the history table and stamps a new period of validity.

Each temporal table carries two datetime2 period columns, declared GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END, tied together by a PERIOD FOR SYSTEM_TIME definition. On insert, the start column is set to the transaction's UTC begin time and the end column to the maximum 9999-12-31, marking the row open. On update or delete, the prior version is copied to the history table with the end time set, closing its period. The times are always UTC and based on the transaction start, so all rows changed in one transaction share a timestamp.

Temporal tables solve auditing, point-in-time reconstruction, trend analysis over time, slowly changing dimensions, and recovery from accidental changes — without the custom trigger code those tasks traditionally required. Because the current and history tables are ordinary tables, the same sys catalog views describe them, and sys.tables.temporal_type tells you which is which.

Code Breakdown

Creating a system-versioned table with a named history table:

 1CREATE TABLE dbo.Employee
 2(
 3    EmployeeID   INT          NOT NULL PRIMARY KEY CLUSTERED,
 4    Name         NVARCHAR(100) NOT NULL,
 5    Position     VARCHAR(100) NOT NULL,
 6    Department   VARCHAR(100) NOT NULL,
 7    AnnualSalary DECIMAL(10,2) NOT NULL,
 8    ValidFrom    DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
 9    ValidTo      DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,
10    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
11)
12WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Querying current and historical data with FOR SYSTEM_TIME:

 1-- Current state (queries the current table only)
 2SELECT * FROM dbo.Employee;
 3
 4-- State as it was at a point in the past
 5SELECT * FROM dbo.Employee
 6    FOR SYSTEM_TIME AS OF '2026-01-01T00:00:00';
 7
 8-- All versions of one row, current and historical
 9SELECT EmployeeID, Name, Position, ValidFrom, ValidTo
10FROM dbo.Employee
11    FOR SYSTEM_TIME ALL
12WHERE EmployeeID = 1000
13ORDER BY ValidFrom;

Finding every temporal table and its history table from metadata:

 1SELECT
 2    SCHEMA_NAME(t.schema_id) AS schema_name,
 3    t.name                   AS temporal_table,
 4    t.temporal_type_desc,
 5    h.name                   AS history_table
 6FROM sys.tables AS t
 7LEFT JOIN sys.tables AS h
 8    ON t.history_table_id = h.object_id
 9WHERE t.temporal_type = 2
10ORDER BY schema_name, temporal_table;

The required table shape

A system-versioned table must have a primary key and exactly one PERIOD FOR SYSTEM_TIME defined over two non-nullable datetime2 columns. The GENERATED ALWAYS AS ROW START/ROW END clauses make those columns system-maintained — you do not write to them. SYSTEM_VERSIONING = ON with a HISTORY_TABLE name links a current table to its history; omit the name and SQL Server creates an anonymous history table for you.

The five FOR SYSTEM_TIME subclauses

History is queried through FOR SYSTEM_TIME, which has five forms. AS OF returns the rows valid at a single instant — the workhorse for "what did this look like then." FROM ... TO and BETWEEN ... AND return all versions active within a range (differing only on whether the upper boundary is inclusive). CONTAINED IN returns versions that both opened and closed inside the range. ALL returns the union of current and history rows with no filter. Internally, each form unions the current and history tables and filters on the period columns.

Locating temporal tables in metadata

The catalog query keys on sys.tables.temporal_type: 0 is a non-temporal table, 1 is a history table, and 2 is a system-versioned temporal (current) table. The self-join on history_table_id resolves each current table to its history table by object_id, giving a clean map of the temporal pairs in a database.

Key Benefits and Use Cases

  • Automatic auditing — every change is recorded with no triggers or application code.
  • Point-in-time reconstruction — AS OF rebuilds the exact state of the data at any past instant.
  • Accidental-change recovery — historical rows make it straightforward to restore a value overwritten in error.
  • Trend and forensic analysis — FOR SYSTEM_TIME ALL exposes the full lifecycle of a row for analysis over time.
  • Slowly changing dimensions — built-in versioning replaces hand-rolled SCD logic in decision-support models.

Performance Considerations

  • History grows continuously: every update and delete writes a history row — even an update that changes nothing — so plan for history-table growth and configure a retention policy where supported.
  • Index the history table for your query pattern: a clustered columnstore index suits analytic ALL-style scans, while a rowstore clustered index on the period columns suits single-row AS OF audits.
  • Timestamps are UTC and transaction-based: all rows in one transaction share the same period start, and times are UTC — account for that when comparing to local-time application data.
  • SELECT * includes period columns unless they are declared HIDDEN; hiding them avoids breaking applications that do not expect the extra columns.
  • History tables are constrained: they cannot carry primary keys, foreign keys, unique indexes, triggers, or change data capture, which limits some maintenance patterns.

Practical Tips

  • Declare the period columns HIDDEN when retrofitting versioning onto an existing table so legacy SELECT * and column-less INSERT statements keep working.
  • Use a view over multiple temporal tables and apply AS OF to the whole view — SQL Server pushes the clause to each temporal table automatically, simplifying point-in-time reporting across a normalized model.
  • Enable versioning on an existing table with ALTER TABLE ... ADD PERIOD FOR SYSTEM_TIME followed by SET (SYSTEM_VERSIONING = ON ...) to migrate off trigger-based history.
  • For audit queries that touch only past versions, query the history table directly for best performance instead of going through FOR SYSTEM_TIME.
  • Use the sys.tables.temporal_type query above to confirm versioning is actually on after a deployment, rather than assuming the ALTER succeeded.

Conclusion

System-versioned temporal tables turn change history into a first-class, built-in feature: define the period columns and turn versioning on, and SQL Server maintains a complete history you query with FOR SYSTEM_TIME. They replace fragile trigger-and-audit-table patterns with a supported mechanism, and sys.tables.temporal_type makes the temporal pairs easy to discover. For auditing, point-in-time analysis, and accidental-change recovery, they belong in the modern DBA's toolkit.

References

Posts in this series