Temporal Tables and how to use them in SQL Server

If you want to keep track of when changes were made to your data, or see how data changes over time, you need to start using temporal tables in SQL Server. Microsoft introduced this great feature in the 2016 version of SQL Server, but is still relatively unknown.

In this article, we’re going to take a look at temporal tables in SQL Server and how to use them. Including a brief discussion on using the SQL Spreads Excel Add-In to make this data more accessible to a wider audience for more transparency.

What are temporal tables in SQL Server?

A temporal table (also known as system-versioned temporal table) is a type of table that keeps a full history of data changes. You can then track when changes to data were made, and so perform audits, point in time analysis or row-level data repairs.

When you create a temporal table with system-versioning, a history table with the same schema gets automatically created. The history table stores previous versions of rows whenever a change is made to the data in the main table.

As you can see in the diagram below, the temporal table (also referred to as the current table) stores the current data as normal, whilst the history table stores the old versions of the data. The process of writing the old versions of the data to the history table whenever a change is made is automatic.

The date columns in the history table are used to record the period of validity for each row. So, for example, in the above diagram, we can see that the previous values for Item A were 50 (valid between 1 Sep and 2 Sep) and 75 (valid between 2 Sep and 8 Sep).

We can use specific T-SQL syntax to query the temporal table and gain insights into which data changed and when.

Temporal tables in SQL Server: use cases

What can you use temporal tables for? Here is a list of scenarios where you could use temporal tables:

• Compliance Auditing
• Historical Trend Analysis
• Point-in-Time Reporting
• Slowly Changing Dimensions

Let’s look at each of these scenarios in a little more detail.

Compliance Auditing

Data compliance regulations such as GDPR require organizations to manage their data in an effective manner. This includes storing data for certain retention periods and also tracking changes to data. Temporal tables provide this functionality in a robust and transparent manner, without having to implement custom solutions.

Historical Trend Analysis

Temporal tables track the value of a record over time, and so they’re especially useful for looking at historical trends. You can query across the current and history table using the FOR SYSTEM_TIME clause to see a complete history of changes. This could be used to view product input cost changes over time, or customer account balances over time.

Point-in-Time Reporting

There are sometimes situations where you need to answer the question, “what was the value of X at a specific date/time?”. This could be in response to an audit query, or to pick up potentially anomalous values. Again, we can make use of the FOR SYSTEM_TIME clause when querying the temporal table.

Slowly Changing Dimensions

Dimensions in data warehousing typically contain static data about entities such as geographical locations, customers, or products. However, this data can change (albeit slowly) over time and the changes are not normally predictable. The term slowly changing dimension is often used to describe this scenario.

Organizations therefore need strategies to manage this process. This could be a ‘do nothing’ approach where no history is preserved or could include keeping history in separate tables. The latter approach can be adopted by incorporating temporal tables into the solution.

How to use temporal tables in SQL Server

Depending on the scenario, you can create a system-versioned temporal table in the following ways:

• create a new system-versioned temporal table, or
• modify an existing table from the database by adding temporal attributes.

Create a temporal table in SQL

A temporal table has the following key characteristics:

1. A primary key must be defined.
2. Two DateTime2 columns must be defined to record the start date and end date of the period of validity. If needed, these columns can be hidden using the HIDDEN flag. These columns are called the SYSTEM_TIME period columns.
   • The GENERATED ALWAYS AS ROW START column represents the time when the row data became current. Basically the system will set current UTC time when an INSERT/UPDATE of the record occurs in the system-versioned temporal table. The GENERATED ALWAYS AS ROW END column represents the time when the row data was no longer current. This column indicates the time when UPDATE/DELETE changes occur.
   • The PERIOD FOR SYSTEM_TIME clause specifies the start and end of the period.
3. 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.
4. The optional HISTORY_TABLE parameter sets the name of the history table. If the 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.

The example below shows an example of creating an employee table as a system-versioned temporal table:

CREATE TABLE dbo.Employee (
  Employee_ID int primary key, 
  Name varchar(50) NOT NULL, 
  LastName varchar(100) NOT NULL, 
  JobTitle varchar(100) NULL, 
  Manager int NULL, 
  HireDate datetime2 NULL, 
  Salary numeric(10, 2) NULL, 
  SysStartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, 
  SysEndTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, 
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (
  SYSTEM_VERSIONING = ON(
    HISTORY_TABLE = dbo.EmployeeHistory
  )
);

When this statement is executed, two tables are created in the database – one for the current data (i.e. the temporal table) and one for the historical data.

Enable system-versioning on an existing non-temporal table

To enable system-versioning on an existing non-temporal table we can run an ALTER TABLE statement that will do two things:

• add the SYSTEM_TIME period columns to the existing table to define periods for system versioning
• enable system versioning and specify history table name

Here is an example that enables system-versioning on an existing table called Employee:

ALTER TABLE 
  dbo.Employee 
ADD 
  SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT GETUTCDATE(), 
  SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT CONVERT(
    DATETIME2, ‘9999 - 12 - 31 23 : 59 : 59.9999999’
  ), 
  PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);
