Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server 2016 T-SQL Syntax to Query Temporal Tables


By:   |   Read Comments (5)   |   Related Tips: 1 | 2 | 3 | 4 | More > SQL Server 2016

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

Timeline of different versions

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)
);


Migrated temporal table

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).

Using AS OF

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

Using AS OF - query result

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.

Using AS OF - execution plan

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:

Using AS OF - selecting a boundary

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.

Using FROM TO - timeline

Version 2, 3 and 4 will be returned.

Using FROM TO - query results

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:

Using FROM TO - same point in time

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.

Using BETWEEN AND

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.

Using CONTAINED IN - timeline
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.

Using CONTAINED IN - query results

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


Last Update:


signup button

next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 08, 2017 - 3:49:59 AM - Mohamed Back To Top

Nice Article. Thank You.
But, IMHO, SQL Server made unnecessary complication with these new clauses.. why couldnt be handled just with >, >=, <, <= , =


Thursday, July 28, 2016 - 3:35:02 AM - Koen Verbeeck Back To Top

Hi Channdeep Singh,

can you please post the query you're using?

Thanks,

Koen


Saturday, July 23, 2016 - 5:34:39 PM - Channdeep Singh Back To Top

Dear Sir- Getting "multipart identifier could not be found" error when using table name or its alias in WHERE clause. Could you suggest a workaround please ? Thanks and regards.

 


Thursday, August 06, 2015 - 5:09:52 PM - JaY Back To Top

Amaizing this can be a game changer for few critical tables which always need historic data to be archived or cleaned. thanks a lot for explaining with examples great effort made,


Tuesday, August 04, 2015 - 10:55:06 AM - Gustavo Maia Back To Top

Great new tip! Very clear examples of the new "temporal" clauses! Nice ilustrations too!

Keep up the good work!


Learn more about SQL Server tools