Temporal tables - SQL Server

What is a temporal table?

Temporal tables give us the possibility to retrieve data from any point in the past and for every data change (Update, Delete, Merge). With temporal table users can recover data from accidental changes (update/delete) as well as audit all changes to data, etc. Temporary table is a new feature in SQL Server available from SQL Server 2016 CTP2 version. A Temporal table is composed of two parts (tables), the first part is the table on which is enabled data tracking and it’s called a System-Versioned table, this table contains current (actual) data, the second part is the table in which is stored any data changes that were made (Update, Delete, Merge) in a System-Versioned table and it’s called History table. $H:\ApexSQL\My articles\Temporal table\25.png$

What every temporal table must have?

datetime2 columns Every temporal table must have two columns with a datetime2 data type. These columns are used exclusively by the system to record the period of validity for each row whenever a row is changed. Both columns should be defined as NOT NULL. If there are no NOT NULL constraints for datetime2 columns, the system will automatically set them as NOT NULL. If NULL constraints are applied to these columns, then the system will raise an error:

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

One datetime2 column must have the GENERATED ALWAYS AS ROW START option set and the second datetime2 column must have the GENERATED ALWAYS AS ROW END option set. The GENERATED ALWAYS AS ROW START column represents the time when the row data became current, basically on an INSERT/UPDATE of the record in the system-versioned temporal table, the system will set current UTC time based on the system clock where the SQL Server instance runs. The GENERATED ALWAYS AS ROW END column represents the time when the row data was no longer current. This column indicates the time when the changes UPDATE/DELETE occur. The system will set the current UTC time. Primary key Also a system-versioned temporal table must have a primary key defined. If the table doesn’t have a defined the primary key, the following error will appear:

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

Attributes The table must have a PERIOD FOR SYSTEM_TIME (<startcol>,<endcol>) clause with two datetime2 columns in it. PERIOD FOR SYSTEM_TIME specifies the name of the columns that the system will use to record the period for which a record is valid:

StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)

The table must also have the SYSTEM_VERSIONING clause. The SYSTEM_VERSIONING is used to set system versioning ON/OFF for the system versioned table:

StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON);

The optional HISTORY_TABLE parameter sets the name of the history table:

StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

If name of the history table is not specified, SQL Server will automatically generate a history table name in a format like this:

MSSQL_TemporalHistoryFor_693577509

Where the number 693577509 is object_id for the system-versioned table. Here an example of a system-versioned table:

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

After executing this statement, the People temporal table will be created and it will be marked as a System-Versioned table. Inside the People table will be created the PeopleHistory table and it will be marked as History table: $H:\ApexSQL\My articles\Temporal table\1.png$

Creating a System-Versioned temporal table

Depending on the scenario, a system-versioned temporal table can be created by modifying an existing table from the database by adding temporal attributes or creating new system-versioned temporal table. There are three ways to create a system-versioned temporal table: Temporal table with an anonymous history table, creating a temporal table with a default history table or creating a temporal table with a user-defined history table. Temporal table with an anonymous history table Basically, when creating this type of temporal table, the only option that is omitted from the code that is shown above is a HISTORY_TABLE parameter:

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON);

Which means, when this code is executed, SQL Server will automatically generate the history table name and create a history table in the same schema as the current / temporal table. The name of the history table will be like this:

MSSQL_TemporalHistoryFor_613577224

Creating a temporal table with a default history table This approach allows the user to pick a much friendlier/meaningful name for history table in the HISTORY_TABLE parameter, rather than to let system to pick a name.

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

The schema (dbo) name is required for the HISTORY_TABLE parameter, otherwise the following error will appear:

Msg 13539, Level 15, State 1, Line 54 Setting SYSTEM_VERSIONING to ON failed because history table ‘PeopleHistory’ is not specified in two-part name format.

Creating a temporal table with a user-defined history table Creating user-defined history table is useful when in the history table needs additional options such as an index in order to obtain a better query performance. The history table cannot have a primary/foreign key, triggers, table constraints, unique index. Otherwise, the following error will be displayed:

Msg 13515, Level 16, State 1, Line 45 Setting SYSTEM_VERSIONING to ON failed because history table ‘test.dbo.PeopleHistory’ has custom unique keys defined. Consider dropping all unique keys and trying again.

The History and System-Versioned table must have the same number of the columns, the same column names and the same data types in order to a temporal table be created correctly. The below example demonstrates the effect of creating a history table with a different column name (NickName2) than the column name (NickName) in the versioned table. Copy the following code in the query editor and execute:

