Temporal tables, also known as system-versioned temporal tables, are a feature in SQL Server that provide built-in support for tracking historical data changes. They automatically keep a full history of data changes and allow you to query data as it was at any point in time.

Key Concepts

1. Current Table: The main table that holds the current data.

2. History Table: A separate table that stores the historical versions of the data.

Creating a Temporal Table

Here' how you can create a temporal table:

1. Create the Main Table and History Table:

   sql

   CREATE TABLE Employees (
       EmployeeID INT PRIMARY KEY,
       FirstName NVARCHAR(50),
       LastName NVARCHAR(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));
   

2. Enable System-Versioning: If you have an existing table, you can enable system-versioning like this:

   sql

   ALTER TABLE Employees
   ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
       ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
       PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
   
   ALTER TABLE Employees
   SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));
   

Querying Temporal Tables

You can query temporal tables to retrieve data at a specific point in time or over a period of time.

1. As Of Specific Point in Time:

   sql

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

2. Between Two Points in Time:

   sql

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

3. From Beginning to a Point in Time:

   sql

   SELECT *
   FROM Employees
   FOR SYSTEM_TIME FROM '2023-01-01T00:00:00' TO '2024-01-01T00:00:00';
   

Managing Temporal Tables

• Disable System-Versioning: Temporarily disable system-versioning if needed.

  sql

  ALTER TABLE Employees
  SET (SYSTEM_VERSIONING = OFF);
  

• Delete Historical Data: Purge historical data to manage storage.

  sql

  DELETE FROM EmployeesHistory
  WHERE ValidTo < '2023-01-01T00:00:00';
  

Benefits

• Data Auditing: Automatically maintain historical data for auditing purposes.

• Time Travel Queries: Easily query data as it was at any point in time.

• Simplified Data Management: Built-in support for maintaining and querying historical data.

Temporal tables are powerful for scenarios requiring historical data tracking and auditing. If you have specific use cases or need more examples, feel free to let me know!