Temporal Tables in Azure SQL

Temporal tables, also known as system-versioned temporal tables, are a database feature that automatically records the full history of every data change in a separate history table. They are defined by the SQL:2011 standard and are fully supported in Azure SQL Database, Azure SQL Managed Instance, and SQL Server 2016+. The core idea is that each row in a temporal table has a period of validity defined by two datetime2 columns: ValidFrom (start time) and ValidTo (end time). When a row is updated or deleted, the old version is moved to the history table with its ValidTo set to the time of the change. The current table always contains only the latest version. This gives you the ability to query the data as it existed at any point in time – a capability called point-in-time analysis or time travel.

When you create a temporal table, you must specify a history table (or let the system create one automatically). The current table must have a primary key and exactly two datetime2 columns defined with GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END. These columns are populated automatically by the engine: - ValidFrom: Set to the transaction's start time when the row is inserted or updated. - ValidTo: Set to '9999-12-31 23:59:59.9999999' for current rows; when a row is updated or deleted, the old row's ValidTo is set to the transaction's start time.

Internally, the engine uses a transaction-based mechanism. When an UPDATE or DELETE occurs, the engine first copies the old row to the history table, setting its ValidTo to the current system time (UTC). Then it performs the modification on the current table, updating the ValidFrom of the new row (for UPDATE) or removing the row (for DELETE). Both operations happen within the same transaction, ensuring consistency. The history table is append-only and cannot be directly modified by users – it is system-managed. The engine automatically partitions the history table if it grows large, but you can also manage partitioning manually.

To create a temporal table:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(100),
    Salary DECIMAL(10,2),
    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.EmployeeHistory));

To query historical data:

SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2023-01-01 12:00:00'
WHERE EmployeeID = 1;

Other temporal clauses: - FOR SYSTEM_TIME FROM <start> TO <end> – returns all row versions that were active within a period. - FOR SYSTEM_TIME BETWEEN <start> AND <end> – includes rows that were active at the start and end boundaries. - FOR SYSTEM_TIME CONTAINED IN (<start>, <end>) – only rows that were entirely within the period. - FOR SYSTEM_TIME ALL – returns all rows from both current and history tables.

To verify temporal table properties:

SELECT * FROM sys.tables WHERE temporal_type_desc = 'SYSTEM_VERSIONED_TEMPORAL_TABLE';
SELECT * FROM sys.tables WHERE object_id = OBJECT_ID('EmployeeHistory');

Step 1: Create the current table with period columns Define the table with two datetime2 columns marked as GENERATED ALWAYS AS ROW START and GENERATED ALWAYS AS ROW END, and include a PERIOD FOR SYSTEM_TIME clause.

Step 2: Enable SYSTEM_VERSIONING Specify WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = ...)). The history table can be named explicitly or created automatically.

Step 3: Insert and modify data Insert rows normally. Each UPDATE or DELETE automatically moves the old version to the history table.

Step 4: Query historical data Use FOR SYSTEM_TIME AS OF @time to see the state at a specific point.

Step 5: Query all changes Use FOR SYSTEM_TIME ALL to combine current and history rows.

Step 6: Disable temporal (if needed) Use ALTER TABLE ... SET (SYSTEM_VERSIONING = OFF) to stop tracking. This detaches the history table (which remains as a regular table).

Step 7: Manage history retention Implement a cleanup job that deletes rows older than a threshold, or use partition switching to drop old partitions.

A company wants to track employee salary changes. They create a temporal table EmployeeSalary. After a year, they want to know the salary of employee 101 on June 1, 2023. They run:

SELECT Salary FROM EmployeeSalary
FOR SYSTEM_TIME AS OF '2023-06-01 10:00:00'
WHERE EmployeeID = 101;

The engine returns the correct salary by looking at both the current table and history table, using the period columns to determine which row was active at that moment.

A multinational corporation with 50,000 employees uses Azure SQL Database to store HR data. They need to comply with GDPR and internal audit requirements to show salary history for any employee over the past 7 years. They implement a temporal table EmployeeSalary with columns EmployeeID, Salary, EffectiveDate (as period start), and EndDate (as period end). The history table grows by about 10,000 rows per day. They run a nightly job to delete history older than 7 years using a DELETE statement. Performance is acceptable because the history table has a clustered index on ValidTo and ValidFrom. Auditors can query: SELECT * FROM EmployeeSalary FOR SYSTEM_TIME AS OF '2020-01-01' WHERE EmployeeID = 12345. This returns the exact salary at that date. Without temporal, they would need custom audit tables and triggers, which are error-prone and harder to maintain.

An e-commerce company with 1 million products uses temporal tables to track changes to product prices and descriptions. They enable temporal on the Product table. When a product price changes, the old price is automatically saved. They use FOR SYSTEM_TIME ALL to generate reports showing price trends. A common issue is that the history table grows very fast (100,000 rows per day). They implement partition switching: the history table is partitioned monthly on ValidTo. Each month, they switch the oldest partition to a staging table and then drop it. This keeps the history table manageable. They also use a view that queries FOR SYSTEM_TIME AS OF GETUTCDATE() for the current product catalog, which performs well because it only reads the current table.

A law firm uses temporal tables to track changes to legal documents stored as metadata in Azure SQL. Each document has a DocumentVersion table that is temporal. Lawyers can query the state of a document as of a specific date for discovery purposes. The main challenge is that the history table includes all intermediate versions, which can be hundreds per document. They use a retention policy of 10 years as required by law. They also set up alerts when the history table exceeds 100 GB. One misconfiguration they encountered: initially they used DATETIME2 with default precision (7 digits), which caused some rows to have the same timestamp for rapid updates. They changed to DATETIME2(3) (millisecond precision) to avoid ambiguity. Another mistake: they forgot to convert query times to UTC, leading to incorrect results. They now always use GETUTCDATE() in temporal queries.