CREATE TABLE PeopleHistory(
PeopleID int NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName2 varchar(25),
StartTime datetime2 NOT NULL,
EndTime2 datetime2 NOT NULL
)
CREATETABLE dbo.People(
PeopleID int PRIMARYKEYNOTNULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS ASROWSTARTNOTNULL,
EndTime datetime2 GENERATED ALWAYS ASROWENDNOTNULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

The following error will be raised:

Msg 13524, Level 16, State 1, Line 45 Setting SYSTEM_VERSIONING to ON failed because column ‘NickName2’ at ordinal 4 in history table ‘test.dbo.PeopleHistory’ has a different name than the column ‘NickName’ at the same ordinal in table ‘test.dbo.People’.

The code below will successfully create system versioned and user-defined history table:

CREATE TABLE PeopleHistory(
PeopleID int NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 NOT NULL,
EndTime2 datetime2 NOT NULL
)
CREATETABLE dbo.People(
PeopleID int PRIMARYKEYNOTNULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25),
StartTime datetime2 GENERATED ALWAYS ASROWSTARTNOTNULL,
EndTime datetime2 GENERATED ALWAYS ASROWENDNOTNULL,
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
)
WITH (SYSTEM_VERSIONING = ON(HISTORY_TABLE = dbo.PeopleHistory));

Alter a Non-Temporal Table to be a System-Versioned Temporal Table In order to convert an existing table, create a temporal table, system versioning must be enabled. Let’s create a table and insert some data:

CREATE TABLE People(
PeopleID int PRIMARY KEY NOT NULL,
Name varchar(50) Null,
LastName varchar(100) NULL,
NickName varchar(25)
)
INSERTINTO dbo.People
(
PeopleID,
Name,
LastName,
NickName
)
VALUES
(
1, — PeopleID – int‘Marko’, — Name – varchar‘Zivkovic’, — LastName – varchar‘Ziva’— NickName – varchar
)

Now try to enable system versioning:

ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = ON)

The following error will appear:

Msg 13510, Level 16, State 1, Line 1 Cannot set SYSTEM_VERSIONING to ON when SYSTEM_TIME period is not defined.

As mentioned earlier, each temporal table must have two columns with datetime2 data type, otherwise the error above will be displayed. Now lets try to execute the below statement with two datetime2 columns:

ALTER TABLE dbo.People
ADD 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)

The result will be:

Msg 4901, Level 16, State 1, Line 4 ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column ‘StartTime’ cannot be added to non-empty table ‘People’ because it does not satisfy these conditions.

This error occurs, because the system will not allow to add NOT NULL columns in the table which already have data in it. To correct this, let’s add a default value for the datetime2 columns and enable system versioning with default name for the history table:

ALTER TABLE dbo.People
ADD StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT GETUTCDATE(),
EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT CONVERT(DATETIME2, ‘9999-12-31 23:59:59.9999999’),
PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
Go
ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE=dbo.PeopleHistory))
GO

Now, when execute the statement above the table People will become temporal table: $H:\ApexSQL\My articles\Temporal table\3.png$ The HIDDEN flag will hide these datetime2 columns from appearing in the result of (SELECT *) query:

SELECT * FROM dbo.People

$H:\ApexSQL\My articles\Temporal table\10.png$ To see datetime2 columns must be included in the SELECT statement list explicitly:

SELECT Name, LastName, NickName, StartTime, EndTime FROM dbo.People

$H:\ApexSQL\My articles\Temporal table\14.png$ The INSERT INTO statement will work without passing values for these columns. The below INSERT statement will execute successfully even though the PERIOD (StartTime,EndTime) columns are omitted from the INSERT statement list

INSERT INTO dbo.People
VALUES
(2, ‘Alan’, ‘Beker’, ‘Al’)

Inserting data There are several ways to insert data into a temporal table when the PERIOD columns are not HIDDEN.

Insert data when the PERIOD columns are omitted from the INSERT statement column list:

INSERT dbo.People (PeopleID, Name, LastName, NickName) VALUES (2, ‘Alan’, ‘Beker’, ‘Al’)

This statement will execute successfully because the system will automatically generate values for the PERIOD columns.
Insert data when the PERIOD columns are in the INSERT statement column list:

INSERT dbo.People (PeopleID, Name, LastName, NickName, StartTime, EndTime) VALUES (2, ‘Alan’, ‘Beker’, ‘Al’, DEFAULT, DEFAULT)

If the PERIOD columns in the INSERT column list, then need to be specified DEFAULT for their value.
Insert data when the INSERT column list is not specified

INSERT dbo.People VALUES (3, ‘Joe’, ‘Anderson’, ‘Jo’, DEFAULT, DEFAULT)

The default value must be specified in the VALUES list for the PERIOD columns, if not specified values for the PERIOD columns the following error will appear: Code:

INSERT dbo.People
VALUES (4, ‘Mike’, ‘Carter’, ‘Mik’)

Result:

Msg 213, Level 16, State 1, Line 16 Column name or number of supplied values does not match table definition.

When inserting data into a temporal table the system versioned (current) table will be affected, the history table will be empty: $H:\ApexSQL\My articles\Temporal table\17.png$ System versioned table:

