Beginning with SQL Server 2016, Microsoft has introduced system-versioning database features. This built-in capability helps in providing information about the data stored at any point of time. This capability, called “Temporal” system versioning, is a database feature that was introduced in ANSI SQL 2011 and is now supported in SQL Server 2017. This is also available in Azure SQL Databases.

What is a Temporal Table

• A type of user table
• A system-versioned table
• Keeps full history of data changes
• Allows easy point in time analysis
• Built-in versioning capability as the period of validity for each row is managed by the database engine

Where can Temporal Tables be used

Temporal tables have a wide variety of uses and can help with:
1. Complete data auditing and data forensics
2. Time Travel data reconstruction: Point in Time Analysis (PITA) and Point in Time Recovery (PITR)
3. Repairing Row-Level Data Corruption and recovery from accidental changes and errors
4. Maintaining slowly-Changing Dimensions for decision support applications
5. Anomaly Detection and calculating trends

Structure of a Temporal table

Irrespective of the use, setting up and querying a Temporal Table is very easy. While there is no shortcut like a check-box or radio button to enable or disable this feature, there is an ON and OFF feature that can be used for a table, combined with a couple of pre-requisite columns and constraints.

• SYSTEM_VERSIONING = ON
• SYSTEM_VERSIONING = OFF

1. A temporal table has two explicitly defined columns:
• Period start column: DATETIME2
• Period end column: DATETIME2

These 2 columns are also referred to as period columns and are used by the system to record period of validity for each data row. Also, sometimes it is easier, and considered best practice to add default constraints to these 2 columns:

• Period start column: DEFAULT (DATEADD (SECOND, (-1),SYSUTCDATETIME()))
• Period end column: DEFAULT (‘9999.12.31 23:59:59.99’)

2. A temporal table also contains a reference to another table:

This table has a schema that is mirrored in the temporal table.
Basically, the main table is the current or temporal table. The other table is a history table, created by mirroring the schema of the main/ reference table. The system uses this history table to automatically store the previous version of each row, whenever there is an update or delete in the temporal table.

How to create a Temporal Table In SQL Server 2016?

During temporal table creation users can also specify the option for the corresponding history table:
1. Default history table
2. History table with specified name
3. Existing history table (schema compliant)

Syntax to create a Temporal Table with default/ Anonymous History Table

Here is the table created with default history table, default constraints and default index:

Syntax to create a Temporal Table and History Table with specified name

Here is the table created with specified history table, specified constraints and index names:

Note that on querying, the HIDDEN columns are not displayed in the results:

Syntax to create a Temporal Table and History Table separately, with mirrored schema

Note that both tables have the same schema/ columns with the in the results:

How to set up system-versioning on an existing table

Let us assume we have a table called Temporal with the following structure:

To enable system versioning, we use an alter table statement:

However, this gives us an error: Msg 13510, Level 16, State 1, Line 1
Cannot set SYSTEM_VERSIONING to ON when the SYSTEM_TIME period is not defined.

To set up SYSTEM_TIME period, we also need the 2 extra columns that are explicitly required along with the default NOT NULL constraints:

Once the columns have been added, we can set up system versioning on this table. To do so, we can choose to mention the name of the history table or opt for a default history table:

In a nutshell

Temporal tables give us an effortless way to capture all the changes that are made to rows in a table. This could have also been done in older SQL Server versions by defining a history table and trigger manually, but this would have involved much more complexity. Instead, this functionality has been built directly into SQL Server as a feature now.

Note: Temporal system versioning is not supported on Azure SQL Data Warehouse currently.