SQL Server 2016 T-SQL Syntax to Query Temporal Tables

By: Koen Verbeeck | Comments (11) | Related: 1 | 2 | 3 | 4 | > Temporal Tables

Problem

I saw your first tip on SQL Server 2016 Temporal tables and they look like an interesting way for SQL Server to automatically keep history of data in a table. Is there any new T-SQL syntax introduced in SQL Server 2016 to support Temporal Tables? Can you provide examples on how to query data in SQL Server 2016 Temporal Tables?

Solution

Once we have created (or migrated) a temporal table and we have been modifying data for some time, we of course want to view all the historic changes of the data. This tip will explore all of the different methods you can use to view the history.

SQL Server 2016 Temporal Table Example

The following script will create two tables and insert data into it.

-- create history table
CREATE TABLE dbo.PriceHistory
	(ID		INT		NOT NULL
	,Product	VARCHAR(50)	NOT NULL
	,Price		NUMERIC(10,2)	NOT NULL
	,StartDate	DATETIME2	NOT NULL
	,EndDate	DATETIME2	NOT NULL
	);
GO

-- insert values for history
INSERT INTO dbo.PriceHistory(ID,Product,Price,StartDate,EndDate)
VALUES	 (1,'myProduct',1.15,'2015-07-01 00:00:00','2015-07-01 11:58:00')
		,(1,'myProduct',1.16,'2015-07-01 11:58:00','2015-07-03 12:00:00')
		,(1,'myProduct',1.18,'2015-07-03 12:00:00','2015-07-05 18:05:00')
		,(1,'myProduct',1.21,'2015-07-05 18:05:00','2015-07-07 08:33:00');

-- create current table to store prices
CREATE TABLE dbo.Price
	(ID		INT		NOT NULL
	,Product	VARCHAR(50)	NOT NULL
	,Price		NUMERIC(10,2)	NOT NULL
	,StartDate	DATETIME2	NOT NULL
	,EndDate	DATETIME2	NOT NULL
	,CONSTRAINT PK_Price PRIMARY KEY CLUSTERED  (ID ASC)
	);
GO

-- insert the current price (make sure start date is not in the future!)
INSERT INTO dbo.Price(ID,Product,Price,StartDate,EndDate)
VALUES	 (1,'myProduct',1.20,'2015-07-07 08:33:00','9999-12-31 23:59:59.9999999');

The script only creates rows for one product, in order to keep the example simple and easy to understand. This product will have 4 historical versions and 1 current version.

This will directly translate to 4 rows in the history table and 1 row in the main table. Now we will convert those two tables into one temporal table.

-- enable system period columns
ALTER TABLE dbo.Price
ADD PERIOD FOR SYSTEM_TIME (StartDate,EndDate);

-- turn on system versioning
ALTER TABLE dbo.Price SET (SYSTEM_VERSIONING = ON
	(HISTORY_TABLE=dbo.PriceHistory,DATA_CONSISTENCY_CHECK=ON)
);

Query SQL Server 2016 Temporal Tables

The SELECT ... FROM clause has a new clause in SQL Server 2016: FOR SYSTEM_TIME. This new clause also has 4 new temporal-specific sub-clauses. In the following sections we will discuss each one with an example.

AS OF Sub-Clause in SQL Server 2016

Using the AS OF sub-clause, you retrieve the version for each row that was valid at that specific point in time. Basically it allows you to time travel to a certain point in the past to see what state the table was in at that point.

For example:

SELECT * FROM dbo.Price
FOR SYSTEM_TIME AS OF '2015-07-04';

This query will retrieve the version that was valid on the 4th of July (midnight).

The third version will be returned, which has the price of 1.18.

Note that it's not necessary to know the name of the history table or how the temporal table is constructed. The use of the temporal table is transparent: you just query the table using the FOR SYSTEM_TIME clause and SQL Server handles all the rest. When looking at the actual execution plan, you can see SQL Server unions the main table and the history table together and filters for the requested rows.

What about the edge case where the AS OF point in time falls on a boundary? In other words, the specified point in time is equal to the end date of one version and the start date of the version that comes right after. Let's test it.

