SQL Server 2016 T-SQL Syntax to Query Temporal Tables
By: Koen Verbeeck | Comments (11) | Related: 1 | 2 | 3 | 4 | More > Temporal Tables
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?
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.
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.
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!
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.
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.
- 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
View all my tips