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

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

SQL Server 2016 T-SQL Syntax to Query Temporal Tables


By:   |   Read Comments (11)   |   Related Tips: 1 | 2 | 3 | 4 | More > 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.

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. Lets 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:


next webcast 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    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, August 08, 2018 - 5:40:12 AM - Koen Verbeeck Back To Top

Hi Dhanraj,

maybe take a look at SQL Server Audit?

You can also create a trigger on your table that logs all insert/update/delete information into an audit table. See also this thread: https://dba.stackexchange.com/questions/161331/how-to-find-who-changed-a-table-value-in-sql-server-2014


Tuesday, August 07, 2018 - 3:43:27 AM - Dhanraj Back To Top

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?


Tuesday, May 08, 2018 - 8:04:49 AM - Yuri Back To Top

Hi Koen,
thank you for your reply and for clarification on data boundaries.

Respectfully,
Yuri


Monday, May 07, 2018 - 3:06:48 AM - Koen Verbeeck Back To Top

 

Hi Yuri,

glad the article was helpful to you.
Regarding the upper/lower boundary: I think it is meant to say that the upper boundary is included, since the point in time B is included (where B is the upper boundary), meaning if the start or end date of a time frame is equal to B, it will be returned in the result set. (as illustrated in the screenshot, where an extra 4th row is returned).

The other sentence should be 

"...there is a subtle difference since the BETWEEN operator includes both boundaries, while the temporal BETWEEN sub-clause only includes the upper boundary."


Sunday, May 06, 2018 - 9:50:58 AM - Yuri Leventman Back To Top

Hi Koen,

this is a very nice article with great illustrations which make it easy to understand the subject at hand.
Thank you.

Would you please clarify if the below was correct?
"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."

Should not it be rather "lower boundary", since we have this predicate for BETWEEN A AND B
StartDate ≤ B AND EndDate > A
and thus it "
results in an extra version being returned."?

Especially considering that you wrote the following later:
"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."

Thank you,
Yuri


Sunday, February 25, 2018 - 8:26:09 AM - Pascal Schirrmann Back To Top

Hi Koen, you just saved my day ! I plan to use temporal database for tracking changes, but I also want to add my historical changes, and before I came here, I was thinking that this is not possible !

Thanks,

Pascal


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