Temporal Tables in SQL

Temporal tables are a powerful feature in SQL that allows tracking and analyzing data changes over time. They enable you to query historical data as it existed at a specific point or within a time range, making them invaluable for auditing, debugging, and data recovery.

Introduction to Temporal Tables

Definition and Purpose

Temporal tables are a type of table in SQL that records historical versions of data. They allow:

  • Storing data changes over time.
  • Retrieving data as it existed at any past point.
  • Simplifying data auditing and debugging.

Use Cases

  1. Auditing: Track how and when data changes occurred.
  2. Point-in-Time Analysis: Retrieve data as of a specific date.
  3. Data Recovery: Restore accidentally deleted or altered data.

Types of Temporal Tables

System-Versioned Temporal Tables

These tables automatically track changes, maintaining a history of all updates and deletions.

  • Current Table: Holds the current version of the data.
  • History Table: Maintains all historical data.

Application-Time Period Temporal Tables

These tables allow specifying a custom time period for data validity, enabling user-defined temporal constraints.

Creating Temporal Tables

Syntax for System-Versioned Temporal Tables

				
					CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Position NVARCHAR(50),
    Salary DECIMAL(10, 2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Explanation

  • ValidFrom and ValidTo: Automatically track the start and end times for each row.
  • PERIOD FOR SYSTEM_TIME: Indicates the columns for tracking system time.
  • SYSTEM_VERSIONING = ON: Activates system versioning, linking the current table with a history table.

Output

  • The Employee table tracks current data.
  • The EmployeeHistory table stores historical versions of each row.

Example: Inserting and Updating Data

				
					-- Insert data into Employee table
INSERT INTO Employee (EmployeeID, Name, Position, Salary)
VALUES (1, 'Alice', 'Developer', 60000);

-- Update the salary
UPDATE Employee
SET Salary = 65000
WHERE EmployeeID = 1;

Explanation

  1. Insert Operation: Adds a row to the Employee table.
  2. Update Operation: Moves the original row to EmployeeHistory with timestamps and updates the current table.

History Table Output

EmployeeIDNamePositionSalaryValidFromValidTo
1AliceDeveloper600002024-11-10 10:00:002024-11-11 12:00:00

Current Table Output

EmployeeIDNamePositionSalaryValidFromValidTo
1AliceDeveloper650002024-11-11 12:00:009999-12-31 23:59:59.999

Querying Temporal Tables

Retrieving Historical Data

				
					SELECT *
FROM Employee FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1;

Output:

EmployeeIDNamePositionSalaryValidFromValidTo
1AliceDeveloper600002024-11-10 10:00:002024-11-11 12:00:00
1AliceDeveloper650002024-11-11 12:00:009999-12-31 23:59:59.999

Time Travel Queries

Retrieve data at a specific point:

				
					SELECT *
FROM Employee FOR SYSTEM_TIME AS OF '2024-11-10 11:00:00';

Output:

EmployeeIDNamePositionSalaryValidFromValidTo
1AliceDeveloper600002024-11-10 10:00:002024-11-11 12:00:00

Altering Temporal Tables

Altering Temporal Tables

				
					ALTER TABLE Employee ADD Department NVARCHAR(50);

Adjusting Time Periods

You can update the history table manually for corrections:

Best Practices

  1. Use Appropriate Indexing: Index ValidFrom and ValidTo for efficient queries.
  2. Monitor History Table Growth: Regularly clean up unnecessary historical data.
  3. Ensure Consistency: Avoid manually altering system-managed timestamps.

Temporal tables in SQL provide robust capabilities for tracking and analyzing data changes over time. By leveraging features like system-versioning and application-defined periods, you can ensure data integrity, simplify auditing, and support historical queries effortlessly. Happy Coding!❤️

Table of Contents

Home
Roadmap
ATS Resume
Playground
Resources