SELECT * FROM dbo.Price
FOR SYSTEM_TIME AS OF '2015-07-03 12:00:00';

This will return the most recent version:

Query FROM A TO B for SQL Server Temporal Table Data

This clause is functionally equivalent to the following construct:

StartDate < B AND EndDate > A

All historic and current rows that were at some point active in the time frame between A and B will be returned. The entire lifespan of such a record can be bigger than the time frame between A and B.

For example:

SELECT * FROM dbo.Price
FOR SYSTEM_TIME FROM '2015-07-02' TO '2015-07-06';

This query will return all versions that were at some point active in the time frame between the 2nd of July and the 6th of July.

Version 2, 3 and 4 will be returned.

What if A and B are the same points in time?

SELECT * FROM dbo.Price
FOR SYSTEM_TIME FROM '2015-07-06' TO '2015-07-06';

This will be the same as using AS OF. The versions valid on that specific point in time will be returned:

However, the behavior differs from AS OF when the point in time is on a boundary of two versions.

SELECT * FROM dbo.Price
FOR SYSTEM_TIME FROM '2015-07-03 12:00:00' TO '2015-07-03 12:00:00';

In this case, no records are returned!

Using FROM TO - same point in time on a boundary

BETWEEN A AND B Logic for SQL Server Temporal Tables

This sub-clause is functionally equivalent to the following construct:

StartDate ≤ B AND EndDate > A

In most cases this is also equivalent to FROM A TO B, except when the end date of the time frame (B) falls on a boundary. With BETWEEN A AND B, this boundary will be included. Let's take a look at the same example as in the previous section, but with the range extended to the starting point of the current version. In the example I compared the BETWEEN and the FROM TO sub-clause.

The difference between the two clauses is now very clear: BETWEEN A AND B includes the upper boundary of the time frame, which results in an extra version being returned. When A and B are the same, BETWEEN A AND B is the equivalent of AS OF.

Although similar to the BETWEEN operator used in the WHERE clause, there is a subtle difference since the BETWEEN operator includes both boundaries, while the temporal BETWEEN sub-clause only includes the lower boundary.

CONTAINED IN (A,B) Logic for SQL Server Temporal Tables

The last sub-clause has the following functional equivalent:

A ≤ StartDate AND EndDate ≤ B

It will only return versions of which the valid timespan is fully within the specified time range, boundaries included. Once a version crosses the boundary of the time range, it is included. Let¡¡¥s use the same example as in the previous sections.

SELECT * FROM dbo.Price
FOR SYSTEM_TIME CONTAINED IN ('2015-07-02','2015-07-06');

The query will now return only version 3, since it is the only version completely within the start and end point of the range. Version 2 and version 4 cross the boundaries, so they are excluded.

Having A and B the same would be rather pointless, since such a query would only return versions that had a lifetime of exactly one point in time.

Conclusion

The new FOR SYSTEM_TIME clause brings a lot of flexibility to SQL Server 2016 with regard to temporal tables. It has 4 different sub-clauses, each with a slightly different interpretation of how temporal data should be returned, allowing for powerful querying of system-versioned tables. If this new clause is not used in a query, a temporal table will only return the active rows from the main table.

Next Steps

Try it out yourself! Use the scripts from this tip to create a temporal table with sample data and familiarize yourself with the different nuances of the new sub-clauses.
For more information, check out the documentation of Temporal Tables and the FROM clause.
Be sure to check out part 1 of this tip about temporal tables.
The following tip gives an overview of all the new features in SQL Server 2016 CTP2.
For more SQL Server 2016, you can use this overview.

About the author

Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Tuesday, August 7, 2018 - 3:43:27 AM - Dhanraj	Back To Top (77037)
It is really an exciting feature. In my case, I would also need to track the user who updated/deleted the record along with action performed (update/delete) to show in my report for that recordid. How can i achieve this?

Thursday, June 8, 2017 - 3:49:59 AM - Mohamed	Back To Top (56964)
Nice Article. Thank You. But, IMHO, SQL Server made unnecessary complication with these new clauses.. why couldnt be handled just with >, >=, <, <= , =