๐ Key Insights: Temporal tables automatically track history of data changes. Perfect for auditing, compliance, and time-travel queries.
Need to know what data looked like last month? Temporal tables make it easy.
๐ Create Temporal Table
-- โ
Create table with temporal support
CREATE TABLE Orders
(
Id INT IDENTITY(1,1) PRIMARY KEY,
CustomerId INT NOT NULL,
OrderDate DATETIME2 NOT NULL,
TotalAmount DECIMAL(18,2) NOT NULL,
Status NVARCHAR(50) NOT NULL,
-- โ
System columns
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));
๐ Query Temporal Data
-- โ
Current data
SELECT * FROM Orders WHERE Id = 123;
-- โ
Data as of specific time
SELECT * FROM Orders
FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00'
WHERE Id = 123;
-- โ
Data between two times
SELECT * FROM Orders
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-01-31'
WHERE CustomerId = 456;
-- โ
All changes (including history)
SELECT * FROM Orders
FOR SYSTEM_TIME ALL
WHERE CustomerId = 456
ORDER BY SysStartTime;
-- โ
Last change before specific time
SELECT TOP 1 *
FROM Orders
FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00'
WHERE CustomerId = 456
ORDER BY SysStartTime DESC;
-- โ
Audit trail for specific order
SELECT
Id,
Status,
SysStartTime AS ChangedAt,
LAG(Status) OVER (ORDER BY SysStartTime) AS PreviousStatus
FROM Orders
FOR SYSTEM_TIME ALL
WHERE Id = 123
ORDER BY SysStartTime;
๐ก Pro Tip: Clean Up History
-- โ Manage history table ALTER TABLE Orders SET (SYSTEM_VERSIONING = OFF); -- โ Clean old history DELETE FROM OrdersHistory WHERE SysEndTime < DATEADD(YEAR, -1, GETUTCDATE()); -- โ Re-enable system versioning ALTER TABLE Orders SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));
