SQL Server 2016 – Query Time Based Data on Temporal Table

Hello Friends,

In my previous blog posts, we have seen multiple ways for the creation of temporal tables and DML operation impact. Today we will have a look at Query Time Based Data on Temporal Table. First, let me create the table and then I’ll perform some DML operations, so that can query the data.

 ------------------ Step 1: Create temporal database -----------------
CREATE DATABASE TEMPORAL;
GO
------------------ Step 2: Create temporal table -----------------
USE [TEMPORAL]
GO
CREATE TABLE TemporalTableDemo
(
	EmpID BIGINT NOT NULL PRIMARY KEY, --One Primary Key is mandatory
	FName VARCHAR(100) NOT NULL,
	MName VARCHAR(100) NOT NULL,
	LName VARCHAR(100) NOT NULL,
	DeptID INT NOT NULL,
	-- two columns with datatime2 data type specified as GENERATED ALWAYS AS ROW START/END
	LifeStart DATETIME2 GENERATED ALWAYS AS ROW START, 
    	LifeEnd DATETIME2 GENERATED ALWAYS AS ROW END,
    	PERIOD FOR SYSTEM_TIME (LifeStart, LifeEnd)
)
WITH (SYSTEM_VERSIONING=ON)
GO
--Inserted data at 2016-08-27 15:04:36.3008697
INSERT INTO TemporalTableDemo
VALUES (1001,'Anuj','Singh','Saini',1,default,default),
(1002,'Prince','Kumar','Rastogi',4,default,default),
(1003,'Akhil','Pratap','Singh',1,default,default),
(1004,'Tarun','Kumar','Sinha',1,default,default),
(1005,'Lokesh','Singh','Solanki',1,default,default)
GO
SELECT * FROM TemporalTableDemo
GO
--Updated record at 2016-08-27 15:07:14.3399192 
UPDATE TemporalTableDemo
SET DeptID=2
WHERE EmpID=1004
GO
SELECT * FROM TemporalTableDemo
GO
-- deleted record at 2016-08-27 15:11:10.7639012
DELETE FROM TemporalTableDemo WHERE EmpID=1002
GO
SELECT * FROM TemporalTableDemo
GO

Now lets take a look at all the records of Temporal table and historical data:

USE [TEMPORAL]
GO
SELECT * FROM TemporalTableDemo
GO
Select * from [dbo].[MSSQL_TemporalHistoryFor_565577053]
GO

To see the data in the tables at a specific time (I am specifying the time when the record was  updated). You can see that record for EmpID 1002 was deleted, so it has been retrieved from historical table:

USE [TEMPORAL]
GO
--Exact as of record updated time
SELECT * FROM TemporalTableDemo
FOR SYSTEM_TIME AS OF '2016-08-27 15:07:14.3399192'
GO

In the above section we have seen the data at a specific time. We can also find out the details of a specific record between a date time range:

USE [TEMPORAL]
GO
--Between start of record insertion and after deletion
SELECT * FROM TemporalTableDemo
FOR SYSTEM_TIME BETWEEN '2016-08-27 15:04:14.3399192' AND '2016-08-27 15:11:14.3399192'
WHERE EmpID=1004
GO

From the above output, you can see that first record is from the current table while the rest of the records from historical table (which is before the update operation). If you want to see only historical records, then you can use CONTAINED IN as mention below. It will fetch the records only from historical table.

USE [TEMPORAL]
GO
--Time specified before record insertion and after update
SELECT * FROM TemporalTableDemo
FOR SYSTEM_TIME CONTAINED IN ('2016-08-27 15:04:00.3399192','2016-08-27 15:11:09.3399192')
WHERE EmpID=1004
GO

From the above output, you can see that we are getting only a single record that is historical record not the current record. We can use ALL if we want to see all the data from current and historical table:

USE [TEMPORAL]
GO
--ALL for all the records 
SELECT * FROM TemporalTableDemo
FOR SYSTEM_TIME ALL
GO

HAPPY LEARNING!

Regards:
Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook