Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

A system-versioned temporal table (SQL Server 2016 onwards) keeps every previous version of each row in a history table, stamped with the time it was valid. You can query the table as it was at any moment. It answers “what did this row look like last Tuesday?” without triggers, but it does not record who made a change.

Creating a new temporal table

CREATE TABLE dbo.Customer (
    CustomerID  int            NOT NULL PRIMARY KEY CLUSTERED,
    Name        nvarchar(100)  NOT NULL,
    CreditLimit decimal(10,2)  NOT NULL,
    ValidFrom   datetime2(2)   GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo     datetime2(2)   GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory));

Converting an existing table

ALTER TABLE dbo.YourTable ADD
    ValidFrom datetime2(2) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL
        CONSTRAINT DF_YourTable_ValidFrom DEFAULT SYSUTCDATETIME(),
    ValidTo datetime2(2) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL
        CONSTRAINT DF_YourTable_ValidTo DEFAULT CONVERT(datetime2(2), '9999-12-31 23:59:59.99'),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE dbo.YourTable
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.YourTableHistory));

HIDDEN keeps the period columns out of SELECT * so existing code is not affected. On a large table, try this on a restored copy first to see how long it takes.

Querying history

The period columns are stored in UTC. On a converted table they are HIDDEN, so name them to see them.

-- The row as it was at a point in time
SELECT * FROM dbo.Customer
FOR SYSTEM_TIME AS OF '2026-10-01T09:00:00'
WHERE CustomerID = 42;

-- Every version of the row
SELECT CustomerID, Name, CreditLimit, ValidFrom, ValidTo
FROM dbo.Customer
FOR SYSTEM_TIME ALL
WHERE CustomerID = 42
ORDER BY ValidFrom;

Limiting history (SQL Server 2017 onwards)

ALTER TABLE dbo.Customer
SET (SYSTEM_VERSIONING = ON (HISTORY_RETENTION_PERIOD = 12 MONTHS));

Old rows are removed by a background task. It only runs when the database option TEMPORAL_HISTORY_RETENTION is ON, which is the default.

What is versioned

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name, t.name AS table_name,
       OBJECT_NAME(t.history_table_id) AS history_table,
       t.history_retention_period, t.history_retention_period_unit_desc
FROM sys.tables t
WHERE t.temporal_type = 2;

Watch out for

• Adding a column with ALTER TABLE is applied to the history table automatically. Most other schema changes need SYSTEM_VERSIONING = OFF, the same change on both tables, then versioning switched back on with the same HISTORY_TABLE.
• TRUNCATE TABLE is not allowed while versioning is on.
• History grows with every update. Busy tables need a retention period or a regular archive.
• It does not record who made the change. Add a ModifiedBy column filled by the application, or use SQL Server Audit alongside it.

From my SQL Server DBA handbook.