Temporal Tables


Working with data inside Relational Database Management Systems (RDBMSs) has been the forte of database administrators. Traditional databases store data that is considered to be valid at the current time only. Temporal tables make it possible for the database administrator to create a snapshot of the data at any point in time in the past.

It is very difficult to implement this temporal data retrieval in a normal database. We would need to create a logging table. Logging is only one part of the infrastructure. If we need to get to the values at a specific point in time, it would involve tons of date and time datatype manipulation. The queries to extract data for a particular date, other than the current value, are extremely complex and prone to error. DBAs often build specialized databases that use a number of functions as helper classes.

With the release of SQL Server 2016 CTP2.0, temporal tables are now practical to maintain. With this release it is important to keep full history of changes in the context of system (transaction) time, i.e. time when data modification became actual in the database. In this case, RDBMS (SQL Server) is fully responsible for keeping an immutable history and maintaining timestamps for current and previous record versions. As Jonathan Allen explained for InfoQ

Implementation wise, a Temporal Table is really two tables. One table contains the current values while another handles the historic versions of the data. The tables are linked so that any UPDATE or DELETE operation in the normal table automatically creates a corresponding history row. (INSERT operations don’t create historic records.)

Let’s look at how to create a database and create our temporal table:

CREATE DATABASE TemporalDB
GO
USE TemporalDB
GO

Simple temporal table with history table automatically created - name of history table is:

MSSQL_TemporalHistoryFor_<object_id of table>) :
CREATE TABLE dbo.TemporalExample (
ID INT NOT NULL PRIMARY KEY,
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL ,
Period for system_time (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON)
GO

Once this table gets created, we will find the same in our Object Explorer tree as shown below:

In this example, we have created a temporal table with a history table to track the data that is created.

As is shown, for the sake of simplicity we design the temporal table using two user tables related to each other. We can identify the related table by querying the DMVs:

SELECT name [Original_Table], OBJECT_NAME(history_table_id)
[History_Table_Name]
FROM sys.tables
WHERE history_table_id IS NOT NULL

If we need to drop the table, it cannot be done until there is a related table. This can be done using the following command.

USE [TemporalDB]
GO
ALTER TABLE [dbo].[TemporalExample] SET ( SYSTEM_VERSIONING = OFF )
GO
DROP TABLE [dbo].[TemporalExample]
GO

-- Add the history table which was created in your databases

DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_933578364]
GO