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

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

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
How can we see the historical snapshot of a table in SQL Server using Temporal Tables (aka Row Versioning)

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
How can we see the historical snapshot of a table in SQL Server using Temporal Tables (aka Row Versioning)
01

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:

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

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
How can we see the historical snapshot of a table in SQL Server using Temporal Tables (aka Row Versioning)
03

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'
How can we see the historical snapshot of a table in SQL Server using Temporal Tables (aka Row Versioning)
04

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
How can we see the historical snapshot of a table in SQL Server using Temporal Tables (aka Row Versioning)
05

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

SELECT * FROM dbo.ipallilos  
FOR SYSTEM_TIME ALL;
How can we see the historical snapshot of a table in SQL Server using Temporal Tables (aka Row Versioning)
06

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:

Share it

Leave a reply