SQL Server

System-Versioned Tables

Concept and Purpose

Temporal Tables in Microsoft SQL Server are a native database feature that automatically maintains the history of all changes to table data.

The feature allows organizations to:

. track how data evolved over time
. query previous versions of rows
. perform point-in-time analytics
. reconstruct accidentally modified data
. satisfy auditing and compliance requirements

All of this is implemented directly in the database engine, without triggers, custom code, or external logging frameworks.

Architectural Overview

A temporal table is physically implemented as two tables:

the current table containing active records
the history table containing previous versions of those records

SQL Server manages these tables as a single logical entity.

Whenever a row is modified, SQL Server automatically:

copies the previous version to the history table
updates timestamps
guarantees transactional consistency

From the developer perspective, only the main table is used.
The history table is completely maintained by the system.

Creating a Temporal Table

CREATE TABLE Employees
(
EmpID INT PRIMARY KEY,
Name NVARCHAR(100),
Salary DECIMAL(10,2),

SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,

SysEndTime   DATETIME2 GENERATED ALWAYS AS ROW END   NOT NULL,

PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)

)
WITH (SYSTEM_VERSIONING = ON);

After executing this statement SQL Server automatically:

creates an associated history table
enables version tracking
manages timestamps
activates time-travel querying

No additional development is required.

Behavior on Data Modifications

Insert Operations

INSERT INTO Employees (EmpID, Name, Salary)
VALUES (1, 'John', 1000);

the row is added to the current table
no history record is created
this is the initial version

Update Operations

UPDATE Employees
SET Salary = 1200
WHERE EmpID = 1;

SQL Server automatically performs:

. storage of the old version in the history table
. update of the current table
. assignment of correct validity timestamps

All actions are handled internally by the engine.

Delete Operations

DELETE FROM Employees
WHERE EmpID = 1;

the row is removed from the current table
its last valid version is saved in the history table
deleted data remains permanently accessible

Time-Travel Querying

Temporal tables provide native SQL syntax for accessing historical data.

Query Data As of a Specific Moment

SELECT *
FROM Employees
FOR SYSTEM_TIME AS OF '2024-05-01 10:30';

This returns the table exactly as it existed at that time.

Query Data for a Time Range

SELECT *
FROM Employees
FOR SYSTEM_TIME BETWEEN
'2024-01-01' AND '2024-12-31';

This allows analysis of changes during a defined period.

Retrieve All Versions of a Row

SELECT *
FROM Employees
FOR SYSTEM_TIME ALL
WHERE EmpID = 1;

This returns the current version together with all historical versions.

Schema Changes

Temporal tables fully support DDL operations.

For example:

ALTER TABLE Employees ADD Department NVARCHAR(50);

SQL Server automatically:

applies the change to the current table
applies the same change to the history table
keeps versioning fully consistent

No manual synchronization is necessary.

Typical Business Use Cases

Temporal tables are especially useful for:

financial corrections
salary and contract history
regulatory compliance
audit requirements
debugging data issues
tracking configuration changes
forensic analysis

Key Benefits

Temporal tables provide:

automatic version tracking
declarative time-travel queries
permanent history storage
transactional consistency
elimination of custom triggers
simplified auditing
minimal performance overhead

Comparative Summary Matrix
SQL Server Temporal Tables vs Other RDBMS Platforms

Comparative Summary Matrix
SQL Server Temporal Tables vs Other RDBMS Platforms

Restoring Data – Correct Logical Scenarios

Scenario A - Restore Correct Value from History

UPDATE e
SET e.Salary = h.Salary
FROM Employees e
JOIN Employees
FOR SYSTEM_TIME AS OF '2024-06-01 09:00' h
ON e.EmpID = h.EmpID;

This restores the previous correct state without backups.

Scenario B – Restoring After a DELETE

If the row was deleted:

DELETE FROM Employees WHERE EmpID = 1;

The row no longer exists in the current table.

An UPDATE will affect zero rows, which is expected.

Correct Recovery Method

To restore a deleted row:

INSERT INTO Employees (EmpID, Name, Salary)
SELECT TOP (1) EmpID, Name, Salary
FROM Employees
FOR SYSTEM_TIME ALL
WHERE EmpID = 1
ORDER BY SysEndTime DESC;

After this insert, the row returns to the current table and normal operations can continue.

If you need to drop table

Disable system versioning

ALTER TABLE Employees

SET (SYSTEM_VERSIONING = OFF);

This detaches the history mechanism.

Drop the main table

DROP TABLE Employees;

Final Technical Conclusion

SQL Server Temporal Tables represent a truly distinctive capability among relational databases.