MS SQL Server

How to Use the SQL Server Temporal Table

Temporal tables, also known as system-versioned tables, are tables that allow you to track and keep a history of the data on a specific table. Using temporal tables, you can track the history of the changes made to the data in a table.

This article will lay a foundation for creating, working with, and using temporal tables in SQL Server.

System versioned tables were introduced in the ANSI SQL 2011 standard and have been available as a feature in SQL Server 2016 and above.

Unlike a normal table that can only show and work with current data, the temporal tables allow you to view and work even with previously deleted data. As mentioned, this is possible due to the ability of a temporal table to keep track of the changes made to the data in a table.

The table contains two key columns: SysStartTime and SysEndTime. These two columns are used to define the existing and previous data for every record in a table. You can use specific time intervals to view how the data in a table has changed.

Create a Temporal Table

Before you can create a temporal table, it must fulfill the following requirements:

  1. A temporal table must contain a primary key constraint defined.
  2. It must contain two columns to record the starting and end date. These columns must be of datetime2 data type. The columns should be declared as GENERATED ALWAYS AS ROW START/END.
  3. SQL Server assumes the two columns are non-nullable. Hence, the create table statement fails if the query tries to set columns that are nullable.
  4. SQL Server auto-generates a history table using a similar schema as the temporal table.
  5. You cannot use INSTEAD OF triggers in a system versioned table.
  6. The history table should not contain any constraints.
  7. You cannot change the data in the history table.
  8. Statements, such as INSERT and UPDATE cannot reference the period columns.
  9. The history table is created as a row history table, and the page compression is applied if applicable. Otherwise, the table is left uncompressed.
  10. SQL Server will auto-generate a clustered index for the history table.

How to Create a Temporal Table: T-SQL

Let’s look at a simple demonstration of creating a temporal table. Consider the example query shown below:

CREATE TABLE dbo.my_temporal_table(
    id INT,
    fname VARCHAR(50),
    email VARCHAR (255),
    department VARCHAR(50),
    CONSTRAINT pk PRIMARY KEY (id),
    SysStartTime datetime2 generated always AS ROW START NOT NULL,
    SysEndTime datetime2 generated always AS ROW END NOT NULL,
    period FOR system_time (SysStartTime, SysEndTime)) WITH (system_versioning = ON);

Once we run the query above, the SQL Server will create the table with the specified name.

In the SQL Server Management Studio, you can view a system versioned table by expanding the tables option in your target database:

Notice that the SQL Server automatically generates a history table with a similar schema as the system versioned table. However, pay attention to the columns in the history table. Notice that they do not have any constraints.

Consider the image shown below:

As you will see, SQL Server generates a history table under a name following a specific format. To set a custom name for your history table, specify it in the create table statement as shown:

---
period FOR system_time (SysStartTime, SysEndTime)) WITH (system_versioning = ON, history_table = mytemporal_tableHistory);
---

Next, if you expand on the indexes option for the history table, you notice that SQL Server auto-generated a clustered index:

Using Temporal Tables

Let us test the functionality of temporal tables by inserting a few records into the table. Consider the example query shown below:

INSERT INTO my_temporal_table(id, fname, email, department)
VALUES (1, 'John Davis', '[email protected]', 'Front-End'),
       (2, 'Ruby Raw', '[email protected]', 'Database'),
       (3, 'Scott Turner','[email protected]', 'Full-Stack'),
       (4, 'Alice Jensen', '[email protected]', 'Version Control'),
       (5, 'Peter Green', '[email protected]', 'Backend');

Once we have the sample data inserted into the table, we can query it as:

SELECT * FROM my_temporal_table;

You should get an output close to the one shown below as

To understand how the system versioned table works, let us delete and update rows in the table:

DELETE FROM my_temporal_table WHERE department = 'Database';
UPDATE my_temporal_table SET fname = 'John M' WHERE id = 5;

Next, query the data in the main table:

SELECT * FROM my_temporal_table;

If you query the history table, you should see the old version of the data with the correct timestamps.

Conclusion

This guide covered the concept of temporal or system versioned table in SQL Server. Using this guide, you will be in a position to track the history of your data using SQL Server temporal tables. We hope you found this article helpful. Check out more Linux Hint articles for tips and tutorials.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list