SQL Server Temporal Tables: Time Travel Queries

Temporal Tables (System-Versioned Tables) automatically keep a full history of data changes. When you update a row, the old version is moved to a history table. This is built-in to SQL Server and Azure SQL.

Creating a Temporal Table

CREATE TABLE dbo.Products
(
    ProductId int IDENTITY(1,1) PRIMARY KEY,
    Name nvarchar(100) NOT NULL,
    Price decimal(10,2) NOT NULL,
    
    -- Period columns
    SysStartTime datetime2 GENERATED ALWAYS AS ROW START,
    SysEndTime datetime2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));

Time Travel Querying

Ask “What was the price of this product yesterday?”

SELECT * FROM dbo.Products
FOR SYSTEM_TIME AS OF '2020-08-23 12:00:00'
WHERE ProductId = 1;

Key Takeaways

• Great for audit trails without writing triggers or app logic.
• Good for slow-changing dimension (SCD) type logic.
• Be aware of storage costs; history tables grow forever.