System Versioned Temporal Tables in SQL Server 2016

A system versioned temporal table keeps full history of changes and allows point in time analysis. 

For such tables SQL Server creates & maintains a history table. When a row is modified or deleted, it is time stamped & moved automatically to history table first.

Creating Temporal Table

CREATE TABLE dbo.Customer

(

-- Primary Key is Required for Temporal Tables.

ID int NOT NULL PRIMARY KEY CLUSTERED,

Name varchar(50) NOT NULL,

MaritalStatus varchar(20),

-- Define Period Columns

StartEffectiveDate datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

EndEffectiveDate datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

-- Designate Period Columns

PERIOD FOR SYSTEM_TIME (StartEffectiveDate ,EndEffectiveDate)

)

WITH

(SYSTEM_VERSIONING = ON -- Mark table as System Versioned.

    (HISTORY_TABLE = dbo.Customer_History) -- Name of History Table.

);

GO

-- Insert Data into temporal table.

INSERT INTO dbo.Customer values ( 1,'John', 'Single'), (2,'Jack', 'Married')

-- Query Temporal Table

select * from dbo.Customer

ID	Name	MaritalStatus
1	John	Single
2	Jack	Married

-- Query History Table

select * from dbo.Customer_History

(0 row(s) affected)

Updating Data

update dbo.Customer

Set MaritalStatus = 'Married' where ID = 1

-- Query Actual Table

select * from dbo.Customer

ID	Name	MaritalStatus
1	John	Married
2	Jack	Married

-- Query History Table (Times are in UTC)

select * from dbo.Customer_History

ID	Name	MaritalStatus	StartEffectiveDate	EndEffectiveDate
1	John	Single	11/9/2015  9:09:56 PM	11/9/2015  9:11:54 PM

Deleting Data

delete from dbo.Customer where ID = 2

-- Query Temporal Table

select * from dbo.Customer

ID	Name	MaritalStatus
1	John	Married

-- Query History Table

select * from dbo.Customer_History

ID	Name	MaritalStatus	StartEffectiveDate	EndEffectiveDate
1	John	Single	11/9/2015  9:09:56 PM	11/9/2015  9:11:54 PM
2	Jack	Married	11/9/2015  9:09:56 PM	11/9/2015  9:14:54 PM

Point in Time Analysis

SQL Server introduced special clauses to allow querying for point in time analysis.

FOR SYSTEM_TIME AS OF <DateTime2>  gives state of table as of the specified time. (Excluding the boundary value)

select * from dbo.Customer FOR SYSTEM_TIME AS OF '2015-11-09 20:52:00'

ID	Name	MaritalStatus
1	John	Single
2	Jack	Married

Dropping Temporal Table

ALTER TABLE [dbo].[Customer] SET ( SYSTEM_VERSIONING = OFF )

DROP Table dbo.Customer

DROP TABLE dbo.Customer_History