SQL Server 2016 Features – Temporal Tables

SQL Server 2016 introduces support for 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 tables are also called as System-Versioned tables and used to record all data changes.In this scenario, SQL Server creates 2 tables, one to store the current data and one to store historical data.Two datetime2 columns are added to both the current and historical tables where SQL Server stores the valid date range of the data: SysStartTime and SysEndTime. The current row will have a SysEndTime value of 9999-12-31, and all records inserted within a single transaction will have the same UTC time.
Temporal table considerations and Limitations
Use cases for temporal tables include:

• Auditing all data changes and performing data forensics when necessary
  
• Reconstructing state of the data as of any time in the past
  
• Calculating trends over time
  
• Maintaining a slowly changing dimension for decision support applications
  
• Recovering from accidental data changes and application errors

Create Temporal Data

CREATE TABLE dbo.Employee 
(  
     EmployeeID int NOT NULL PRIMARY KEY CLUSTERED, 
     ManagerID int NULL, 
     FirstName varchar(50) NOT NULL,
     LastName varchar(50) NOT NULL,
     SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
     SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL, 
     PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)   
)  
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

Query Temporal Data
Use the FOR SYSTEM_TIME clause with one of the following four sub-clauses to return the data for a specific date or within a date range:

• AS OF <date_time>
• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time>, <end_date_time>

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME AS OF '2015-12-21 09:00:00'
WHERE ManagerID = 3

SELECT * 
FROM dbo.Employee
FOR SYSTEM_TIME FROM '2015-07-01' TO '2015-12-31' 
ORDER BY EmployeeID, SysStartTime

Technorati Tags: SQL 2016,Temporal Tables