SQL Server Temporal Tables

One of the most important new features of ANSI SQL 2011 (ISO/IEC 9075:2011) was defining language enchantments for SQL temporal functionality. Support for SQL Server came with the 2016 version as a "system-versioned temporal tables."

The basic principle behind temporal tables is to bring support out of the box for getting data from any point in time from the table, not just the current version.

By using temporal tables, developers can quickly implement Soft deletes without any flags or having to rewrite 'delete commands' with updates. Soft updates that are harder to implement come easy as well. It is possible to search through each row's entire history per id (primary key) or get data for inputted DateTime.

SQL Server implementation is straightforward and consists of two tables, primary that holds the current data, and the secondary table with historical states. The only modification to the main table is the addition of two explicitly defined columns with datatime2 type - SysStartTime and SysEndTime. The secondary table is mirrored schema, and the table name can be auto-set or manually chosen.

Temporal tables create a copy of the current data before updating or deleting it. Every historical point has as timestamps, so it is possible to query historical data with new temporal SQL commands. However, standard SQL queries will continue to work with the current state of data. Insert commands do not modify the data in the history table.

It is possible to query the data with temporal SQL commands or use traditional SQL with a read-only history table and generate any report necessary.

Uses

Some of the more common uses are:

Recovering from unintended data changes or mistakes
"Time Travel" (reconstructing states of data)
Forensics
Various analysis of trends

Temporal SQL Commands

---
	AS OF \<date_time>		Returns values that were current at that time
	FROM \<start_date_time> TO \<end_date_time>		Returns multiple rows that were active in that period, without end/start dates
	BETWEEN \<start_date_time> AND \<end_date_time>		Returns multiple rows that were active in that period, with end/start dates
	CONTAINED IN (\<start_date_time> , \<end_date_time>)		Returns multiple rows of data that was changed in that period, with end/start dates
	ALL		Returns current and historical data (union)

Example

Creating Temporal Tables

Creating a Temporal table without specifying the history table name.

CREATE TABLE dbo.Entity
(
  [Id] int NOT NULL PRIMARY KEY CLUSTERED
  , [Name] nvarchar(100) NOT NULL
  , [Value] nvarchar(100) NOT NULL
  , [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START
  , [ValidTo] datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )
WITH (SYSTEM_VERSIONING = ON);

Current Data

SELECT [Id]
      ,[Name]
      ,[Value]
      ,[ValidFrom]
      ,[ValidTo]
  FROM [TemporalDataBaseTemp].[dbo].[Entity]

---
	2		Red		250		2020-10-13 04:18:39.7184059		9999-12-31 23:59:59.9999999
	3		Green		400		2020-10-13 04:18:47.7949314		9999-12-31 23:59:59.9999999

Historical Data Values

SELECT [Id]
      ,[Name]
      ,[Value]
      ,[ValidFrom]
      ,[ValidTo]
  FROM [TemporalDataBaseTemp].[dbo].[MSSQL_TemporalHistoryFor_901578250]


2	Red	200	2020-10-13 04:18:31.3173873	2020-10-13 04:18:39.7184059
3	Green	300	2020-10-13 04:18:37.0598939	2020-10-13 04:18:44.9957158
3	Green	350	2020-10-13 04:18:44.9957158	2020-10-13 04:18:47.7949314
1	Blue	100	2020-10-13 04:18:26.5023901	2020-10-13 04:28:46.4464257

ALL

SELECT [Id]
      ,[Name]
      ,[Value]
      ,[ValidFrom]
      ,[ValidTo]
  FROM [TemporalDataBaseTemp].[dbo].[Entity]
  FOR SYSTEM_TIME ALL
  ORDER BY [Id], [ValidTo]


1	Blue	100	2020-10-13 04:18:26.5023901	2020-10-13 04:28:46.4464257
2	Red	200	2020-10-13 04:18:31.3173873	2020-10-13 04:18:39.7184059
2	Red	250	2020-10-13 04:18:39.7184059	9999-12-31 23:59:59.9999999
3	Green	300	2020-10-13 04:18:37.0598939	2020-10-13 04:18:44.9957158
3	Green	350	2020-10-13 04:18:44.9957158	2020-10-13 04:18:47.7949314
3	Green	400	2020-10-13 04:18:47.7949314	9999-12-31 23:59:59.9999999

AS OF

Data before deleting 'Blue'.

SELECT [Id]
      ,[Name]
      ,[Value]
      ,[ValidFrom]
      ,[ValidTo]
  FROM [TemporalDataBaseTemp].[dbo].[Entity]
  FOR SYSTEM_TIME AS OF '2020-10-13 04:27:46.4464257'


2	Red	250	2020-10-13 04:18:39.7184059	9999-12-31 23:59:59.9999999
3	Green	400	2020-10-13 04:18:47.7949314	9999-12-31 23:59:59.9999999
1	Blue	100	2020-10-13 04:18:26.5023901	2020-10-13 04:28:46.4464257

Temporal Tables Types

There are a few options when creating Temporal Tables. It is possible to create
a temporal table with:

an anonymous history table - Define just the primary table schema; SQL Server creates a history table with an auto-generated name
a default history table - Define the primary table schema and History table name; SQL Server creates a history table with that Name
a user-defined history table - Define the primary table schema and a History table schema, and connect during creation. Usable if you already have some custom made history table or you need finer control.

Removing Temporal Tables

It is possible to remove the recording of the historical data temporarily by setting off permanently. By setting System Versioning to Off, no data loss will happen to the temporal table. However, it will be possible to delete the table and modify schema or data because it would be just a regular table. By turning back System Versioning to On (with manually selecting already used history table), it is possible to have changed history (or modified schema). A history table with modified data would become part of the primary temporal table.

Support for Temporal Tables - Entity Framework

At the moment, the Entity framework Does not support all features of temporal tables. There are several 3rd party addons, but official Microsoft support is lacking.