How can we see the historical snapshot of a table in SQL Server using Temporal Tables (aka Row Versioning)

• Author
• Recent Posts

Stratos Matzouranis

The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.

Certifications:

Latest posts by Stratos Matzouranis (see all)

• How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
• How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
• How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024

Coming from the SQL Server 2016 version, SQL Server brought the ability to view a table historically over time. That is, to have a row versioning in the table with the possibility to choose the time we want. This process is made possible by the use of temporal tables.

What are temporal tables

The temporal tables are tables that are designed to keep their historicity to facilitate easy access point in time.

Each temporal table it must have two datetime2 columns that are automatically updated by the database engine. Their job is to record how long each record was valid and when it was modified.

Also one is created historical table with the same shape as the original one whose job is to store the previous values of records (row versions) every time they happen update ή delete.

How temporal tables work

To check it validity of records, the two system datetime2 columns we mentioned before are used:

• As StartDate is the date it happened insert the subscription.
• As EndDate is the date it was modified ie it was done update ή delete. If the date appears year 9999 means that this line is the latest and is in effect.

The example

First we will make the temporal table. The difference with a normal table is that we must have defined the two systemic pillars StartDate and EndDate, as well as his name history table:

CREATE TABLE ipallilos (
 id INT IDENTITY(1,1) PRIMARY KEY
,Onoma VARCHAR(30)
,Epitheto VARCHAR(30)
,Misthos DECIMAL
,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.ipallilosHist)) 
GO

We fill the table with entries:

INSERT INTO ipallilos (onoma,epitheto,misthos) VALUES
( 'Stratos', 'Matzouranis',3500.00),
( 'Nikos', 'Georgiou',1600.00),
( 'Anastasis', 'Papandreou',1900.00)
GO

select * from ipallilos

But here something strange is happening while I passed the registrations 20:30 it seems like 18:30 passed. This happens as the time it uses is not with the timezone which the server has, i.e. GMT+2 but with UTC.

In order to see the real time when the recordings were made, we should add to the time the difference between the UTC timezone and the timezone of the system:

* The StartDate time is different in this image as I rebuilt the table from scratch afterwards to account for this phenomenon.

select *,DATEADD(HOUR,DATEDIFF(hour,  SYSUTCDATETIME(),SYSDATETIME() ),StartDate) as RealStartDate from dbo.ipallilos

Now let's try to make one insert, one update and one delete:

insert into ipallilos (onoma,epitheto,misthos) VALUES
( 'Maria', 'Nikolaou',1500.00)
GO

update ipallilos set misthos = 4000
where id = 1

delete ipallilos where  id = 3

select * from dbo.ipallilos

We see that in the first record the StartDate changed. This happened as after the update the old registration went to history table and the record we see here passed as new:

So to history table we will have the registration done update and the one that happened delete. As EndDate we see the time these actions took place:

select * from dbo.ipallilosHist

Let's see how the registrations were between 21:30 and 22:30:

** I can't state the exact time because if I don't put between it will want me to put the exact time when the change was made at ms level.

SELECT * FROM dbo.ipallilos  
FOR SYSTEM_TIME BETWEEN '2020-10-06 21:30' and '2020-10-06 22:00'

Oops!! this image is not the one we had at ~22:00 as I updated these values at 22:10. This happened as we said SYSTEM_TIME it is in UTC timezone.

To see the table with the time of our timezone that we have on the server, we should use the functions that we showed before in parameters:

declare 
@realtimeFROM datetime,
@realtimeTO datetime;
set @realtimeFROM = DATEADD(HOUR,DATEDIFF(hour,SYSDATETIME(),SYSUTCDATETIME() ),'2020-10-06 21:30');
set @realtimeTO = DATEADD(HOUR,DATEDIFF(hour,SYSDATETIME(),SYSUTCDATETIME() ),'2020-10-06 22:00');

SELECT * FROM dbo.ipallilos  
FOR SYSTEM_TIME BETWEEN @realtimeFROM and @realtimeTO

To see all the historical documents together with the applicable ones:

SELECT * FROM dbo.ipallilos  
FOR SYSTEM_TIME ALL;

How to disable the temporal table

To delete a temporal table we must first close versioning and delete both the normal table and the historical one:

ALTER TABLE [dbo].[ipallilos] SET ( SYSTEM_VERSIONING = OFF)
GO
DROP TABLE [dbo].[ipallilos]
GO
DROP TABLE [dbo].[ipallilosHist]
GO

Sources:

• Temporal tables