GO 
ALTER TABLE 
  dbo.Employee 
SET 
  (
    SYSTEM_VERSIONING = ON (
      HISTORY_TABLE = dbo.EmployeeHistory
    )
  );
GO

Notes on the example:

• it assumes that there’s an existing table called Employee with a primary key defined.
• the newly created period columns are populated for system versioning using default values for the start and end times because these values can’t be null.
• uses the HIDDEN clause for the period columns to ensure no impact on existing applications interacting with the current table.

Disable and re-enable system versioning to change table schema

This is for the scenario when you want to make changes to a system-versioned table, such as adding columns. Disabling system versioning is required to modify the table schema.

These steps should be completed within a transaction to prevent updates to both tables while updating the table schema, which means you can skip the data consistency check when re-enabling system versioning and gain a performance benefit.

Here is an example:

BEGIN TRAN 
/* Takes schema lock on both tables */
ALTER TABLE 
  Employee 
SET 
  (SYSTEM_VERSIONING = OFF);
/* expand table schema for temporal table */
ALTER TABLE 
  Employee 
ADD 
  Is_Active VARCHAR DEFAULT 'N' NOT NULL;
/* Expand table schema for history table */
ALTER TABLE 
  EmployeeHistory 
ADD 
  Is_Active VARCHAR DEFAULT 'N' NOT NULL;
/* Re-establish versioning again*/
ALTER TABLE 
  Employee 
SET 
  (
    SYSTEM_VERSIONING = ON (
      HISTORY_TABLE = dbo.EmployeeHistory, 
      DATA_CONSISTENCY_CHECK = OFF
    )
  );
COMMIT

Drop temporal tables

If you want to drop temporal (system versioning) tables, you need to make sure you disable the system versioning first.

ALTER TABLE 
  [dbo].[Employee] 
SET 
  (SYSTEM_VERSIONING = OFF) GO 
DROP 
  TABLE [dbo].[Employee] GO 
DROP 
  TABLE [dbo].[EmployeeHistory] GO

How to Query a Temporal table

First, lets add some sample data into our Employee table and then perform some changes to the data. Once this has been done, we can perform some queries against the temporal table.

Add data using the SQL Spreads Excel Add-In

We can easily add the data to the Employee table via Excel using the SQL Spreads Add-In for Excel. If you don’t already have the Add-In installed, you can download the SQL Spreads free trial.

After completing the installation, setup the connection to your SQL Server by clicking on the Open Designer button on the SQL Spreads menu. Once you have connected, you’ll see the list of databases in the right-hand pane – expand the TemporalTable_Demo database and click on the Employee table.

The Employee table from SQL Server will now be loaded into Excel – of course it is currently empty, but we can add some sample data. However, before we do this, we need to specify that the SysStartTime and SysEndTime columns are Read Only because SQL Server manages these columns. To do this, click on the Columns tab in the SQL Spreads Designer and then enable the Read Only setting for these two columns.

Now we can go ahead and add our sample data and click on the Save to Database button. The data will now be written back to SQL Server.

We can check that the data has been written back to SQL Server in SQL Management Studio:

Now we can make the following changes to the data in Excel, clicking the Save to Database button after each change:

• Change 1 : the salary of Charlie Lee is changed from $36,000 to $40,000
• Change 2 : the job title of Hanna Taylor is changed from Supply Chain Analyst to Procurement Analyst
• Change 3 : the salary of Alice Smith is changed from $30,000 to $32,000

The diagram below shows these changes on a timeline (for the sake of simplicity for this article, it is assumed that the interval between each change was 2 minutes).

After the initial insert, there are 3 subsequent changes to the data:

• Change 1 : at 2023-09-19 17:18 the salary of Charlie Lee is changed from $36,000 to $40,000
• Change 2 : at 2023-09-19 17:20 the job title of Hanna Taylor is changed from Supply Chain Analyst to Procurement Analyst
• Change 3 : at 2023-09-19 17:22 the salary of Alice Smith is changed from $30,000 to $32,000

When each update is made in the Employee table (i.e. for Change 1, Change 2 and Change 3), the old data is written to the Employee History table.

