Temporal Table is a new type of user defined table introduced in Sql Server 2016. Temporal table is like any other normal tables the main difference is for Temporal Tables Sql Server automatically tracks the full history of the data changes into a separate history table. Because of the tracking of the fully history of the data, it provides a mechanism to know the state of the data at any point in time.

With regular tables we can only know the current/latest state of the data, we will not be able to see the past state of the updated or deleted records. For regular tables if we need to keep track of the history developer need to create triggers and store the data in separate table there is no built in support for it. But with Temporal Tables Sql Server provides the built-in support for tracking the full history of the data and also provides the temporal querying construct FOR SYSTEM_TIME to query historic data at any point in time or for a period of time. Because of this built in support by the database engine to track the history of the data, Temporal Tables are referred as system-versioned temporal tables/system versioned tables.

• Below are the some of the common uses of the System-Versioned Temporal Tables
• It provides a mechanism to Audit the data changes as the complete history is maintained
• Recovering from accidental data changes. For instance if someone has wrongly deleted a record, because of the availability of the history data we can easily recover these deleted records.

Maintain a Slowly Changing Dimension (SCD) for decision support applications

This is the first article in the series of articles on the Temporal Tables, below is the complete list of articles on System-Versioned Temporal Tables. This article will cover how we can create a new Temporal Table and DML operations on the Temporal Table with extensive list of examples

• Part 1: Introduction to Temporal Tables and DML operations on the Temporal Table
• Part 2: Querying System-Versioned Temporal Table
• Part 3: Enabling System Versioning for an Existing Regular Table
• Part 4: DDL operations on the System-Versioned Temporal Table

Creating a Temporal Table

Below image explain the syntax of the Temporal Table creation

Following are the list of attributes/properties a table need to have to consider/make it as System Versioned Temporal Table:

• Table needs to have a Primary Key. If table doesn’t have the primary key then it raises the following error:

  Msg 13553, Level 16, State 1, Line 5
  System versioned temporal table ‘ SqlhintsTemporalDemoDB.dbo.Customer’ must have primary key defined.

• Table needs to have two DATETIME2 columns representing the start and end of the row’s validity period.
  • The row validity start time column is marked by the clause: GENERATED ALWAYS AS ROW START
  • The row validity end time column is marked by the clause: GENERATED ALWAYS AS ROW END

  Both these row validity start/end time columns should be NOT NULL, if NOT NULL constraint is not specified for these columns Sql Server will consider them as NOT NULL. If these columns are specified as NULL, then it will raise an error like the below:

  Msg 13587, Level 16, State 1, Line 5
  Period column ‘StartTime’ in a system-versioned temporal table cannot be nullable.

  We can mark these period columns as HIDDEN, if we mark them as HIDDEN then these columns will not appear in the result of query and also INSERT INTO statement without column list works without passing these column values in the values list. So this HIDDEN flag makes the enabling of the System Verionsed tables completely transparent to the users/application. It means the application will continue to work and behave functionally as it was previously without needing any changes in it.

• PERIOD FOR SYSTEM_TIME (StartTime, EndTime) clause specifies the names of the columns that the Sql Server engine will use to record the period for which a record is valid

• HISTORY_TABLE : This parameter is used to specify the Name of the History Table. It can be an existing table name whose schema matches with the current/Temporal table or it can be new table name which Sql Server will create.

  This parameter is optional, if it is not specified Sql Server generates history table name like: MSSQL_TemporalHistoryFor<primary_table_object_id>

• SYSTEM_VERSIONING : This argument is used to enable/disable system versioning on the table

Execute the below script to create a new demo database and TEMPORAL TABLE

--Create demo data base
CREATE DATABASE SqlhintsTemporalDemoDB
GO
USE SqlhintsTemporalDemoDB
GO
--Create Temporal Table dbo.Customer
CREATE TABLE dbo.Customer 
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START 
              HIDDEN NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END
              HIDDEN NOT NULL ,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING=ON (HISTORY_TABLE=dbo.CustomerHistory))

After executing the above statement, if we look into the SSMS window we can see that a dbo.Customer table created and it is marked as System-Versioned table. Inside this table we can see a nested table dbo.CustomerHistory table created and marked as History Table as shown in the below image:

DML operations on Temporal Table

Let us now understand one by one the DML INSERT, UPDDATE and DELETE operations on the Temporal Table.

INSERT Operation on Temporal Table

Let us execute the following statement to insert a record in the Temporal Table dbo.Customer by enabling the actual execution plan:

INSERT INTO dbo.Customer
VALUES (1,'Basavaraj Biradar')

RESULT

The above statement worked even though we have not specified StartTime and EndTime column values. It worked because we have marked these two columns as HIDDEN, otherwise it would have failed. If these columns were not specified as HIDDEN in that case we need to pass these two column values as DEFAULT in the values clause.

From the above results execution plan, we can see that the record is inserted only in the Temporal/Main table dbo.Customer only.

Let us execute the following statement to check the data in the dbo.Customer and dbo.CustomerHistory table

--Get the records from the temporal table
SELECT * FROM [dbo].[Customer]
--Get the records from the history table
SELECT * FROM [dbo].[CustomerHistory]

RESULT

From the above result we can say that INSERT operation will insert record only in the current (i.e. Temporal) table only, it will not insert the record in the history table.

