When you want to retrieve temporal data that was valid at a given point in time, the resulting set could contain both actual and historical data. For instance, the following query would return all rows from the People temporal table in the WideWorldImporters sample database that were valid at 20th May 2013 at 8 A.M.:

SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo
FROM Application.People WHERE ValidFrom <= '20130520 08:00:00' AND ValidTo > '20130520 08:00:00'
UNION ALL 
SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo
FROM Application.People_Archive WHERE ValidFrom <= '20130520 08:00:00' AND ValidTo > '20130520 08:00:00'; 

The query returns 1,060 rows. For a single person, only one row is returned: either the actual or a historical row. A row is valid if its start date was before or exactly on the given date and its end date is greater than the given date.

The new FOR SYSTEM_TIME clause with the AS OF sub-clause can be used to simplify the preceding query. Here is the same query with temporal Transact-SQL extensions:

SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo
FROM Application.People FOR SYSTEM_TIME AS OF '20130520 08:00:00'; 

Of course, it returns the same result set and the execution plans are identical, as shown in the following screenshot:

Under the hood, the query processor touches both tables and retrieves data, but the query with temporal extensions looks simpler.

The special case of a point-in-time query against a temporal table is a query where you specify the actual date as the point in time. The following query returns actual data from the same temporal table:

DECLARE @Now AS DATETIME = SYSUTCDATETIME(); 
SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo 
FROM Application.People FOR SYSTEM_TIME AS OF @Now; 

The previous query is logically equivalent to this one:

SELECT PersonID, FullName, OtherLanguages, ValidFrom, ValidTo FROM Application.People; 

However, when you look at the execution plans (see the following screenshot) for the execution of the first query, both tables have been processed, while the non-temporal query had to retrieve data from the current table only:

So, you should not use temporal queries with the FOR SYSTEM_TIME AS clause to return data from the current table.