Temporal Tables & Time-Travel Queries

-- SQL Server: turn a normal table into a system-versioned temporal table.
-- The database will AUTOMATICALLY keep a full history of every change.

CREATE TABLE employees (
    id            INT PRIMARY KEY,
    name          VARCHAR(100),
    salary        DECIMAL(12,2),
    department    VARCHAR(50),
    -- These two columns ARE the "system time" period.
    -- The DB fills them in for you; you never write to them.
    valid_from    DATETIME2 GENERATED ALWAYS AS ROW START,
    valid_to      DA
...

-- Every UPDATE/DELETE now saves the previous version to history.
-- You write plain SQL — no triggers, no extra steps.

-- 1) Alice is hired on a 50,000 salary
INSERT INTO employees (id, name, salary, department)
VALUES (7, 'Alice', 50000, 'Sales');

-- 2) Three months later she gets a raise to 60,000
UPDATE employees SET salary = 60000 WHERE id = 7;

-- 3) Later she moves to Marketing on 65,000
UPDATE employees SET salary = 65000, department = 'Marketing' WHERE id = 7;

-- The employees table 
...

-- Ask for the complete timeline of one row.
-- FOR SYSTEM_TIME ALL = current row + every historical version.

SELECT name, salary, department, valid_from, valid_to
FROM employees
FOR SYSTEM_TIME ALL
WHERE id = 7
ORDER BY valid_from;

-- Each row's valid_from / valid_to says exactly when that
-- version was the live one. The newest row ends at 9999-12-31
-- (a "max date" the engine uses to mean "still current").

-- Time-travel: see the table EXACTLY as it was at a past instant.
-- FOR SYSTEM_TIME AS OF '<timestamp>' rewinds the whole table.

SELECT name, salary, department
FROM employees
FOR SYSTEM_TIME AS OF '2024-04-01 09:00:00'
WHERE id = 7;

-- On 1 April 2024 Alice was still in Sales on 60,000,
-- so this returns the OLD values — not today's 65,000.
-- The DB transparently reads from history for you.

-- Range queries: every version that was active during a window.

-- BETWEEN '..a..' AND '..b..'  -> versions active at any point in [a, b]
SELECT name, salary, valid_from, valid_to
FROM employees
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-12-31'
WHERE id = 7
ORDER BY valid_from;

-- FROM '..a..' TO '..b..'  -> same idea, but EXCLUSIVE of the end instant.
SELECT name, salary
FROM employees
FOR SYSTEM_TIME FROM '2024-01-01' TO '2024-07-01'
WHERE id = 7;

-- Use these for "what did this row lo
...

-- VALID time (a.k.a. application time) is a DIFFERENT idea.
-- It records when a fact is true in the REAL WORLD, and YOU set it.
-- System time = when the DB stored the row; valid time = when it applies.

CREATE TABLE subscriptions (
    id            INT PRIMARY KEY,
    customer_id   INT,
    plan          VARCHAR(50),
    monthly_price DECIMAL(8,2),
    -- You choose these dates; the database does not auto-fill them.
    valid_from    DATE NOT NULL,
    valid_to      DATE NOT NULL
);

INSERT
...