Temporal Tables

An insurance company got a claim on Friday. The claim referenced a policy that was updated on Wednesday. The customer said their coverage included dental. The database showed it didn't. The customer insisted it did, before Wednesday's update.

The question: what did this policy look like last Tuesday? Not today. Not Wednesday. Tuesday.

With a regular table, you can't answer this. The UPDATE on Wednesday overwrote the old data. The Tuesday state is gone forever.

With a temporal table, you can answer it in one query:

SELECT * FROM policies
FOR SYSTEM_TIME AS OF '2025-03-25'
WHERE policy_id = 'P-12345';

That returns the row as it existed on March 25th, before the Wednesday update. No application-level versioning. No history tables you maintain yourself. The database handles it natively.

Temporal tables are the SQL standard's answer to data versioning. Instead of building versioning logic in your application, the database tracks the full history automatically. You get time-travel queries out of the box.

A system-versioned temporal table automatically tracks when each row was valid in the database. Every row has two hidden timestamp columns, the start of its validity period and the end.

In SQL Server (which has the most mature implementation):

CREATE TABLE policies (
 policy_id VARCHAR(50) PRIMARY KEY,
 customer_name VARCHAR(255),
 coverage_type VARCHAR(100),
 monthly_premium NUMERIC(10,2),
 
. System-versioned columns
 valid_from DATETIME2 GENERATED ALWAYS AS ROW START,
 valid_to DATETIME2 GENERATED ALWAYS AS ROW END,
 PERIOD FOR SYSTEM_TIME (valid_from, valid_to)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.policies_history));

When you INSERT a row, the database sets valid_from = NOW() and valid_to = 9999-12-31 (effectively "forever"). When you UPDATE a row, the database automatically:

1. Copies the current row to the history table with valid_to = NOW()
2. Updates the main row with the new data and valid_from = NOW()

When you DELETE a row, the database copies it to history with valid_to = NOW() and removes it from the main table.

You never manage this manually. The database does it all.

Time-travel queries:

-- What did the policy look like on March 25th?
SELECT * FROM policies
FOR SYSTEM_TIME AS OF '2025-03-25'
WHERE policy_id = 'P-12345';

-- Show all versions of this policy
SELECT * FROM policies
FOR SYSTEM_TIME ALL
WHERE policy_id = 'P-12345'
ORDER BY valid_from;

-- What policies were active between March 1 and March 31?
SELECT * FROM policies
FOR SYSTEM_TIME FROM '2025-03-01' TO '2025-03-31';

No JOINs. No subqueries. No version columns to manage. The SQL syntax reads like natural language. "show me this table as of that date."