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
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.
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
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.
Sharing is caring
Did you like what Mehul Mohan wrote? Thank them for their work by sharing it on social media.
No comments so far
Leave a question/feedback and someone will get back to you