Here is what the data looks like in the Employee table and the EmployeeHistory table:

Note the following:

• The Employee table shows the current version of all records, so it shows the new values after the 3 changes to the data were made.
• The EmployeeHistory table contains 3 records – one for each of the updates that were made. The SysStartTime and SysEndTime shows the periods of validity for that version of the record – in this case, the start shows the time of the initial insert, and the end shows the time when the update was made.
• The EmployeeHistory table does not show the initial inserts; a row only gets added for updates or deletes.

Now let’s look at how to query the data.

Get values at a specific point in time

If we want to know the values at a specific point in time, we can use the FOR SYSTEM_TIME statement, as in the following query:

SELECT * 
FROM 
  [dbo].[Employee] FOR SYSTEM_TIME AS OF '2023-09-19 17:19:00' 
ORDER BY 
  Employee_ID

The diagram below highlights that the point in time value used in the query is between Change 1 and Change 2, and so the original values (i.e. Version 1) for Hannah and Alice are valid, whilst the Version 2 value is valid for Charlie:

The following results are returned:

As you can see, because we specified a point in time that was between when change 1 and change 2 were made, the value for Salary for Charlie Lee shows the changed value, not the original one. All other records show their original values.

Also, note that we don’t need to include the EmployeeHistory table in our query at all. We simply run our query against the Employee table, and because it has an associated history table, SQL Server knows to return the history data also.

Get versions between two dates

If we want to see all the versions of the records between two particular dates, we can specify a date range in our query:

SELECT *, SysStartTime, SysEndTime 
FROM 
  [dbo].[Employee] FOR SYSTEM_TIME 
FROM '2023-09-19 17:17:00' TO '2023-09-19 17:21:00' 
ORDER BY 
  Employee_ID, SysStartTime 

The diagram below shows the start and end times of the query:

Note that in this query, I’ve explicitly included the SysStartTime and SysEndTime to help us see when the versions were valid. Here are the results:

As you can see, two records for Charlie Lee and Hannah Taylor are included in the results, because for the time range that we specified, 2 versions of the data were in existence.

Viewing Temporal table data in Excel

With the SQL Spreads Add-In for Excel, you can view and write-back data to SQL Server from within Excel, making the data accessible to a wide range of people.

In the screenshot below, you can see that I have connected to the EmployeeHistory table using SQL Spreads. Then I created a Tree Filter so that I can select an employee to see how their Job Title, Manager or Salary has changed over time.

In an upcoming article, we’ll look in more detail about how to use Excel and SQL Spreads to work with temporal tables in SQL Server.

Summary

A temporal table in SQL Server (also known as system-versioned temporal table) is a feature introduced in SQL Server 2016 that enables you to keep a historical record of data changes in a database table. They are particularly useful for auditing and tracking changes to data over time.

The process to create a temporal table, or to enable system-versioning on an existing table, is straight forward. Querying data in a temporal table is also an easy process and simply requires you to define the point in time or time range that you are interested in viewing data about. Microsoft has really done a great job here – it’s now not necessary for organizations to create their own audit or history table solutions; it is simply a matter of using the built-in features of temporal tables.

FAQs

What are the limitations of temporal tables?

Temporal tables have a few limitations:

1. Non-Support for Non-Clustered Columnstore Indexes: Temporal tables cannot have non-clustered columnstore indexes. Consider alternative strategies if your table requires this type of index.
2. Limited Precision for Date and Time Types: The history table of temporal tables uses the datetime2 data type, potentially limiting the precision of your temporal data.
3. No Direct Modification of System-Versioned Tables: Direct modifications of historical data in system-versioned tables are not allowed. Use appropriate temporal queries or procedures for changes.
4. Exercise Caution with Temporal Table Triggers: Triggers on temporal tables can impact system-versioning behavior and lead to unexpected results. Use them judiciously.

What are the temporal table data types?

Temporal tables use specific data types:

1. datetime2: Temporal tables utilize the datetime2 data type for the start and end columns in the history table, representing the period during which a row was valid.
2. Regular Columns: In addition to system-versioning columns, your temporal tables will have regular columns with data types based on your application’s needs.

How to check if a table is temporal in SQL Server?

To check if a table is a temporal table, run the following query on your database:

SELECT name, history_table_id, temporal_type, temporal_type_desc

FROM sys.tables

This query examines the temporal_type column in the sys.tables system catalog view, categorizing a table as system-versioned, a history table, or non-temporal. You can adjust the WHERE clause for specific table checks. Read the blog article from the beginning to get a step by step guide on how to create a temporal table, or change a table to temporal.