Understanding System-Versioned Tables in SQL

System-versioned tables in SQL are a feature that allows automatic tracking of data changes over time. These tables help maintain historical versions of records, making it easier to audit changes and retrieve past data.

How System-Versioned Tables Work?

A system-versioned table consists of,

Main table: Stores the current data.
History table: Automatically keeps historical versions of records.

Creating a System-Versioned Table

The following SQL script creates a system-versioned table named Employees along with a history table.

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

Querying System-Versioned Tables

Get Current Data

SELECT *  
FROM Employees;

Get Historical Data

SELECT *  
FROM EmployeesHistory;

Retrieve Data at a Specific Time

SELECT * 
FROM Employees 
FOR SYSTEM_TIME AS OF '2024-03-01T10:00:00';

Advantages of System-Versioned Tables

Automatically tracks data changes.
Provides historical auditing without manual tracking.
Allows time-travel queries to retrieve past data states.
Ensures data integrity and compliance with regulatory requirements.

System-versioned tables are highly beneficial for applications that require auditing, data recovery, and change tracking.