SQL Server – Temporal Table

In this post, we will see how to create and manage a Temporal Table in Sql Server.
But first of all, what is a Temporal Table?
From Microsoft web site:
“Temporal tables (also known as system-versioned temporal tables) are a database feature that brings built-in support for providing information about data stored in the table at any point in time, rather than only the data that is correct at the current moment in time.“
In a nutshell, Temporal tables maintain history of data for a given retention period and they could be very useful in many scenarios like for example Auditing and historical trend analysis.
Temporal tables have been a feature of SQL Server since version 2016 and now are supported in Azure Sql Server as well.

We start opening SSMS and we create a database called ‘Test’ with this script:

USE [master]
GO

CREATE DATABASE [Test]

Then, we create a Temporal table called Users defined as follows:

CREATE TABLE Users
(  
    [Id] int NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, 
    Email NVARCHAR(250) NOT NULL,
    Password NVARCHAR(250) NOT NULL , 
    IsAdmin bit NOT NULL,
	Rating int NOT NULL,
    [ValidFrom] datetime2 (0) GENERATED ALWAYS AS ROW START,
    [ValidTo] datetime2 (0) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
 )  
 WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.UsersHistory));

It is important to highlight that in order to have everything works fine, we need to add the ValidFrom and ValidTo columns and the PERIOD line.

Now, we feed the table using this script:

USE [Test]
GO
INSERT INTO [dbo].[Users]([Email], [Password], [IsAdmin], [Rating])
     VALUES('email1@test.com', 'pass1', 0, 100)
INSERT INTO [dbo].[Users]([Email], [Password], [IsAdmin], [Rating])
     VALUES('email2@test.com', 'pass2', 1, 120)
INSERT INTO [dbo].[Users]([Email], [Password], [IsAdmin], [Rating])
     VALUES('email3@test.com', 'pass3', 0, 10)
INSERT INTO [dbo].[Users]([Email], [Password], [IsAdmin], [Rating])
     VALUES('email4@test.com', 'pass4', 0, 300)
GO

In order to check everything went well, we run a simple query:

Then, we are going to run 3 updates using the item with the Id = 1:

update [dbo].[Users] set Rating = 50 where Id=1

update [dbo].[Users] set Rating = 150 where Id=1

update [dbo].[Users] set Rating = 80 where Id=1

Now, If we run a simple query, this will be the result:

This is exactly what we expected but, using specific parameters in the query, we could have in output all values modified in the table:

BETWEEN

select * from [dbo].[Users]
for System_Time
 Between '2022-07-04 22:03:00' and '2022-07-04 22:04:00'
order by Id

AS OF

select * from [dbo].[Users]
for System_Time
 AS OF '2022-07-04 21:55:30'
order by Id

CONTAINED IN

select * from [dbo].[Users]
for System_Time
Contained in ('2022-07-04 21:55:00', '2022-07-04 22:04:59')
order by Id

ALL

select * from [dbo].[Users]
for System_Time
All
order by Id

Obviously, a temporal table handles all CRUD operations, like for example Delete.
If we run a Delete operation for item with Id = 1

delete from [dbo].[Users] where Id = 1

this will be the result:

But, if we run all the previous queries, we will have the item deleted in output as well:

In the end, we can say that using Temporal table is a good solution in scenarios that require tracking the history of data changes.
In my opinion a good candidate table for a ‘Temporal table’ is an entity that doesn’t have many small updates because, we have to consider the space used for saving data that could grow exponentially.