Time Journey in Azure SQL with Temporal Tables

Applications typically must know what knowledge regarded like earlier than. Who modified it, when it modified, and what the earlier values had been. Rebuilding that historical past in software code is tedious and error inclined. This is very invaluable when exposing a database to an AI agent by means of MCP servers like SQL MCP Server, the place data discovery issues.

Learn extra about SQL MCP Server at

Azure SQL features a inbuilt characteristic that tracks row historical past robotically. Temporal tables let the database preserve a full change historical past with out triggers, audit tables, or customized logic.

Working demo

What We Are Building

A desk that robotically tracks each change over time.

Step 1: Create the Table

A temporal desk requires two datetime columns that outline the validity interval. Azure SQL manages these robotically once you allow 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 canine',  'pending');
GO

This desk is created as a system-versioned temporal desk. The ValidFrom and ValidTo columns outline the interval throughout which every row model is legitimate, and Azure SQL maintains them robotically. With SYSTEM_VERSIONING = ON, each UPDATE and DELETE causes the earlier model of the row to be written to the historical past desk. By specifying HISTORY_TABLE = dbo.TodosHistory, you management the identify and schema of the historical past desk, whereas Azure SQL continues to handle its contents and lifecycle. If you don’t present a historical past desk, Azure SQL creates one for you robotically.

Step 2: Update Data Normally

No particular syntax is required. Changes are tracked robotically.

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

Each replace creates a historic model of the row.

Step 3: Query Current Data

SELECT * FROM dbo.Todos;

Result

Step 4: Query Historical Data

Use FOR SYSTEM_TIME to question previous variations.

All Versions

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

Result

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 desk precisely because it existed at that second.

Why Use Temporal Tables?

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

Temporal tables transfer knowledge historical past into the database engine itself. Once enabled, each change turns into observable, queryable, and dependable with out additional code.