How to work with Temporal Tables in SQL Server

How to work with Temporal Tables in SQL Server

SQL Server 2016 introduced the support for temporal tables (also referred to as system-versioned temporal tables). This feature enables you to track historical data by capturing not only the current data but also the state of data at any given point in time. Temporal tables are a significant addition to databases, based on the ANSI SQL 2011 standard.

SELECT name, object_id, temporal_type, temporal_type_desc FROM sys.tables;

temporal_type	temporal_type_desc
0	NON_TEMPORAL_TABLE
1	HISTORY_TABLE
2	SYSTEM_VERSIONED_TEMPORAL_TABLE

SELECT OBJECT_NAME(object_id) AS table_name, * 
FROM sys.periods
table_name name period_type period_type_desc object_id start_column_id end_column_id
Employee SYSTEM_TIME 1 SYSTEM_TIME_PERIOD 581577110 7 8

SELECT name AS 'Column_Name', generated_always_type, generated_always_type_desc 
FROM sys.columns

List all temporal tables along with their history tables.

SELECT schema_name(t.schema_id) AS temporal_table_schema, 
       t.name AS temporal_table_name,
       schema_name(h.schema_id) AS history_table_schema, 
       h.name AS history_table_name, 
       CASE 
         WHEN t.history_retention_period = -1 
         THEN 'INFINITE' 
         ELSE CAST(t.history_retention_period AS VARCHAR) + ' ' + t.history_retention_period_unit_desc + 'S' 
       END AS retention_period 
FROM sys.tables t 
LEFT OUTER JOIN sys.tables h ON t.history_table_id = h.object_id 
WHERE t.temporal_type = 2 
ORDER BY temporal_table_schema, temporal_table_name

Creating a table with a history table.

CREATE TABLE dbo.Employee 
( 
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED, 
  [Name] nvarchar(100) NOT NULL, 
  [Position] varchar(100) NOT NULL, 
  [Department] varchar(100) NOT NULL, 
  [Address] nvarchar(1024) NOT NULL, 
  [AnnualSalary] decimal(10,2) 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.EmployeeHistory));

The script above will create two tables: Employee and EmployeeHistory.

Inserting and updating data in the tables above.

INSERT INTO Employee (EmployeeID, Name, Position, Department, Address, AnnualSalary) 
SELECT 1, 'himanshu', 'dba', 'IT', 'BC', 4000;
GO
INSERT INTO Employee (EmployeeID, Name, Position, Department, Address, AnnualSalary) 
SELECT 100, 'himanshu', 'dba', 'IT', 'BC', 4000;
GO
UPDATE Employee SET AnnualSalary = 5000 WHERE EmployeeID = 1;
GO
UPDATE Employee SET AnnualSalary = 4500 WHERE EmployeeID = 100;
GO
SELECT * FROM Employee;
SELECT * FROM EmployeeHistory;
GO
SELECT * FROM Employee 
FOR SYSTEM_TIME 
BETWEEN '2014-01-01 00:00:00.0000000' AND '2020-11-01 00:00:00.0000000';

Here is the result of the queries above: