Marko Zivkovic

Temporal tables in SQL Server

July 4, 2016 by

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 datatime2 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 datatime2 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 datatime2 column must have the GENERATED ALWAYS AS ROW START option set and the second datatime2 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 datatime2 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 ) CREATE TABLE dbo.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 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 ) CREATE TABLE dbo.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));

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) ) INSERT INTO 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.

  1. 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.

  2. 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.

  3. 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 datatime2 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

See more

To investigate more, you can download ApexSQL Complete, a free SSMS and Visual Studio add-in, which supports all temporal table options.

Further reading


Marko Zivkovic

Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic
Marko Zivkovic
Recovery

About Marko Zivkovic

Marko is a Mechanical engineer, who likes to play basketball, foosball (table-soccer) and listen to rock music. He is interested in SQL code, PHP development, HTML and CSS techniques.

Currently working for ApexSQL LLC as a Software Sales Engineer, he is helping customers with any technical issues and does quality assurance for ApexSQL Complete, ApexSQL Refactor and ApexSQL Search free add-ins.

View all posts by Marko Zivkovic

2,482 Views