For an instructor lead, in-depth look at learning SQL click below.
If you are dealing with data that changes over time and need to maintain a historical record of those changes, SQL Server Temporal Tables is a feature you should know. Temporal Tables, introduced in SQL Server 2016, are system-versioned tables that keep a history of data changes and allow easy point in time analysis.
Setting Up a Temporal Table
To start using temporal tables, you first need to set up a system-versioned table. You need to define two tables: the current table and the history table. The current table keeps the current data, and the history table tracks changes.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE TABLE Employee ( [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED ,[Name] NVARCHAR(100) NOT NULL ,[Position] NVARCHAR(100) NOT NULL ,[Department] NVARCHAR(100) NOT NULL ,[ValidFrom] DATETIME2 (2) GENERATED ALWAYS AS ROW START ,[ValidTo] DATETIME2 (2) GENERATED ALWAYS AS ROW END ,PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory)); |
Modifying Data in a Temporal Table
Once you have your temporal table set, ANY changes (INSERT, UPDATE, DELETE) you make to the data in the current table will be tracked in the history table.
|
1 2 3 4 5 |
UPDATE Employee SET Position = 'Senior Developer' WHERE EmployeeID = 1; |
Querying Data at a Specific Point in Time
The true power of temporal tables comes when querying data at a specific point in time. You can see the state of your data as it was at any previous point in time using the FOR SYSTEM_TIME clause.
|
1 2 3 4 5 6 |
SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2021-05-01T01:02:03.123' WHERE EmployeeID = 1; |
This feature of SQL Server provides powerful tools for easily managing and analyzing your historical data, making it especially useful in debugging, auditing, and data forensics.