SELECT p.* FROM dbo.People p

$H:\ApexSQL\My articles\Temporal table\13.png$ History table:

SELECT ph.* FROM dbo.PeopleHistory ph

$H:\ApexSQL\My articles\Temporal table\5.png$ The values in the StartTime column of the system versioned table is the time when the INSERT statement is executed and value in the EndTime will be the maximum datetime2 which is 9999-12-31 23:59:59.9999999 Updating data Let’s execute the below statement, which will update the NickName from Ziva to Mare:

UPDATE dbo.People
SET dbo.People.NickName = ‘Mare’
WHERE dbo.People.PeopleID = 1

Now, retrieve data from temporal and history table:

–Get the records from the temporal table
SELECT p.* FROM dbo.People p
–Get the records from the history table
SELECT ph.* FROM dbo.PeopleHistory ph

$H:\ApexSQL\My articles\Temporal table\15.png$ The temporal (current) table is updated and in the StartTime column is set the time when the update statement is executed. The system will automatically insert the record in the history table with the data that were before the update and in the EndTime column of the history table will be inserted the time when the update statement is executed. Let’s try to update the history table PeopleHistory and set NickName from Ziva to Maruska:

UPDATE dbo.PeopleHistory
SET
dbo.PeopleHistory.NickName = ‘Maruska’
WHERE dbo.PeopleHistory.PeopleID = 1

This will return an error:

Msg 13561, Level 16, State 1, Line 54 Cannot update rows in a temporal history table ‘test.dbo.PeopleHistory’.

The only way to update a history (PeopleHistory) table is to set SYSTEM_VERSIONING to OFF in the system versioned (People) table:

ALTER TABLE dbo.People
SET (SYSTEM_VERSIONING = OFF)

Since the SYSTEM_VERSIONING is set to OFF the link between system versioned (People) and history (PeopleHistory) tables is gone and both tables will become like any other regular tables: $H:\ApexSQL\My articles\Temporal table\8.png$ Now let’s try to update StartTime column in the People table:

UPDATE dbo.People
SET
StartTime=GETUTCDATE()
WHERE dbo.People.PeopleID = 2

The following error will appear:

Msg 13537, Level 16, State 1, Line 54 Cannot update GENERATED ALWAYS columns in table ‘test.dbo.People’.

In order to be available to update PERIOD columns the following statement must be executed:

ALTER TABLE dbo.People
DROP PERIOD FOR SYSTEM_TIME
GO

Now execute the UPDATE statement:

UPDATE dbo.People
SET
StartTime=GETUTCDATE()
WHERE dbo.People.PeopleID = 2

The following message will appear:

(1 row(s) affected)

Deleting data Let’s delete the record from the People table where the PeopleID is 1

DELETE FROM dbo.People WHERE PeopleID = 1

This code will delete the record from the temporal table (People) and insert the deleted record in the history table (PeopleHistory): Retrieve data from the temporal and history table:

–Get the records from the temporal table
SELECT p.* FROM dbo.People p
–Get the records from the history table
SELECT ph.* FROM dbo.PeopleHistory ph

$H:\ApexSQL\My articles\Temporal table\16.png$ From the image above, it’s clear that the record was deleted from table People and inserted in the history table PeopleHistory with EndTime of the record as the DELETE statement execution time. If executing the delete statement on the history table:

DELETE FROM dbo.PeopleHistory WHERE PeopleID = 1

Result will be:

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

This is happening because the update/delete operations are not allowed on the history table as long as SYSTEM_VERSIONING is enabled. To delete history and system versioned table, execute the code below:

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

Author
Recent Posts

Marko Zivkovic

Marko aka “Zivko” is a senior software analyst from Nis, Serbia focusing on SQL Server and MySQL as well as client technologies like SSMS, Visual Studio, and VSCode. He has extensive experience with quality assurance, issue escalation/resolution, and product evangelism.

He is a prolific author of authoritative content related to SQL Server including a number of “platinum” articles (top 1% in terms of popularity and engagement). His writing covers a range of topics on MySQL and SQL Server including remote/linked servers, import/export, LocalDB, SSMS, and more.

In his part-time, Zivko likes basketball, foosball (table-soccer), and rock music.

See more about Marko at LinkedIn

View all posts by Marko Zivkovic

Latest posts by Marko Zivkovic (see all)

How to connect to a remote MySQL server using SSL on Ubuntu - April 28, 2020
How to install MySQL on Ubuntu - March 10, 2020
Using SSH keys to connect to a remote MySQL Server - November 28, 2019

SQLShack

Temporal tables – SQL Server

What is a temporal table?

What every temporal table must have?

Creating a System-Versioned temporal table

What is a temporal table?

What every temporal table must have?

Creating a System-Versioned temporal table

Related posts: