System-Versioned (Temporal) Tables in SQL Server

calendar Jun 19, 2026 / · 8 min read · sql server temporal tables system versioning for system_time history table sys.tables sys.databases 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 a queryable copy of the previous row, with no triggers and no hand-built audit table. SQL Server records the period each row version was valid and answers point-in-time questions through a single FOR SYSTEM_TIME clause. The catalog view sys.tables lists which tables are versioned and where each history table lives.

Purpose and Overview

Temporal tables arrived in SQL Server 2016, and the version matters for the catalog queries as well as the feature: temporal_type and history_table_id in sys.tables apply to SQL Server 2016 and later, while the history retention columns (history_retention_period and its unit) apply to SQL Server 2017 and later. A 2016 instance can version rows, but the retention query below needs 2017 or later.

A system-versioned temporal table is a user table designed to keep a full history of data changes, allowing point-in-time analysis. The system manages the period of validity for each row. The table that stores current data is called the current table, and it carries a reference to a history table with a mirrored schema. Each time a row in the current table is updated or deleted, the system stores the previous version of the row in the history table.

Every temporal table has two explicitly defined datetime2 period columns, here ValidFrom and ValidTo, declared GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END and tied together by PERIOD FOR SYSTEM_TIME. Typical use cases are auditing and data forensics, reconstructing the state of data as of any past time, calculating trends over time, maintaining a slowly changing dimension, and recovering from accidental data changes and application errors. Because current and history tables are ordinary tables, the system catalog views describe them, and sys.tables.temporal_type tells you which is which.

Code Breakdown

Create 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));

Query current and historical data with FOR SYSTEM_TIME:

 1-- Current state (same as any non-temporal table)
 2SELECT * FROM dbo.Employee;
 3
 4-- State as it was at a point in the past (the value is read as UTC)
 5SELECT * FROM dbo.Employee
 6    FOR SYSTEM_TIME AS OF '2026-01-01T00:00:00';
 7
 8-- Every version 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;

List every temporal table and its history table:

 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;

List the history retention setting for each temporal table (SQL Server 2017 and later):

 1SELECT
 2    DB.is_temporal_history_retention_enabled,
 3    SCHEMA_NAME(t.schema_id)          AS temporal_schema,
 4    t.name                            AS temporal_table,
 5    t.history_retention_period,
 6    t.history_retention_period_unit_desc
 7FROM sys.tables AS t
 8OUTER APPLY
 9(
10    SELECT is_temporal_history_retention_enabled
11    FROM sys.databases
12    WHERE name = DB_NAME()
13) AS DB
14WHERE t.temporal_type = 2;

The required table shape

A system-versioned temporal table must have a primary key defined and exactly one PERIOD FOR SYSTEM_TIME defined with two datetime2 columns, declared GENERATED ALWAYS AS ROW START or GENERATED ALWAYS AS ROW END. The period columns are always assumed to be non-nullable, and if they are explicitly defined as nullable, the CREATE TABLE statement fails. The history table must be schema-aligned with the current table in the number of columns, column names, ordering and data types.

There are three ways to specify the history table. With no name, the Database Engine creates an anonymous history table in the same schema, named MSSQL_TemporalHistoryFor_<object_id>, with a suffix added only if the name is not unique. With a name, as in the script, the schema name is mandatory and the system creates the table. Or you create the history table beforehand and reference it, which lets you choose the storage and indexes. A created history table is a rowstore table with PAGE compression applied if possible, plus a default clustered index named IX_<history_table_name> on the period columns (end, start).

What each data change writes

On insert, the system sets ValidFrom to the begin time of the current transaction in UTC and ValidTo to the maximum value 9999-12-31, which marks the row as open. On update, the previous version goes to the history table with ValidTo set to the transaction begin time, and the current row gets a new ValidFrom. On delete, the previous version goes to the history table with its period closed, and the row is removed from the current table. A MERGE behaves as if up to three statements (INSERT, UPDATE, DELETE) ran. Because the times are the transaction begin time, all rows inserted in one transaction share the same period start.

The five FOR SYSTEM_TIME subclauses

AS OF returns rows where ValidFrom <= date_time AND ValidTo > date_time, the state at one instant. FROM ... TO returns row versions active at any time in the range and excludes rows that became active exactly on the upper boundary. BETWEEN ... AND is the same except it includes rows that became active on the upper boundary. CONTAINED IN returns versions that were both opened and closed within the range. ALL returns the union of the current and history tables. Internally, AS OF, FROM ... TO and BETWEEN perform a union of the temporal table and its history table, then filter.

FOR SYSTEM_TIME filters out rows with a zero-duration period (ValidFrom = ValidTo). The Database Engine generates those rows when you update the same primary key several times in one transaction. If you need them, query the history table directly.

Locating temporal tables in metadata

In sys.tables, temporal_type is 0 for NON_TEMPORAL_TABLE, 1 for HISTORY_TABLE and 2 for SYSTEM_VERSIONED_TEMPORAL_TABLE. The history_table_id column returns the object_id of the history table when temporal_type is 2, and NULL otherwise, which is why the self-join resolves each current table to its history table. The retention query adds is_temporal_history_retention_enabled from sys.databases for the current database.

Key Benefits and Use Cases

  • Auditing without triggers — every update and delete is recorded in the history table by the engine.
  • Point-in-time reconstruction — AS OF returns the state of the data at any past instant, at the precision of the period columns.
  • Recovery from accidental changes — the overwritten values are still in the history table.
  • Trend analysis — FOR SYSTEM_TIME BETWEEN combined with GROUP BY and aggregates summarizes every row version active in a window.
  • Slowly changing dimensions — built-in versioning replaces custom history logic.
  • Reports across many tables — apply AS OF to a view over several temporal tables and it is applied to each temporal table in the view.

Performance Considerations

  • History grows with every change: the Database Engine adds a row to the history table even if no column values change, so plan for its size and review the retention setting from sys.tables.
  • Index the history table for the workload: for analytic queries with aggregates or windowing functions, a clustered columnstore index is recommended. For auditing the changes to a single row, a rowstore history table with a clustered index is recommended.
  • Times are UTC: period values and AS OF literals are interpreted as UTC, so convert before comparing with local-time application data.
  • History table restrictions: it cannot have a primary key, foreign keys, unique indexes, table constraints or triggers, and it cannot be configured for change data capture, change tracking, transactional replication or merge replication.
  • Retrofit cost depends on edition: adding non-nullable columns with defaults to an existing table with data is a size-of-data operation on all editions other than Enterprise, where it is a metadata operation.

Practical Tips

  • Hide the period columns on existing tables — with HIDDEN, SELECT * and INSERT without a column list keep working for applications that do not know about the new columns.
  • Convert a trigger-based design in place — add the PERIOD FOR SYSTEM_TIME definition, then SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ..., DATA_CONSISTENCY_CHECK = ON)), which runs a data consistency check on the existing data.
  • Choose the start default carefully — when retrofitting, the default for the start column says from which point you consider existing rows valid, and it cannot be a point in the future. The end column must be the maximum value for the datetime2 precision.
  • Query the history table directly for past versions only — when you need non-current row versions alone, reading the history table skips the union with the current table and performs best.
  • Verify after deployment — run the temporal_type query rather than assuming the ALTER took effect.

Conclusion

Define the period columns, turn SYSTEM_VERSIONING on, and the engine keeps the history; FOR SYSTEM_TIME reads it back. The sys.tables queries above give a quick inventory of which tables are versioned, where their history lives and, on SQL Server 2017 and later, how long it is kept.

References

Posts in this series