Temporal, Ledger and Graph Tables

Use cases: auditing, point-in-time analysis, recovering from accidental changes, SCD Type 2.

28.1 System-Versioned Temporal Tables (2016+)

Use cases: auditing, point-in-time analysis, recovering from accidental changes, SCD Type 2.

CREATE TABLE dbo.Employees_T
(
    EmployeeID INT PRIMARY KEY,
    FullName   NVARCHAR(100),
    Salary     DECIMAL(12,2),
    ValidFrom  DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo    DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employees_T_History, HISTORY_RETENTION_PERIOD = 2 YEARS));

SELECT * FROM dbo.Employees_T FOR SYSTEM_TIME AS OF '2025-06-01';
SELECT * FROM dbo.Employees_T FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-12-31';
SELECT * FROM dbo.Employees_T FOR SYSTEM_TIME ALL WHERE EmployeeID = 1;
-- Also: FROM..TO, CONTAINED IN
ALTER TABLE dbo.Employees_T SET (SYSTEM_VERSIONING = OFF);  -- required for some schema changes

28.2 Ledger Tables (2022+)

Tamper-evident tables built on cryptographic hashing (a blockchain-like structure).

Updatable ledger tables keep history. Append-only ledger tables allow inserts only.

CREATE TABLE dbo.Payments (PaymentID INT, Amount DECIMAL(12,2))
WITH (SYSTEM_VERSIONING = ON, LEDGER = ON);
CREATE TABLE dbo.AuditEvents (EventID INT, Msg NVARCHAR(400)) WITH (LEDGER = ON (APPEND_ONLY = ON));
EXEC sp_generate_database_ledger_digest;
EXEC sp_verify_database_ledger_from_digest_storage;

28.3 Graph Tables (2017+)

Edge constraints: CONSTRAINT EC CONNECTION (Person TO Person).

CREATE TABLE dbo.Person (ID INT PRIMARY KEY, Name NVARCHAR(50)) AS NODE;
CREATE TABLE dbo.FriendOf (Since DATE) AS EDGE;

INSERT dbo.FriendOf ($from_id, $to_id, Since)
SELECT p1.$node_id, p2.$node_id, '2024-01-01' FROM dbo.Person p1, dbo.Person p2
WHERE p1.Name = 'Asha' AND p2.Name = 'Ravi';

SELECT p2.Name FROM dbo.Person p1, dbo.FriendOf f, dbo.Person p2
WHERE MATCH(p1-(f)->p2) AND p1.Name = 'Asha';

-- Shortest path (2019+)
SELECT p1.Name, STRING_AGG(p2.Name, '->') WITHIN GROUP (GRAPH PATH) AS Path
FROM dbo.Person p1, dbo.FriendOf FOR PATH f, dbo.Person FOR PATH p2
WHERE MATCH(SHORTEST_PATH(p1(-(f)->p2)+)) AND p1.Name = 'Asha';