Retrieving all temporal data

Since temporal data is separated into two tables, to get all temporal data you need to combine data from both tables, but there is no sub-clause defined in the SQL:2011 standard for that purpose. However, the SQL Server team has introduced the extension (sub-clause) ALL to simplify such queries.

Here is a temporal query that returns both actual and historical data for the person with the ID of 7:

SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo FROM Application.People FOR SYSTEM_TIME ALL WHERE PersonID = 7; 

The query returns 14 rows, since there are 13 historical rows and one entry in the actual table. Here is the logically equivalent, standard, but a bit more complex query:

SELECT PersonID, FullName, ...

Get SQL Server 2017 Developer's Guide now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.