SQL Server Temporal Table Implementation

A system-versioned temporal table in SQL Server maintains a built-in history of every row state change. When system versioning is enabled, the database engine automatically copies the pre-change version of each row to a companion history table before applying the modification to the main table. This eliminates the need for custom triggers or separate audit tables for common point-in-time queries.

Temporal tables matter when you need to answer questions like “what did this row look like yesterday?”, “who changed this record and when?”, or “revert a mistaken update from two days ago?” Common use cases include regulatory audit trails, slowly changing dimension tracking, and debugging data anomalies without relying on application-layer logging.

Creating a System-Versioned Temporal Table

The defining feature is the PERIOD FOR SYSTEM_TIME clause, which binds two datetime2 columns—SYSTEM_START and SYSTEM_END—to the feature. These columns are generated automatically; their values represent the interval during which a given row version was valid.

CREATE TABLE Sales (
    SaleID int NOT NULL PRIMARY KEY,
    ProductID int NOT NULL,
    Quantity int NOT NULL,
    Price money,
    SYSTEM_START datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SYSTEM_END   datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (SYSTEM_START, SYSTEM_END)
)
WITH (SYSTEM_VERSIONING = ON);

When SYSTEM_VERSIONING = ON is set, SQL Server automatically creates the history table for you, deriving its name from the source table's internal object id rather than a predictable <table>_history. Do not look it up by a guessed name — read sys.tables.history_table_id, or pass HISTORY_TABLE yourself if you need a name you control. Its columns match the source table plus the two period columns. The engine populates SYSTEM_START with the transaction start time and sets SYSTEM_END to the maximum datetime2 value (9999-12-31 23:59:59.9999999) for the current row version.

Supplying an existing history table

If a history table already exists—perhaps created under a naming convention or partitioned differently—reference it with the HISTORY_TABLE option:

CREATE TABLE SalesHistory (
    SaleID int NOT NULL,
    ProductID int NOT NULL,
    Quantity int NOT NULL,
    Price money,
    SYSTEM_START datetime2 NOT NULL,
    SYSTEM_END   datetime2 NOT NULL
);

CREATE TABLE Sales (
    SaleID int NOT NULL PRIMARY KEY,
    ProductID int NOT NULL,
    Quantity int NOT NULL,
    Price money,
    SYSTEM_START datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    SYSTEM_END   datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (SYSTEM_START, SYSTEM_END)
)
WITH (SYSTEM_VERSIONING = HISTORY_TABLE (SalesHistory));

The history table must have the same column names, nullability, and data types as the source table. Omitting a column or using a different data type causes the ALTER TABLE ... SYSTEM_VERSIONING = HISTORY_TABLE (...) statement to fail.

Executing the creation script

sqlcmd -S localhost -d MyDB -i create-temporal.sql

The -i flag streams the T-SQL script to the instance. If the script creates both the main and history tables in a single batch, the feature activates immediately after the batch completes.

Querying Current and Historical Data

Point-in-time queries

The FOR SYSTEM_TIME AS OF clause reads the state of the table at a specific moment. This is useful for auditing or reproducing a business decision made at a known time.

-- Current data (the latest version of each row)
SELECT SaleID, ProductID, Quantity, Price
FROM Sales;

-- Data as-of a specific timestamp
SELECT SaleID, ProductID, Quantity, Price
FROM Sales
FOR SYSTEM_TIME AS OF '2024-03-10 14:23:00';

-- All versions of a row that were valid during a window
SELECT SaleID, Price, SYSTEM_START, SYSTEM_END
FROM Sales
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-01-31';

Joining current and historical states

Applications often need to compare a current value against its previous state.

SELECT 
    c.SaleID,
    c.Price AS CurrentPrice,
    h.Price AS PreviousPrice,
    h.SYSTEM_START,
    h.SYSTEM_END
FROM Sales AS c
LEFT JOIN SalesHistory AS h
    ON c.SaleID = h.SaleID
    AND h.SYSTEM_START <= GETUTCDATE()
    AND h.SYSTEM_END > GETUTCDATE()
WHERE c.SaleID = 42;

The join condition anchors the history row to the current transaction time, ensuring only the version that was active “now” is returned.

Managing Retention and Archive Table Growth

The history table accumulates rows as changes occur. Without cleanup, it grows proportionally to the volume of DML operations on the main table, impacting storage costs and potentially slowing DML due to index maintenance.

Manual data movement

-- Archive rows older than three years into a cold-storage table
INSERT INTO ColdStorage SELECT * FROM SalesHistory
WHERE SYSTEM_END < DATEADD(YEAR, -3, GETUTCDATE());

-- Remove archived rows from the history table
DELETE FROM SalesHistory
WHERE SYSTEM_END < DATEADD(YEAR, -3, GETUTCDATE());

This pattern assumes the cold-storage table shares the same schema. If partitioning is used, SWITCH PARTITION operations can move entire partitions between the history table and an archive more efficiently.

Automated retention

SQL Server does not provide a built-in TTL or auto-purge mechanism for temporal tables. Retention must be implemented through:

SQL Agent jobs that execute periodic DELETE or MERGE statements against the history table
Partition switching combined with partition elimination to retire old partitions
External backup and archival workflows that snapshot the history table and truncate it

