Mastering SQL Server 2017

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