Time Travel in Azure SQL with Temporal Tables

Jerry Nixon

Principal Program Manager

Applications often need to know what data looked like before. Who changed it, when it changed, and what the previous values were. Rebuilding that history in application code is tedious and error prone. This is especially valuable when exposing a database to an AI agent through MCP servers like SQL MCP Server, where information discovery matters.

Learn more about SQL MCP Server at https://aka.ms/sql/mcp

Azure SQL includes a built in feature that tracks row history automatically. Temporal tables let the database keep a full change history without triggers, audit tables, or custom logic.

Working demo https://gist.github.com

What We Are Building

A table that automatically tracks every change over time.

Step 1: Create the Table

A temporal table requires two datetime columns that define the validity period. Azure SQL manages these automatically when you enable system versioning.

CREATE TABLE dbo.Todos
(
    Id        INT IDENTITY(1,1) PRIMARY KEY,
    Title     NVARCHAR(200) NOT NULL,
    State     NVARCHAR(20)  NOT NULL DEFAULT 'pending',

    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.TodosHistory
    )
);
GO

INSERT INTO dbo.Todos (Title, State) VALUES
    ('Buy groceries', 'pending'),
    ('Walk the dog',  'pending');
GO

This table is created as a system-versioned temporal table. The ValidFrom and ValidTo columns define the period during which each row version is valid, and Azure SQL maintains them automatically. With SYSTEM_VERSIONING = ON, every UPDATE and DELETE causes the previous version of the row to be written to the history table. By specifying HISTORY_TABLE = dbo.TodosHistory, you control the name and schema of the history table, while Azure SQL continues to manage its contents and lifecycle. If you do not provide a history table, Azure SQL creates one for you automatically.

Step 2: Update Data Normally

No special syntax is required. Changes are tracked automatically.

UPDATE dbo.Todos
SET State = 'completed'
WHERE Title = 'Walk the dog';
GO

Each update creates a historical version of the row.

Step 3: Query Current Data

SELECT * FROM dbo.Todos;

Result

Id	Title	State	ValidFrom	ValidTo
1	Buy groceries	pending	current	9999-12-31
2	Walk the dog	completed	current	9999-12-31

Step 4: Query Historical Data

Use FOR SYSTEM_TIME to query past versions.

All Versions

SELECT *
FROM dbo.Todos
FOR SYSTEM_TIME ALL
ORDER BY Id, ValidFrom;

Result

Id	Title	State	ValidFrom	ValidTo
2	Walk the dog	pending	earlier	update time
2	Walk the dog	completed	update time	9999-12-31

As Of a Point in Time

DECLARE @time DATETIME2 = DATEADD(MINUTE, -1, SYSUTCDATETIME());

SELECT *
FROM dbo.Todos
FOR SYSTEM_TIME AS OF @time;

This returns the table exactly as it existed at that moment.

Why Use Temporal Tables?

History is enforced by the database
No triggers or audit tables to maintain
All updates and deletes are captured
Application code stays simple
Works automatically with backups and restores
Allows reversible operations
Gives AI and automation traceability

Temporal tables move data history into the database engine itself. Once enabled, every change becomes observable, queryable, and reliable without extra code.