This script contains the examples demonstrating the usage of Temporal Tables in SQL Server 2016.

Temporal Tables - SQL Server 2016.sql

-- Creating Temporal table with automatically named History table
CREATE TABLE dbo.Employee
(
    EmployeeID        int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
    EmployeeName      varchar(50) NOT NULL, 
    Age               int NULL, 
	Email             nvarchar(50),
 
    ValidFrom           datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    ValidTo             datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,   
 
    PERIOD FOR SYSTEM_TIME (
        ValidFrom, 
        ValidTo
    )   
)
WITH ( SYSTEM_VERSIONING = ON ); 
GO

---------------------------------------------------------------------------

SELECT * FROM dbo.Employee

---------------------------------------------------------------------------

-- To drop the temporal table, the System Versioning needs to be turned off
ALTER TABLE [dbo].[Employee] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[Employee]
GO
DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_441768631]
GO

--------------------------------------------------------------------------

-- Creating Temporal table with a named History table
CREATE TABLE dbo.Employee
(
    EmployeeID        int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
    EmployeeName      varchar(50) NOT NULL, 
    Age               int NULL, 
	Email             nvarchar(50),
 
    ValidFrom           datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    ValidTo             datetime2 GENERATED ALWAYS AS ROW END   NOT NULL,   
 
    PERIOD FOR SYSTEM_TIME (
        ValidFrom, 
        ValidTo
    )   
)
WITH ( SYSTEM_VERSIONING = ON  (HISTORY_TABLE = dbo.EmployeeHistory)); -- Named History table
GO

----------------------------------------------------------------------------

-- Insert data into Temporal table
INSERT INTO dbo.Employee
(EmployeeName,  Age, Email)
VALUES
('Samir', 30, N'samirbehara@gmail.com'),
('Dennis', 40, 'dennisbaker@hotmail.com'),
('Robert', 35, 'robert.tedrow@yahoo.com')

SELECT * FROM dbo.Employee
SELECT * FROM dbo.EmployeeHistory

----------------------------------------------------------------------------

-- Modify the Base table and look at the updates to the History table
DELETE FROM dbo.Employee
WHERE EmployeeID = 3

UPDATE dbo.Employee
SET Age = 38
WHERE EmployeeID = 2

SELECT * FROM dbo.Employee
SELECT * FROM dbo.EmployeeHistory

----------------------------------------------------------------------------

-- Look at the current state of the table
SELECT * FROM dbo.Employee

-- Reconstruct state of data at a specific time using the AS OF clause
SELECT * FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2016-05-04 02:34:09.9419923'


------------------------------------------------------------------------------

SELECT * FROM dbo.Employee
WHERE EmployeeID = 2

-- Get all historical data for a specific row
SELECT * FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2016-05-04 02:34:09.9419923'
WHERE EmployeeID = 2


-------------------------------------------------------------------------------