SQL Server 2016: the Time Travel with temporal table – Part I

Principle & Creation

Microsoft create in SQL server 2016 a new feature temporal table, based on the latest edition of the SQL standard: SQL:2011.
If you want read more about this standard, click here.
I divide my article into 4 parts to be clearer and easier to read:
Part I – Principle & Creation
Part II – INSERT, UPDATE & DELETE commands
Part III – SELECT command
Part IV – Maintenance & Metadata

To begin, I will just precise a mistake between SQL server and Oracle. I read and heard that Temporal Tables is like the Flashback Technology from Oracle, but in reality it is the same as the new feature in the Oracle 12c Release 1: Temporal Validity. More information here
Flashback is based on undo or flashback log Files and not directly the table.

SQL Server version/built used for this serie of articles is CTP 2.2/13.0.407.1

Why temporal?

It is likely to come back in time to see the database how it “looks like” at this moment.
Another interesting point is for DML Audit (INSERT, UPDATE, DELETE or MERGE), because we have a trace for all data changes.
Another advantage is the possibility to recover data, if unintentional data changes happened (human or application errors).

How its work?

When you create a temporal table or you transform (Alter) a “normal” table to a temporal table, 2 tables are implemented:

• Current Table with the current value for each records called System-Versioned Table
• History table with all previous values for each records called History Table

The principle is very simple:  add a Start and an End Time columns to have a validity period for each record.

Let’s go to the practice.
In my sample, I create a ‘Zoo’ database with a table ‘Animals’ which inventory animals.
I create my table ‘Animals’ for the inventory with this new feature:

USE Zoo;

CREATE TABLE [dbo].[Animals]
(
 [AnimalId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](200) NOT NULL,
 [Genus Species] [varchar](200) NOT NULL,
 [Number]  [int] NOT NULL,

 CONSTRAINT [PK_Animals] PRIMARY KEY CLUSTERED ([AnimalId] ASC),

 /*Temporal: Define the Period*/
  [StartDate] [datetime2](0) GENERATED ALWAYS AS ROW START NOT NULL,
  [EndDate]  [datetime2](0) GENERATED ALWAYS AS ROW END NOT NULL,
 PERIOD FOR SYSTEM_TIME([StartDate],[EndDate])
) 
 WITH (SYSTEM_VERSIONING=ON(HISTORY_TABLE = [dbo].[AnimalsHistory]))

Run the query and you can see when you refresh Tables folder in SSMS, a “(System-Versioned)” text near your table name.

If you want to change an existing table, you need to define a default value for the Start Date and the End Date like this:

ALTER TABLE [dbo].[Animals]
 ADD StartDate datetime2(0) GENERATED ALWAYS AS ROW START CONSTRAINT P_StartDateConstraint DEFAULT '2000.01.01',
 EndDate  datetime2(0) GENERATED ALWAYS AS ROW END CONSTRAINT P_EndDateConstraint DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59'),
 PERIOD FOR SYSTEM_TIME(StartDate,EndDate);

In SSMS, I expand my Table and I can see a new table dbo.AnimlasHistory with the symbol

In my table, I define the history table with the option:

WITH (SYSTEM_VERSIONING=ON(HISTORY_TABLE = [dbo].[AnimalsHistory]))

You can notice, this table creation add a Clustered index ix_AnimalsHistory.

This index is created with the Primary Key (AnimalId), the Start Date and the End Date.

CORE MESSAGE

• It is easy to create a Temporal Table, just add a Start Date and the End Date with a DateTime2 data type.
• The option “WITH (SYSTEM_VERSIONING=ON(HISTORY_TABLE = <History Table Name>))” validates the usage of the temporal table and specifies the name of the history table.
• In SQL Server Management Studio (SSMS), a temporal table have (System-Versioned) near its name.
• The history table is just below the table in the object explorer tree with the symbol and has (History) near its name.
• The history table has automatically a clustered index created during the temporal table creation.