Time Travel in Azure SQL

TemporalTable.sql

------------------------------------------------------------
-- Temporal Tables demo (idempotent, all in one script)
-- Uses unique table names to avoid conflicts with other demos
------------------------------------------------------------

------------------------------------------------------------
-- Reset (safe to rerun)
------------------------------------------------------------
IF OBJECT_ID('dbo.TodosTemporal', 'U') IS NOT NULL
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM sys.tables
        WHERE object_id = OBJECT_ID('dbo.TodosTemporal')
          AND temporal_type = 2
    )
    BEGIN
        ALTER TABLE dbo.TodosTemporal SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE dbo.TodosTemporal;
END
GO

IF OBJECT_ID('dbo.TodosTemporalHistory', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.TodosTemporalHistory;
END
GO

------------------------------------------------------------
-- Create temporal table with explicit history table
------------------------------------------------------------
CREATE TABLE dbo.TodosTemporal
(
    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.TodosTemporalHistory
    )
);
GO

------------------------------------------------------------
-- Seed data
------------------------------------------------------------
INSERT INTO dbo.TodosTemporal (Title, State) VALUES
    ('Buy groceries', 'pending'),
    ('Walk the dog',  'pending');
GO

------------------------------------------------------------
-- Capture an "as of" time, then make a change
------------------------------------------------------------
DECLARE @asOf DATETIME2 = SYSUTCDATETIME();

WAITFOR DELAY '00:00:01';

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

------------------------------------------------------------
-- Current data
------------------------------------------------------------
SELECT * FROM dbo.TodosTemporal;

------------------------------------------------------------
-- All versions (current + history)
------------------------------------------------------------
SELECT *
FROM dbo.TodosTemporal
FOR SYSTEM_TIME ALL
ORDER BY Id, ValidFrom;

------------------------------------------------------------
-- Data as of the captured time
------------------------------------------------------------
SELECT *
FROM dbo.TodosTemporal
FOR SYSTEM_TIME AS OF @asOf;