There is no ALTER TABLE ... RETENTION = ... option. The feature records history indefinitely until manually pruned.

Catalog and Diagnostic Queries

Inspecting temporal table configuration

SELECT 
    t.name AS TableName,
    t.type_desc,
    h.name AS HistoryTableName,
    h.create_date AS HistoryCreateDate,
    t.modify_date AS TableModifyDate
FROM sys.tables t
LEFT JOIN sys.tables h ON h.is_temporal_history = 1
    AND h.name LIKE N'%' + t.name + N'%'
WHERE t.is_temporal = 1;

The is_temporal column in sys.tables is 1 when system versioning is active. The is_temporal_history flag identifies the companion history table.

Checking system versioning state

SELECT 
    name, 
    is_temporal, 
    system_versioning_start_date, 
    system_versioning_end_date
FROM sys.tables 
WHERE is_temporal = 1;

system_versioning_start_date and system_versioning_end_date reflect when the feature was enabled and, if disabled, when it was disabled.

Sharp Edges — What Silently Fails or Surprises

1. Primary key requirement

SYSTEM_VERSIONING = ON requires the main table to have a primary key. Without one, the ALTER TABLE statement fails:

Msg 13553, Level 16, State 1, Line 1
The table cannot be enabled for system versioning because it does not have a primary key.

If the table uses a natural key, add a unique constraint or surrogate key before enabling temporal tracking.

2. History table schema mismatch

The history table must mirror the source table’s column definitions exactly. A mismatch produces:

Msg 13552, Level 16, State 1, Line 1
System versioning requires that the history table be created with the same column definitions as the source table.

This includes nullability—NULL in one and NOT NULL in the other is sufficient to trigger the error. The two period columns (SYSTEM_START, SYSTEM_END) are added by the engine and are not counted toward this matching requirement.

3. DML performance impact

Every UPDATE or DELETE on a temporal table writes to two locations: the modified row in the main table and a new row in the history table. Under high-throughput workloads, this doubles the write amplification. Non-clustered indexes on the main table must be maintained for both the current-row update and the history-row insert, which can increase latency and tempdb usage during bulk operations.

4. The period columns are yours to name — the declaration is what matters

There are no reserved identifiers for the two period columns. You name them, and Microsoft's own examples use names such as ValidFrom/ValidTo or SysStartTime/SysEndTime. The SYSTEM_START and SYSTEM_END used throughout this page are a readable convention, not a requirement — a table that already defines StartTime and EndTime can bind the period to exactly those columns, and nothing is reinterpreted by doing so.

What the engine does require is the declaration: both period columns must be datetime2 and carry GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END.

-- Valid: you choose the names; the GENERATED ALWAYS clauses
-- are what make these period columns
StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime   datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

Naming columns in PERIOD FOR SYSTEM_TIME that lack those declarations is rejected at DDL time, not silently accepted — so this particular mistake fails loudly.

5. Dropping system versioning does not purge history

ALTER TABLE Sales SET (SYSTEM_VERSIONING = OFF);

This stops further history writes but leaves the history table and its data intact. The feature ceases to track changes, but no rows are deleted. To reclaim space, the history table must be dropped or its data purged manually.

6. `SELECT INTO` does not preserve temporal properties

SELECT * INTO NewTable FROM Sales;

The new table is a regular heap or clustered index with no SYSTEM_VERSIONING property. To retain temporal behavior, recreate the table with the WITH (SYSTEM_VERSIONING = ON) clause.

7. Bulk import considerations

bcp and BULK INSERT operations write only to the current row set. History rows are not generated during bulk loads. If the intent is to seed historical data, either disable SYSTEM_VERSIONING temporarily, populate the history table manually after the load, or stage the data through a non-temporal staging table before enabling the feature.

Diagnostic and Recovery Pattern

Verifying temporal integrity after a failed ALTER

-- Check current state
SELECT is_temporal, system_versioning_start_date, system_versioning_end_date
FROM sys.tables WHERE name = N'Sales';

-- If is_temporal is 0, the feature was not activated
-- Re-enable if needed
ALTER TABLE Sales SET (SYSTEM_VERSIONING = ON);

If the ALTER failed midway (e.g., due to a constraint violation), the feature may be partially enabled. Querying sys.tables reveals the actual state and guides whether a re-attempt or manual fix is required.

Point-in-time restore considerations

When restoring a database backup that contains a temporal table, both the main and history tables are restored as of the chosen LSN or timestamp. Point-in-time queries using FOR SYSTEM_TIME AS OF will reflect the restored state at that recovery point. Application logic that depends on exact SYSTEM_START/SYSTEM_END values should account for the restore window, as these metadata columns are restored alongside the data.

Migration from trigger-based auditing

Teams that previously relied on INSERT/UPDATE/DELETE triggers to capture before/after images can transition to temporal tables by:

Dropping the existing audit triggers
Converting the target table to system-versioned with WITH (SYSTEM_VERSIONING = ON)
Validating that the automatically created history table schema matches the former trigger output (column names, types, nullability)
Updating queries that previously joined against the trigger-generated audit table to use FOR SYSTEM_TIME instead

The temporal approach reduces boilerplate, eliminates trigger-maintenance overhead (such as error handling for recursive trigger nesting or transaction log bloat), and provides built-in point-in-time queries without custom T-SQL for each reporting need.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.