Temporal Table Explained

20. September 2021 Mehedi SQL Server , SQL Tips Comments (0)

Are you familiar with Temporal table not temporary table? Well, if you are not, then you are in the right path.

Temporal Table

A temporal table is an user designed system-versioned table which keeps the full history of data changes of a table. This changes of data is fully managed by the SQL Server database engine. It was introduced in ANSI SQL 2011 and SQL Server first support it as built-in database feature from SQL Server 2016 version.

Why Temporal Table

Temporal table's has below use cases:

  • Auditing and forensic analysis of data changes
  • Reconstructing state of the data at any given time in the past
  • Projecting trends of data changes
  • Maintaining a slowly changing dimension for decision support applications    
  • Rebuilding data in case of accidental data changes or application errors

How it works

Temporal table works in pair. So, when we create a System-versioning temporal table two tables are created with identical schema.

Image-1: Temporal table and the associated history table (courtesy by Microsoft)

  1. The primary/current table which stores all current data
  2. The secondary/history table which preserve the history of changes
  3. Two additional datetime2 columns are required
    • Period start column: Start time of the row
    • Period end column: End time of the row. Default value is the max value i.e. 9999-12-31 23:59:59.9999999 to mark the column as active

Demo

Let's see the practical example. We will create a student table to store the data, make some updates and will examine the final output.

Create the table

IF OBJECT_ID('Students', 'U') IS NOT NULL
    DROP TABLE Students;
GO

CREATE TABLE dbo.Students
(
  [pk_id] int NOT NULL PRIMARY KEY CLUSTERED
  , [sid] varchar(20) NOT NULL
  , [name] varchar(50) NOT NULL
  , [class] varchar(100) NOT NULL
  , [address] nvarchar(1024) NOT NULL
  , [startDate] datetime2 GENERATED ALWAYS AS ROW START
  , [endDate] datetime2 GENERATED ALWAYS AS ROW END
  , PERIOD FOR SYSTEM_TIME ([startDate], [endDate])
 )
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.StudentsHistory));

Image-2: Two tables are created

Populate dummy data

INSERT INTO Students ([pk_id],[sid],[name],[class],[address])
VALUES (1, 'S_001', 'Bob', 'Class-1', 'Dhaka, Bangladesh')
, (2, 'S_002', 'Smith', 'Class-1', 'Dhaka, Bangladesh')
, (3, 'S_003', 'John', 'Class-1', 'Dhaka, Bangladesh');

Now we will make some modification

UPDATE Students Set
[class] = 'Class-2'
WHERE  [pk_id] = 1;

UPDATE Students Set
[class] = 'Class-3'
WHERE  [pk_id] = 1;

Output

Display Students table

SELECT * FROM Students;

Image-3: Output of Students table

SELECT * FROM StudentsHistory;

Image-4: Output of StudentsHistory table

Auditing the Students table.

SELECT * FROM Students
FOR SYSTEM_TIME
BETWEEN '2021-01-01 00:00:00.0000000' AND '2022-01-01 00:00:00.0000000'
ORDER BY [pk_id] ASC;

Image-5: Output of Students table audit

Important Notes

Few important notes of using Temporal table:

  • Temporal table must have a primary key. However, the history table don't have a primary key
  • The SYSTEM_TIME period columns must have a datatype of datetime2
  • The Temporal or history table cannot be FILETABLE
  • INSERT or UPDATE query directly on SYSTEM_TIME period columns is not allowed
  • TRUNCATE TABLE is not allowed
  • INSERT or UPDATE query directly on history table is not allowed
  • TRIGGER has limited usage

Summary

Temporal table is very useful in case of audit and forensic analysis and preventing unwanted delete or update of data. System-versioning temporal table also shows track of data changes. In my experience, I used it in some projects where I need to preserve the history of data changes. Hopefully, it will be helpful to you also.

 

 

 

Tags:

Add comment