Querying temporal table data

We have discussed what is a temporal table, how we can create it, and what are the limitations of a temporal table. We have also discussed how we can convert an existing table to a temporal table in our previous posts.

In this post “querying temporal table data” we are going to learn how to query the time varying data from a temporal table.

In SQL Server 2016, we have a new clause FOR SYSTEM_TIME, which has four new sub clauses to query temporal table data:

1. AS OF <datetime>
2. FROM <start_datetime> TO <end_datetime>
3. BETWEEN <start_datetime> AND <end_datetime>
4. CONTAINED IN (start_datetime, end_datetime)

Let’s create a temporal table with dummy data to query with FOR SYSTEM_TIME clause and its sub clauses with examples. Below script is used to create a temporal table and to insert few dummy rows in the table with SQL Server default datetime value (1900-01-01) as row StartTime. We have also executed an update on a row at the end of the script to enable time varying data in the table for demo purpose.

--Create temporal table
CREATE TABLE dbo.EmployeeMaster
(
 BusinessEntityID int NOT NULL,
 NationalIDNumber nvarchar(15) NOT NULL,
 LoginID nvarchar(256) NOT NULL,
 JobTitle nvarchar(50) NOT NULL,
 BirthDate date NOT NULL,
 MaritalStatus nchar(1) NOT NULL,
 HireDate date NOT NULL,
 Gender nchar(1) NOT NULL,
 StartTime DATETIME2(0) NOT NULL,
 EndTime DATETIME2(0) NOT NULL,
 CONSTRAINT PK_EmployeeMaster_BusinessEntityID PRIMARY KEY CLUSTERED(BusinessEntityID ASC)
)

--Add sample rows in the table with SQl Server default date time as row start time
INSERT INTO dbo.EmployeeMaster(BusinessEntityID, NationalIDNumber, LoginID, JobTitle, BirthDate, MaritalStatus, HireDate, Gender, StartTime, EndTime)
VALUES (1, '295847284', 'adventure-works\ken0', 'Chief Executive Officer', CAST('1969-01-29' AS Date), 'S', CAST('2009-01-14' AS Date), 'M', '1900-01-01', '9999-12-31 23:59:59'),
(2, '245797967', 'adventure-works\terri0', 'Vice President of Engineering', CAST('1971-08-01' AS Date), 'S', CAST('2008-01-31' AS Date), 'F', '1900-01-01', '9999-12-31 23:59:59'),
(3, '509647174', 'adventure-works\roberto0', 'Engineering Manager', CAST('1974-11-12' AS Date), 'M', CAST('2007-11-11' AS Date), 'M', '1900-01-01', '9999-12-31 23:59:59')

--Declare StartTime and EndTime columns as system period columns
ALTER TABLE dbo.EmployeeMaster
ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

--Enable system versioning on the table
ALTER TABLE dbo.EmployeeMaster 
SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeMaster_History, DATA_CONSISTENCY_CHECK = ON))

--Update a record to log an entry in history table for demo purpose
UPDATE dbo.EmployeeMaster SET JobTitle = 'President of Engineering' WHERE BusinessEntityID = 2

Below are the employee master table and the associated history table:

We have created a temporal table with dummy rows, now we are going to discuss the sub clauses of FOR SYSTEM_TIME clause which are used to query the temporal table.

1. AS OF <datetime>

AS OF clause returns a result set which has single row for each record valid at the given point of time. This clause performs an UNION between main table and history table and then filters the result set to return single row version of each record which was valid at the given time. The qualifying row(s) satisfies the below condition:

rowstart_datetime <= datetime AND rowend_datetime > date_time

To get the valid row for business entity id 2 as on 22-Aug-2015 and for today from the employee master table, use below select statements:

SELECT * FROM dbo.EmployeeMaster FOR SYSTEM_TIME AS OF '2015-08-22' WHERE BusinessEntityID = 2

--Using a datetime variable initialized with current date time
DECLARE @Date DATETIME = GETDATE()
SELECT * FROM dbo.EmployeeMaster FOR SYSTEM_TIME AS OF @Date WHERE BusinessEntityID = 2

Using JOINs with Temporal tables

