How to work with Temporal Tables in SQL Server

SQL Server 2016 introduced support for temporal tables (also known as system-versioned temporal tables) as a database feature that brings built-in support for providing information about data stored in the table at any point in time rather than only the data that is correct at the current moment in time. Temporal is a database feature that was introduced in ANSI SQL 2011.

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 table with respective history table.

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

Create table with 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));

Above script will create two tables Employee & EmployeeHistory

Some Insert update in above tables

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 above queries