Also we can observe that even though we have written our queries as SELECT *, still it did not return the period columns StartTime and EndTime of the Temporal Table as these columns are marked as HIDDEN. If we need to get these columns in the result we need to specify them explicitly in the SELECT statement as in the following statement.

--Get the records from the temporal table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[Customer]
--Get the records from the history table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[CustomerHistory]

RESULT

From the result we can see that Sql Server is recording the Period StartTime column with a datetime value which is present at the time of the execution of the statement and as this record is still open (i.e. still active) so the EndTime populated by the Sql Server is the DATETIME2 max value i.e. 9999-12-31 23:59:59.9999999.

UPDATE Operation on the Temporal Table

Let us execute the below update statement to update the name of the customer from Basavaraj Biradar to Basavaraj Prabhu Biradar by enabling the actual execution plan:

UPDATE dbo.Customer
SET Name = 'Basavaraj Prabhu Biradar'
WHERE Id = 1

RESULT

From the execution plan, we can see that first it is updating the record in the dbo.Customer table and then Sql server is inserting a record in the dbo.CustomerHistory table.

Let us execute the following statement to check the data in the dbo.Customer and dbo.CustomerHistory table

--Get the records from the temporal table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[Customer]
--Get the records from the history table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[CustomerHistory]

RESULT

From the result we can see that update of a record in the temporal table results in inserting the record in the History table with state of the record before update and endtime will be the time at which the update statement is executed. And the Temporal (i.e. current/Main) table will have the updated row with starttime as the time at which the update statement is executed and as this record is still active it will have endtime as DATETIME2 data types max value.

Let us try to update the PERIOD columns and observe the behavior:

UPDATE dbo.Customer
SET Name = 'Basavaraj Prabhu Biradar',
	StartTime = GETUTCDATE(),
	EndTime = GETUTCDATE()
WHERE Id = 1

RESULT

Msg 13537, Level 16, State 1, Line 1
Cannot update GENERATED ALWAYS columns in table ‘SqlhintsTemporalDemoDB.dbo.Customer’.

From the result it is clear that we can’t update the PERIOD column values when SYSTEM_VERSIONING is ON. TO do this we need to first disable the system versioning and then drop the PERIOD definition from the Customer table. Then these two tables will become like any other regular tables and we can perform any operations on it.

DELETE Operation on the Temporal Table

Let us execute the following statement to delete the record from the customer table by enabling the execution plan

DELETE FROM dbo.Customer WHERE Id = 1

RESULT

From the execution plan we can see that Sql Server is deleting the record from the Temporal Table i.e. dbo.Customer and Inserting the deleted record in the History table dbo.CustomerHistory.

Let us execute the following statement to check the data in the dbo.Customer and dbo.CustomerHistory table

--Get the records from the temporal table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[Customer]
--Get the records from the history table
SELECT Id, Name, StartTime, EndTime FROM [dbo].[CustomerHistory]

RESULT

From the results we can see that the record is deleted from the Temporal table and the state of the record before delete is stored in the History table with endtime of the record as the DELETE statement execution time.

TRUNCATE Operation on the Temporal table

Let us try executing the following truncate statement on the Temporal Table

TRUNCATE TABLE dbo.Customer

RESULT

Msg 13545, Level 16, State 1, Line 1
Truncate failed on table ‘SqlhintsTemporalDemoDB.dbo.Customer’ because it is not supported operation on system-versioned tables.

From the result it is clear that TRUNCATE operation is not supported on the TEMPORAL table

DML OPERATION on the HISTORY table

Let us try executing the following delete statement on the History Table

DELETE FROM dbo.CustomerHistory WHERE Id = 1

RESULT

Msg 13560, Level 16, State 1, Line 1
Cannot delete rows from a temporal history table ‘SqlhintsTemporalDemoDB.dbo.CustomerHistory’.

From the result it is clear that DML operation is not supported on the HISTORY table as long as the SYSTEM_VERSIONING is enabled

CLEAN-UP

Let us drop the Customer and CustomerHistory tables by executing the following script

--Disable the system versioning
ALTER TABLE dbo.Customer SET (SYSTEM_VERSIONING = OFF)
GO
--Drop Period definition
ALTER TABLE dbo.Customer
DROP PERIOD FOR SYSTEM_TIME
GO
--Now drop Customer and CustomerHistory tables
DROP TABLE dbo.Customer 
DROP TABLE dbo.CustomerHistory

[ALSO READ] New features in Sql Server 2016:

• Native JSON Support in Sql Server 2016
  • FOR JSON Clause in Sql Server 2016
  • OPENJSON Function in Sql Server 2016
  • ISJSON Function in Sql Server 2016
  • JSON_VALUE Function in Sql Server 2016
  • JSON_QUERY Function in Sql Server 2016
  • lax and strict JSON Path modes in Sql Server 2016
  • Indexing Strategy for JSON Value in Sql Server 2016
• DROP IF EXISTS Statement in Sql Server 2016
• Compare Execution Plans in Sql Server 2016
• Live Query Statistics in Sql Server 2016
• DATEDIFF_BIG Function in Sql Server 2016
• Difference between DATEDIFF and DATEDIFF_BIG functions in Sql Server
• SESSION_CONTEXT in Sql Server 2016