As per MSDN:
"What is a system-versioned temporal table?
A system-versioned temporal table is a new type of user table in SQL Server 2016, designed to keep a full history of data changes and allow easy point in time analysis. This type of temporal table is referred to as a system-versioned temporal table because the period of validity for each row is managed by the system (i.e. database engine).
Every temporal table has two explicitly defined columns, each with a datetime2 data type. These columns are referred to as period columns. These period columns are used exclusively by the system to record period of validity for each row whenever a row is modified.
In addition to these period columns, a temporal table also contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table. During temporal table creation users can specify existing history table (must be schema compliant) or let system create default history table."
Highlights:
- Defined in ANSI SQL:2011 Specification
- SQL Sever 2016 implements it as System Versioned Temporal Tables
- Add a History Table to a User Table
- Old rows are moved to History Table as data changes
- Automatically tracks data changes
- Easy querying of historical data states with a new T-SQL syntax. Can even bridge the Main table & History table.
- Use cases for Auditing, Data Forensics, Time Travel, Recovering accidental data changes
- You can create a new Temporal table or alter existing to add as Temporal
- Requires no changes to your Applications
Example:
Create a Temporal Table:
Create Table dbo.Individuals
(
IndividualID INT NOT NULL PRIMARY KEY CLUSTERED,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100),
SysStart datetime2 GENERATED ALWAYS AS ROW START,
SysEnd datetime2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStart, SysEnd)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.IndividualsHistory));
*You can chose different names for SysStart & SysEnd
Execute DML statements:
Insert into dbo.Individuals (IndividualID, FirstName, LastName, City) Select 1, 'Bill', 'Gates', 'Seattle'
Update dbo.Individuals set City = 'San Jose' where IndividualID = 1
Update dbo.Individuals set City = 'San Fransisco' where IndividualID = 1
Update dbo.Individuals set City = 'Los Angeles' where IndividualID = 1
Select from the Main/Current Table, History Table and both tables:
Main/Current Table:
Select IndividualID, FirstName, LastName, City, DATEADD(HH,-5,SysStart) as 'SysStart EST', DATEADD(HH,-5,SysEnd) as 'SysEnd EST', SysStart, SysEnd from dbo.Individuals
History Table:
Select IndividualID, FirstName, LastName, City, DATEADD(HH,-5,SysStart) as 'SysStart EST', DATEADD(HH,-5,SysEnd) as 'SysEnd EST', SysStart, SysEnd from dbo.IndividualsHistory
Both Tables:
SELECT * FROM dbo.Individuals FOR SYSTEM_TIME All order by SysStart ASC
Query Results:
SSMS:
Notes:
To view all temporal tables: Select Name, temporal_type, temporal_type_desc from sys.tables where temporal_type > 0
To truncate or drop a Temporal table you have to Alter table and set system_versioning = OFF. *When turning system_versioning back ON
again make sure to include the History table name in that statement in order to link those tables up as before.
Resources:
Temporal Tables
|
|
|
|
|
|