SQL Server TEMPORAL TABLE

SQL Server Temporal tables (also known as system-versioned tables) allow us to track data changes. It was introduced in SQL Server 2016.

It allows SQL Server to maintain and manage the history of the data in the table automatically, So we can get all information about the data that was stored at any specified time rather than just the data that is current.

So basically a system versioning is impemented as a pair for any table that is a current table and a history table, the current table contains the current value for each row and the history table contains each previous value for each row.

In each table current table and History table , the following two columns are used to define the period of validity for each row

• Period start column: The system records the start time for the row in this column, by default it is specfied as the SysStartTime column of data type datetime2.
• Period end column: The system records the end time for the row in this column, by default it is specified as the SysEndTime column of data type datetime2.

Following are some real time usages temporal tables.

• Auditing: Temporal table provides all the information of data changes so it is easy to get the information when data was modified in real table, so auditing data can be done easily.
• Reconstructing state of the data , in case a record is accidentally deleted or updated.
• Calculating trends over time: by understanding how the data changes over time.

Lets create a temporal table ProductMasterHistory for SQL table named as ProductMaster in SQL Server.

CREATE TABLE dbo.ProductMaster
(PRODID INT IDENTITY(1,1) PRIMARY KEY,
PRODUCTNAME VARCHAR(100),
PROD_INV VARCHAR(10),
PRODDATE DATETIME,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductMasterHistory));

You can check temporal also known as history table in object explorer, there you can see a ProductMasterHistory table is created.

 

Now we will perform insert, update and delete operation to understand the versioning table functionality.

Data track by Temporal table when records are inserted in table

Lets insert some records into ProductMaster table.

INSERT INTO ProductMaster(PRODUCTNAME, PROD_INV, PRODDATE) 
VALUES( 'Acer Windows 10.0,'450CVGH, getdate()),
('LENEVO Windows 10.0','600CVGH', getdate())

As we have just inserted some records in ProductMaster table , Lets select records from ProductMaster table and ProductMasterHistory table.

When records are inserted, the system sets the value for the SysStartTime column to the begin time of the current transaction (in the UTC time zone) based on the system clock and sets the value for the SysEndTime column to the maximum value of 9999-12-31 which indicates that the row as open.

SELECT * FROM dbo.ProductMaster
SELECT * FROM dbo.ProductMasterHistory

As you can see, sysEndtime date for inserted records is ‘9999-12-31’ which indicates that the row as open as these records are just inserted into a ProductMaster table and no modifications are done on these records so far that why there is no track history available in History table.

Data track by Temporal table when Record are updated in  table

Lets update the product invoice number from ‘450CVGH’ to‘230CVGH’ for ProdID =1.

UPDATE dbo.ProductMaster SET Prod_INV ='230CVGH' WHERE PRODID =1;

Now select records from ProductMaster and ProductMasterHistory tables

SELECT * FROM dbo.ProductMaster WHERE PRODID=1
SELECT * FROM dbo.ProductMasterHistory WHERE PRODID=1 
order by SysEndTime 

 

 

As you can see, after updating prod_inv in table, a systarttime ‘2020-06-08 05:27:46.9443453’ and an old record is inserted into the history table with the updated timestamp.

The updated timestamp becomes the SysStartTime column of the modified record in ProductMaster table.

The column SysEndTime in ProductMaster table indicates the active record of the table.

Lets update the prod_inv one more time for same record to see  all the multiple modification for any records are tracked in history table.

UPDATE dbo.ProductMaster SET Prod_INV ='500CVGH' WHERE PRODID =1;

 

Lets select records from ProductMaster and ProductMasterHistory tables.

SELECT * FROM dbo.ProductMaster WHERE PRODID=1 
SELECT * FROM dbo.ProductMasterHistory WHERE PRODID=1 order by SysEndTime 

 

And you can see, history table –ProductMasterHistory keeps tracking all the data changes that are made in ProdcutMaster table.

SysEndTime date for first record in history ProductMasterHistory table becomes the sysStartTime date for second entry record in history table, so this is the way history table maintain the tracking of records.

You can also selects all records that is the current data and history data in single result set using following query.

SELECT * FROM dbo.PRODUCTMASTER 
FOR SYSTEM_TIME ALL WHERE PRODID =1 order by PRODID, SysEndTime

 

 

Data track by Temporal table when records are deleted in table

Lets delete one record from table ProductMaster for PRODID 1.

DELETE FROM dbo.ProductMaster WHERE PRODID=1

 

Now select records from ProductMaster and ProductMasterHistory tables.

SELECT * FROM dbo.ProductMaster WHERE PRODID=1 
SELECT * FROM dbo.ProductMasterHistory WHERE PRODID=1 
order by SysEndTime

As you can see, there is no data available for ProdID =1 in ProductMaster table, which means data is deleted from ProdId =1  while in History table the sysEndTime for prodID=1 against the latest entry is updated with the timestamp that indicates the record is deleted from current table.

 

How to query a Temporal data ?

To query a temporal table, there are five variations and option that can be used with FOR SYSTEM_TIME clause.

You have already seen the functionality of ALL with FOR SYSTEM_TIME ( in update case), you can also use remaining option as per your requirement.

The FOR SYSTEM_TIME clause has many variations and options. This provides a way to query the data across current and history tables.

1. AS OF <datetime> – It returns the union of current table and history table as a result table with a rows containing the values that were actual (current ) at the specified point in time in the past(History ).
2. FROM <startdatetime> TO <enddatetime> – It returns the union of current table and history table as a result table with the values for all row versions that were active within the specified time range, regardless of whether they started being active before the <startdatetime> parameter value for the FROM argument or ceased being active after the <enddatetime> parameter value for the TO argument.
3. BETWEEN <startdatetime> AND <enddatetime> – It is similar to FROM clause except the table of rows returned includes rows that became active on the upper boundary defined by the <enddatetime> endpoint
4. CONTAINED IN (<startdatetime> , <enddatetime>) – It returns a table with the values for all row versions that were opened and closed within the specified time range defined by the <startdatetime> and  <enddatetime> for the CONTAINED IN argument.
5. ALL – It returns the union of rows that belong to the current and the history table.

How to delete History table or how to make temporal table as regular table ?

In case if you want to delete History table, then you will see that it can not be directly deleted by drop query also in object explorer you will not get delete option for this as shown below, There is no delete option for history table.

 

 

If you try to drop table using drop statement, it will also an error.

So to get the delete option, first you will have to turn off the system_versioning for table.

As soon you turned off system_versioning for table you will see that temporal table and history table becomes a regular table.

It can be done using alter table statement as shown below.

ALTER TABLE dbo.ProductMaster SET ( SYSTEM_VERSIONING = OFF ) GO

 

After turning off system_versioning, you can easily drop tables from SQL server.

 Drop table dbo.ProductMaster

Also Read..

SQL Server Change Data Capture (CDC)