Temporal Tables in SQL

When working with databases, we often come across situations where we need to keep track of the changes to our data over time. Whether it's for auditing purposes, business intelligence, or just data recovery, tracking changes can be crucial. This is where the concept of "Temporal Tables" in SQL comes into play. Temporal tables, also known as system-versioned tables, store a full history of data changes and allow easy point-in-time analysis. Introduced in SQL Server 2016, this feature has been a game-changer in many aspects of database management.

Understanding Temporal Tables

Temporal tables in SQL Server are a type of table that record data about the state of data at any point in time. Unlike regular tables, temporal tables keep a history of data changes. A temporal table consists of two components: the current table, and the history table. The current table holds the present data, while the history table holds the historical data, storing all changes made to the current table.

Creating Temporal Tables

Let's dive into how you can create a temporal table. First, create a standard table, then add two columns to manage the period of validity (start and end) for each row. These columns must be of DATETIME2 type. Here's a quick example:

CREATE TABLE Employees (
    EmployeeID int NOT NULL PRIMARY KEY,
    Name nvarchar(100) NOT NULL,
    Position nvarchar(100) NOT NULL,
    Department nvarchar(100) NOT NULL,
    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));

In this example, we've created a temporal table called "Employees". The ValidFrom and ValidTo columns are used to manage the period of validity for each row. Note the use of GENERATED ALWAYS AS ROW START/END – this ensures that SQL Server will automatically handle these columns.

The PERIOD FOR SYSTEM_TIME clause is used to define the period of validity for each row. Lastly, the WITH (SYSTEM_VERSIONING = ON) clause is used to enable system-versioning for the table, and HISTORY_TABLE = dbo.EmployeesHistory to specify the history table.

Inserting Data into Temporal Tables

Inserting data into a temporal table is just like inserting data into any other table. Here's an example:

INSERT INTO Employees (EmployeeID, Name, Position, Department)
VALUES (1, 'John Doe', 'Software Engineer', 'IT');

This will insert a new row into the "Employees" table. SQL Server will automatically set the ValidFrom column to the current UTC time, and ValidTo to '9999-12-31 23:59:59.9999999'.

Updating Data in Temporal Tables

When you update data in a temporal table, SQL Server automatically stores a copy of the old data in the history table. Here's an example:

UPDATE Employees
SET Position = 'Senior Software Engineer'
WHERE EmployeeID = 1;

This will update John Doe's position in the "Employees" table. The old data will be copied into the "EmployeesHistory" table, with the ValidTo column set to the current UTC time.

Querying Data from Temporal Tables

One of the primary benefits of temporal tables is that they allow you to easily query data as it was at any point in time. Here's an example:

SELECT *
FROM Employees FOR SYSTEM_TIME AS OF '2023-05-19'
WHEREEmployeeID = 1;

In this query, we're retrieving the data for employee John Doe as it was on May 19, 2023. The FOR SYSTEM_TIME AS OF clause is used to specify the point in time that we're interested in.

Similarly, you can query the data within a specific period using BETWEEN keyword:

SELECT *
FROM Employees FOR SYSTEM_TIME BETWEEN '2023-01-01' AND '2023-12-31'
WHERE EmployeeID = 1;

This query will return all changes made to the data for employee John Doe in the year 2023.

Deleting Data from Temporal Tables

When you delete a row from a temporal table, SQL Server does not delete the corresponding rows from the history table. This allows you to recover data easily if needed. Here's an example:

DELETE FROM Employees
WHERE EmployeeID = 1;

This command will delete John Doe's record from the "Employees" table, but his data will remain in the "EmployeesHistory" table.

Modifying Temporal Tables

To modify the schema of a temporal table, you must first turn off system versioning. Here's how you can do it:

ALTER TABLE Employees SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE Employees ALTER COLUMN Department nvarchar(200) NOT NULL;
ALTER TABLE Employees SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));

These commands first turn off system versioning for the "Employees" table, then modify the "Department" column, and finally turn system versioning back on.

Frequently Asked Questions

1. Can I use temporal tables with existing tables?

Yes, you can convert an existing table to a temporal table. You'll need to add two DATETIME2 columns (for row start and end times), and enable system versioning.

2. What happens if I try to modify the data in the history table directly?

Direct modifications to the history table are not allowed when SYSTEM_VERSIONING is ON. This ensures the integrity of the historical data.

3. How can I clean up old data from the history table?

You can use the DELETE command to remove old data from the history table. However, you'll need to turn off system versioning first.

4. Can I use Temporal Tables in Azure SQL Database?

Yes, Azure SQL Database fully supports Temporal Tables.

5. Do all SQL versions support Temporal Tables?

No, Temporal Tables were introduced in SQL Server 2016. They are not available in earlier versions.

Using temporal tables in SQL is a great way to keep track of data changes in your application. Whether you need to audit data changes, recover data, or simply analyze data over time, temporal tables offer a built-in, easy-to-use solution. Just remember to plan for the increased storage requirements, as maintaining a history of data changes can consume a lot of space. As with any feature, be sure to test it thoroughly in your environment to ensure it meets your needs.