For an instructor lead, in-depth look at learning SQL click below.
Temporal tables, also known as system-versioned temporal tables, are a new feature in SQL Server 2016. They are designed to keep a full history of data changes, allowing you to query ‘point in time’ data, which is incredibly useful in scenarios such as data auditing, data forensics, and trend analysis.
Creating Temporal Tables
Temporal tables consist of two parts: the current table (also known as the ‘real’ table) and the history table. Let’s look at how to create a temporal table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE Employees ( [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED, [Name] NVARCHAR(100) NOT NULL, [Position] NVARCHAR(100) NOT NULL, [Department] NVARCHAR(100) NOT NULL, [Startdate] DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL, [Enddate] DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL, PERIOD FOR SYSTEM_TIME (Startdate, Enddate) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory)); |
This query will create a new ‘Employees’ table along with its corresponding history table ‘EmployeesHistory’. The ‘Startdate’ and ‘Enddate’ columns are special; they represent the period for which a row is valid in the ‘real’ table. SQL Server will automatically manage these columns for you.
Querying Temporal Tables
Querying data from temporal tables is easy and straightforward. You can query it as the same way like querying a non-temporal table. The difference will come when you query the data from a specific point of time or within a period of time.
|
1 2 3 4 5 6 |
SELECT * FROM employees FOR SYSTEM_TIME AS OF '2022-01-01T12:00:00.000' WHERE department = 'HR'; |
This query will return all the ‘HR’ department employees as of the first moment of 2022.
Updating and Deleting Data
Anytime a record is updated or deleted, SQL Server will automatically move the old version of the record to the history table. The following example changes an employee’s department and will create a record in the EmployeesHistory table:
|
1 2 3 4 5 |
UPDATE Employees SET Department = 'Marketing' WHERE EmployeeID = 1; |
Temporal tables are powerful tools when it comes to tracking data changes over time. They can also serve as a form of backup or data recovery, as they make it quick and easy to restore a table to a previous state.
Conclusion
Overall, temporal tables in SQL Server provides a simple and efficient way to keep track of data history and enable point-in-time analysis. This is just the tip of the iceberg of what you can do with temporal tables. There’s a lot more to explore!