Advanced SQL: Temporal Tables for Versioning Data

Temporal tables automatically keep a full history of data changes, allowing you to query data as it existed at any point in time. This is achieved through the use of two tables: the current table and a history table. The current table holds the latest version of the data, while the history table stores all previous versions.

Creating a Temporal Table

To create a temporal table, you need to define the table structure along with two additional columns to store the period of validity for each record. Here’s an example of how to create a temporal table in SQL Server:

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

Explanation of the Code:

• EmployeeID: The primary key for the table.
• Name, Position, Salary: Standard columns for employee data.
• ValidFrom and ValidTo: These columns automatically track when a record is valid.
• PERIOD FOR SYSTEM_TIME: This clause specifies the columns that define the time period for which the row is valid.
• SYSTEM_VERSIONING = ON: This enables the temporal feature, creating a history table named EmployeesHistory.

Inserting Data

Once the temporal table is created, you can insert data just like you would with a regular table:

INSERT INTO Employees (EmployeeID, Name, Position, Salary)
VALUES (1, 'John Doe', 'Software Engineer', 75000.00),
       (2, 'Jane Smith', 'Project Manager', 90000.00);

Updating Data

When you update a record in a temporal table, the system automatically moves the old version of the record to the history table. Here’s how to update an employee’s salary:

UPDATE Employees
SET Salary = 80000.00
WHERE EmployeeID = 1;

After this update, the EmployeesHistory table will contain the previous salary of John Doe, along with the time period when that record was valid.

Querying Historical Data

To query data from the current table, you can simply use a standard SELECT statement:

SELECT * FROM Employees;

To retrieve historical data, you can query the history table directly:

SELECT * FROM EmployeesHistory;

Alternatively, you can use the FOR SYSTEM_TIME clause to query the current table for data as it existed at a specific point in time:

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

Example Query Results

Best Practices

1. Define Clear Period Columns: Ensure that the ValidFrom and ValidTo columns are correctly set up to avoid data integrity issues.
2. Regular Maintenance: Periodically review the history table to archive or delete old records if they are no longer needed.
3. Indexing: Consider indexing the history table for better performance when querying historical data.
4. Limit History Table Size: Be mindful of the size of the history table; excessive growth can impact performance.

Conclusion

Temporal tables provide a robust mechanism for tracking changes to your data over time, making them invaluable for applications requiring historical data analysis. By implementing best practices, you can ensure that your temporal tables are efficient and maintainable.

Learn more with useful resources:

• Microsoft Docs: Temporal Tables
• SQL Server Central: Understanding Temporal Tables
• Dataedo Blog: SQL Server Temporal Tables