SQL Server Temporal Tables: Time Travel Queries

Temporal tables automatically track history. Query what data looked like at any point in time.

Create Temporal Table

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name NVARCHAR(100),
    Salary DECIMAL(10,2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
) WITH (SYSTEM_VERSIONING = ON);

Query History

SELECT * FROM Employees 
FOR SYSTEM_TIME AS OF '2020-01-01'
WHERE Id = 1;

Use Cases

• Audit trails
• Point-in-time recovery
• Slowly changing dimensions