We can use temporal tables in joins like regular tables. Below is an example:

--An inner self join to demonstrate joins on temporal tables
SELECT TmpEmp.BusinessEntityID AS TmpEmpCol, Emp.LoginID AS EmpCol
FROM dbo.EmployeeMaster FOR SYSTEM_TIME AS OF '2015-08-22' AS TmpEmp
INNER JOIN dbo.EmployeeMaster AS Emp ON TmpEmp.BusinessEntityID = Emp.BusinessEntityID

In the above query we have used a self inner join with the created dummy table to demonstrate how we can use joins with temporal tables. It seems silly to join the same table in such a way as we did above, but its just for a demo purpose. We can use these tables as normal tables in JOINs.

2. FROM <start_datetime> TO <end_datetime>

FROM clause returns all the row versions (historic and current) of each record which were active within the given time frame. It applies below filter:

row_starttime < end_datetime AND row_endtime  > start_datetime

This clause returns the rows which were active within the given time frame at some point of time.

SELECT * FROM dbo.EmployeeMaster FOR SYSTEM_TIME FROM '2015-08-22' TO '2015-08-23' WHERE BusinessEntityID = 2

We have updated the record for business entity id 2 in the table creation script, have a look on the output of the query:

Internally it performs an UNION operator between temporal table and the associated history table and returns the rows satisfying the above mentioned condition.

3. BETWEEN <start_datetime> AND <end_datetime>

BETWEEN clause returns the rows which qualify the below condition:

row_starttime <= end_datetime AND row_endtime  > start_datetime

BETWEEN clause is similar to the FROM clause. The only difference between FROM clause and BETWEEN clause is that the BETWEEN clause qualifies the records which became active on the exact given parameter end_datetime, whereas FROM clause excludes the records which became active on the exact given end_datetime. We can see this in the qualifying condition of BETWEEN clause which uses row_starttime <= end_datetime, and FROM clause which uses row_starttime < end_datetime for end_datetime value.

SELECT * FROM dbo.EmployeeMaster FOR SYSTEM_TIME BETWEEN '2015-08-22' AND '2015-08-23' WHERE BusinessEntityID = 2

Below is the output which is same as FROM clause output:

To understand the difference between FROM clause and BETWEEN clause, have a look on the below queries. Before using in your scenario, change the start date and end date time accordingly.

--FROM clause
SELECT * FROM dbo.EmployeeMaster FOR SYSTEM_TIME FROM '2015-08-22 10:40:05' TO '2015-08-22 10:40:06' WHERE BusinessEntityID = 2

--BETWEEN clause
SELECT * FROM dbo.EmployeeMaster FOR SYSTEM_TIME BETWEEN '2015-08-22 10:40:05' AND '2015-08-22 10:40:06' WHERE BusinessEntityID = 2

Below is the output:

We can see that BETWEEN clause qualified the records which became active on the exact given parameter end_datetime, whereas FROM clause excluded the record (highlighted with red square).

4. CONTAINED IN (start_datetime, end_datetime)

CONTAINED clause returns the records which qualify the below condition:

row_starttime >= start_datetime AND row_endtime  <= end_datetime

Above condition qualifies all the records having row start date time exactly on the given parameter start_datetime and row end date time exactly on the given parameter end_datetime.

CONTAINED clause returns all the row versions of each record which are valid exactly within the given time frame (inclusive of lower and upper boundary date time value).

SELECT * FROM dbo.EmployeeMaster FOR SYSTEM_TIME CONTAINED IN('2015-08-22', '9999-12-31 23:59:59') WHERE BusinessEntityID = 2

Below is the output:

Conclusion

We can use the temporal tables to store and retrieve time varying data. It can also be used in JOINS as a regular table, we have an example for the same during the discussion of “AS OF” sub clause. SQL Server 2016 has introduced FOR SYSTEM_TIME clause, which has four new sub clauses to query temporal tables. Below table summarizes the row qualifying criteria of each sub clause we discussed above:

Thanks for the reading and please do share and rate this post. Also input your valuable feedback in comment section of the post.

Reference: https://msdn.microsoft.com/en-us/library/dn935015.aspx