A robust feature that was added to SQL Server 2016 are temporal tables, which offer an integrated way to store and retrieve past data. They make it possible for you to monitor all alterations made to the data in a table, which can be very helpful for data analysis, auditing, and compliance. The definition, operation, and practical applications of temporal tables will all be covered in detail in this article.
How do Temporal Tables Work?
System-versioned tables, sometimes referred to as temporal tables, automatically keep track of all data modifications across time. They are made up of two parts:
- Current Table: Stores the current data.
- History Table: Automatically stores the historical versions of data.
When a row in the current table is updated or deleted, SQL Server moves the previous version of the row to the history table. This allows you to query historical data at any point in time.
Key Features of Temporal Tables
- Automated Data Management: Automatically manages the movement of historical data to the history table.
- Point-in-Time Analysis: Allows querying data as it appeared at any specific point in time.
- Auditing and Compliance: Provides an audit trail of changes for regulatory compliance.
- Data Recovery: Enables recovery of data to a previous state without complex restore operations.
Creating Temporal Tables
Creating a temporal table involves specifying system versioning during table creation. Here’s a step-by-step guide.
Define the Current Table: Include period columns for system start and end times.
CREATE TABLE Employees
(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
Position NVARCHAR(100),
Salary DECIMAL(18, 2),
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));
In this example,
SysStartTime and SysEndTime are the system period columns.
PERIOD FOR SYSTEM_TIME defines the period of system time.
Automatically Manage History Table
SQL Server creates and manages the history table.
Querying Temporal Tables
Temporal tables allow you to query historical data using the FOR SYSTEM_TIME clause.
SELECT * FROM Employees FOR SYSTEM_TIME BETWEEN '2023-01-01T00:00:00' AND '2023-01-01T23:59:59';
Retrieve Current Data
SELECT * FROM Employees;
Retrieve Data at a Specific Point in Time
SELECT * FROM Employees FOR SYSTEM_TIME AS OF '2023-01-01T12:00:00';
Retrieve Data Over a Time Range
SELECT * FROM Employees FOR SYSTEM_TIME BETWEEN '2023-01-01T00:00:00' AND '2023-01-01T23:59:59';
Retrieve All Historical Data
SELECT * FROM Employees FOR SYSTEM_TIME ALL;
Managing Temporal Tables
Turn Off System Versioning: You can turn off system versioning to make schema changes or manage data manually.
ALTER TABLE Employees SET (SYSTEM_VERSIONING = OFF);
Re-enable System Versioning
ALTER TABLE Employees SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));
Cleanup Old Data: To manage the size of the history table, you can periodically archive or clean up old data:
DELETE FROM EmployeesHistory WHERE SysEndTime < '2022-01-01T00:00:00';
Best Practices
- Indexing: Ensure proper indexing on period columns to optimize query performance.
- Data Retention Policies: Implement data retention policies to manage the growth of the history table.
- Security: Secure both current and history tables to prevent unauthorized access to sensitive historical data.
Conclusion
Temporal tables in SQL Server offer a robust solution for managing historical data, providing significant benefits for auditing, compliance, and point-in-time analysis. By automatically capturing and storing historical versions of data, they simplify the process of tracking changes over time. With the ability to query data as it existed at any point in time, temporal tables enhance the capabilities of SQL Server for modern data management needs. Implementing temporal tables involves a straightforward setup, and with best practices in place, they can significantly improve your data management strategy.