Introduction to System-versioned temporal tables – New database feature of SQL Server 2016

System-versioned temporal tables is new database feature of SQL Server 2016. Another improved version of table level auditing with some new features after trigger, CT & CDC. System-versioned temporal tables stores data of table data modification history. Temporal is a database feature that was introduced in ANSI SQL 2011.

Most common usage for temporal tables are:

Data Auditing
Repairing or recovering record level corruptions
Recovering from missing records
Trend Analysis

How System-versioned temporal tables works? –

System-versioned temporal table contains actual data and corresponding History table contains old data. When you perform any DML operation on table, Old version of data got moved to history table.

How to create System-versioned temporal tables? –

We have 3 possible ways to System-versioned temporal tables.

1. Creating a temporal table with an anonymous history table

2. Creating a temporal table with a default history table

3. Creating a temporal table with a user-defined history table

How to stop System-versioning and drop actual & history table? –

To stop system versioning and drop actual data and history table, we need to off system versioning on table before drop table execution.

USE <DB_Name>

ALTER TABLE [dbo].<Table_Name> SET ( SYSTEM_VERSIONING = OFF )

DROP TABLE [dbo].<Table_Name>

DROP TABLE [dbo].<Table_Name_History_Table>

How to query System-versioned temporal tables? –

You can perform normal select statement on both actual and history table like other normal tables. But System-versioned temporal tables got new clause FOR SYSTEM_TIME with five temporal-specific sub-clauses to query data across the current and history tables. This new SELECT statement syntax is supported directly on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.

Select * from Department_UserDefined

Select * from DepartmentHistory_UserDefined

select * from Department_UserDefined FOR SYSTEM_TIME AS OF ‘2018-01-21 09:34:44.4731356’

5 Clauses FOR SYSTEM_TIME:-

Expression	Qualifying Rows	Description
AS OF<date_time>	SysStartTime <= date_time AND SysEndTime > date_time	Returns a table with a rows containing the values that were actual (current) at the specified point in time in the past. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values in the row that was valid at the point in time specified by the <date_time> parameter.
FROM<start_date_time>TO<end_date_time>	SysStartTime < end_date_time AND SysEndTime > start_date_time	Returns a table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <start_date_time> parameter value for the FROM argument or ceased being active after the <end_date_time>parameter value for the TO argument. Internally, a union is performed between the temporal table and its history table and the results are filtered to return the values for all row versions that were active at any time during the time range specified. Rows that ceased being active exactly on the lower boundary defined by the FROM endpoint are not included and records that became active exactly on the upper boundary defined by the TO endpoint are not included also.
BETWEEN<start_date_time>AND<end_date_time>	SysStartTime <= end_date_time AND SysEndTime > start_date_time	Same as above in the FOR SYSTEM_TIME FROM <start_date_time>TO<end_date_time> description, except the table of rows returned includes rows that became active on the upper boundary defined by the <end_date_time> endpoint.
CONTAINED IN (<start_date_time> , <end_date_time>)	SysStartTime >= start_date_time AND SysEndTime <= end_date_time	Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.
ALL	All rows	Returns the union of rows that belong to the current and the history table.

How to check table type? –

SYS.TABLES system view got new columns to check table is SYSTEM_VERSIONED_TEMPORAL_TABLE or HISTORY_TABLE or NON_TEMPORAL_TABLE. You can check the list of all SYSTEM_VERSIONED_TEMPORAL_TABLE & HISTORY_TABLE from SYS.Tables.