Temporal Table in MS SQL Database

Introduction

Temporal tables in Microsoft SQL Server are a special type of table designed to automatically track and store historical changes to data. Introduced in SQL Server 2016, they provide a built-in mechanism for auditing, versioning, and recovering data over time.

Why Use Temporal Tables?

Key Benefits:

• Data Versioning: Automatically maintains historical data changes.
• Point-in-Time Analysis: Retrieve records as they were at a specific time.
• Auditing and Compliance: Helps in tracking data changes for regulatory requirements.
• Recovery of Data: Restore accidentally deleted or modified data.
• Performance Optimization: Eliminates the need for manual triggers and log-based change tracking.

How Temporal Tables Work

Temporal tables consist of two tables:

1. System-Versioned Table (Current Data Table) – Stores the latest version of records.
2. History Table – Stores previous versions of records with time-range metadata.

Each row in the history table includes:

• SysStartTime: Timestamp of when the row became valid.
• SysEndTime: Timestamp of when the row was replaced.

Creating a Temporal Table

Example:

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

This creates two tables:

• Employees (current data table)
• EmployeesHistory (system-managed history table)

Inserting and Updating Data

Insert Data:

INSERT INTO Employees (EmployeeID, Name, Position, Salary)
VALUES (1, 'John Doe', 'Developer', 70000);

Update Data:

UPDATE Employees SET Salary = 75000 WHERE EmployeeID = 1;

This automatically moves the previous version of the record to the EmployeesHistory table.

Querying Data from a Temporal Table

Retrieving Current Data:

SELECT * FROM Employees;

Retrieving Historical Data:

SELECT * FROM EmployeesHistory;

Point-in-Time Query:

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

Retrieving Changes Over a Period:

SELECT * FROM Employees
FOR SYSTEM_TIME BETWEEN '2024-01-01T00:00:00' AND '2024-01-31T23:59:59';

Disabling and Dropping Temporal Tables

Disabling System Versioning:

ALTER TABLE Employees SET (SYSTEM_VERSIONING = OFF);

Dropping a Temporal Table:

DROP TABLE Employees, EmployeesHistory;

Best Practices for Temporal Tables

• Use datetime2 for better precision in tracking changes.
• Enable data compression on history tables to save space.
• Regularly archive old data from the history table to optimize performance.
• Implement indexing on history tables for efficient queries.
• Use FOR SYSTEM_TIME queries for accurate time-based analysis.

References

For further reading, check out:

• Microsoft SQL Server Documentation
• Best Practices for Temporal Tables

---

Conclusion: Temporal tables in SQL Server provide an efficient and automated way to track historical data changes. They are useful for auditing, compliance, and point-in-time recovery, making them a valuable tool for database administrators and developers.

🚀 Happy Querying!