Temporal Tables in SQL Server: automatic history without triggers

System-Versioned Temporal Tables, introduced in SQL Server 2016 and the SQL:2011 standard, automatically maintain the history of all data changes — no triggers, no manual audit code.

Create a Temporal Table

CREATE TABLE dbo.Customers (
    customer_id  INT           PRIMARY KEY,
    name         NVARCHAR(200) NOT NULL,
    email        NVARCHAR(200) NOT NULL,
    plan         VARCHAR(50),
    -- Mandatory columns for temporal versioning
    valid_from   DATETIME2     GENERATED ALWAYS AS ROW START NOT NULL,
    valid_to     DATETIME2     GENERATED ALWAYS AS ROW END   NOT NULL,
    PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomersHistory));

Insert, update and delete normally

-- Nothing changes on write — SQL Server manages the history automatically
UPDATE dbo.Customers SET plan = 'Premium' WHERE customer_id = 101;
DELETE FROM dbo.Customers WHERE customer_id = 999;
-- The deleted record is preserved in the history table

Query data in the past

-- Current state
SELECT * FROM dbo.Customers WHERE customer_id = 101;

-- State on January 1, 2024
SELECT * FROM dbo.Customers
FOR SYSTEM_TIME AS OF '2024-01-01 00:00:00'
WHERE customer_id = 101;

-- Full history of a customer
SELECT *, valid_from, valid_to FROM dbo.Customers
FOR SYSTEM_TIME ALL
WHERE customer_id = 101
ORDER BY valid_from;

-- Changes in a period (auditing)
SELECT * FROM dbo.Customers
FOR SYSTEM_TIME BETWEEN '2024-06-01' AND '2024-06-30'
WHERE customer_id = 101;

Managing the history table

-- See the history table size
SELECT COUNT(*) FROM dbo.CustomersHistory;

-- Clean history older than 2 years
ALTER TABLE dbo.Customers SET (SYSTEM_VERSIONING = OFF);
DELETE FROM dbo.CustomersHistory WHERE valid_to < DATEADD(YEAR, -2, GETDATE());
ALTER TABLE dbo.Customers SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomersHistory));

Conclusion

Temporal Tables are the most elegant way to implement auditing and history in SQL Server. Zero additional code, standard SQL syntax for temporal queries, and full compatibility with the query optimizer.