DB-유

Temporal Tables in SQL Server for Change History

· mssql, tsql, auditing

"What did this record look like last Tuesday?" I've answered that with hand-written audit triggers more often than I like to admit, and the triggers were always the part that broke. System-versioned temporal tables, available since SQL Server 2016, do it in the engine: every UPDATE and DELETE copies the old row into a history table with validity timestamps, and FOR SYSTEM_TIME lets you query the table as it was at any point. No triggers, no application changes, and the history table is a normal table you can index and partition.

Any edition of 2016 or later works (retention policies need 2017). The table must have a primary key, and plan storage: a frequently updated table can produce a history table many times its own size.

A new temporal table

CREATE TABLE dbo.Products (
    ProductId   int NOT NULL PRIMARY KEY CLUSTERED,
    Name        nvarchar(100) NOT NULL,
    Price       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.Products_History));

The two period columns are maintained by SQL Server in UTC. Always name the history table; otherwise you get an auto-generated name like MSSQL_TemporalHistoryFor_581577110. The history table gets a clustered index on (ValidTo, ValidFrom) by default, which suits the point-in-time queries.

Use datetime2(2) or lower precision unless you truly need 100-nanosecond resolution; it saves a byte per column in every history row.

Converting an existing table

For a table that already exists, add the period columns with defaults so existing rows get a validity range, then switch versioning on:

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

ALTER TABLE dbo.Customers
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Customers_History));

HIDDEN keeps the period columns out of SELECT *, which stops existing application code and INSERT ... SELECT * from breaking. The ALTER TABLE ADD takes a schema modification lock and updates every row, so run it in a maintenance window on large tables.

Querying history

As of a point in time:

SELECT ProductId, Name, Price, ValidFrom, ValidTo
FROM dbo.Products FOR SYSTEM_TIME AS OF '2020-06-02 09:00:00'
WHERE ProductId = 42;

All versions of a row within a range:

SELECT ProductId, Name, Price, ValidFrom, ValidTo
FROM dbo.Products FOR SYSTEM_TIME BETWEEN '2020-05-01' AND '2020-06-01'
WHERE ProductId = 42
ORDER BY ValidFrom;

Everything ever, current and historical:

SELECT * FROM dbo.Products FOR SYSTEM_TIME ALL WHERE ProductId = 42 ORDER BY ValidFrom;

FROM ... TO and CONTAINED IN are the other two sub-clauses. Remember the timestamps are UTC; convert your local time with AT TIME ZONE when users give you a wall-clock time:

DECLARE @local datetime2 = '2020-06-02 11:00:00';
DECLARE @utc datetime2 = CONVERT(datetime2, @local AT TIME ZONE 'Central European Standard Time' AT TIME ZONE 'UTC');
SELECT * FROM dbo.Products FOR SYSTEM_TIME AS OF @utc;

Retention

On SQL Server 2017 and later, the engine can purge history older than a given age. Enable it on the database once, then per table:

ALTER DATABASE Sales SET TEMPORAL_HISTORY_RETENTION ON;

ALTER TABLE dbo.Products
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Products_History, HISTORY_RETENTION_PERIOD = 12 MONTHS));

A background task deletes old rows in batches using the clustered index on ValidTo. On 2016 you clean up yourself: turn versioning off, delete from the history table, turn it back on.

Indexing the history table

The default clustered index handles AS OF queries well. If you often ask "show me all changes to product X", add an index on the key plus the period:

CREATE NONCLUSTERED INDEX IX_Products_History_ProductId
    ON dbo.Products_History (ProductId, ValidFrom, ValidTo);

For very large history tables, a clustered columnstore index on the history table (Enterprise, or Standard from 2016 SP1) compresses it dramatically and speeds up range scans.

Try it

UPDATE dbo.Products SET Price = Price * 1.1 WHERE ProductId = 42;
UPDATE dbo.Products SET Name = Name + ' (new)' WHERE ProductId = 42;

SELECT * FROM dbo.Products_History WHERE ProductId = 42;               -- two rows
SELECT * FROM dbo.Products FOR SYSTEM_TIME ALL WHERE ProductId = 42;    -- three rows

SELECT t.name, t.temporal_type_desc, h.name AS history_table, t.history_retention_period, t.history_retention_period_unit_desc
FROM sys.tables t
LEFT JOIN sys.tables h ON h.object_id = t.history_table_id
WHERE t.temporal_type = 2;

Each update should have produced one history row with ValidTo equal to the next version's ValidFrom.

Errors

  • Msg 13537: Cannot alter table 'Products_History' because the table is not a supported history table. You are trying to change or TRUNCATE the history table while versioning is on. ALTER TABLE dbo.Products SET (SYSTEM_VERSIONING = OFF) first, make the change, turn it back on.
  • Msg 13560: Cannot delete rows from a temporal history table 'dbo.Products_History'. Same rule: the history table is read-only while versioning is on. Use a retention policy or turn versioning off for the cleanup.
  • Msg 13542: ADD PERIOD FOR SYSTEM_TIME on table 'dbo.Customers' failed because there are open records with start of period set to a value in the future. Server clock was changed, or rows were inserted with a future default. Fix the offending ValidFrom values before enabling.
  • INSERT into the temporal table fails with Msg 13536: Cannot insert an explicit value into a GENERATED ALWAYS column. Application code lists the period columns explicitly or uses SELECT * from another table with the same shape. Use HIDDEN on the period columns or list the columns.
  • TRUNCATE TABLE dbo.Products fails. Truncate is not allowed on a system-versioned table. Use DELETE, which writes history, or switch versioning off.

My habits: always name the history table, hide the period columns on converted tables, and keep an eye on history size. It has replaced every homemade audit trigger I used to maintain, and I don't miss them.