SELECT command

In this article, I present the last command SELECT.
I divide my article into 4 parts to be clearer and easier to read:
Part I – Principle & Creation
Part II – INSERT, UPDATE & DELETE commands
Part III – SELECT command
Part IV – Maintenance & Metadata

SQL Server version/built used for this serie of articles is CTP 2.2/13.0.407.1

SELECT Command

In my sample, I search information of the number of African wild cat in the zoo.
I begin with a simple sample in my table:

SELECT * from [Zoo].[dbo].[Animals] WHERE Name='African wild cat'

temporal16
The associate query plan, indicates that a use of the clustered Index from my table without the history table. It is very important to know this for the next steps.
temporal17
I have a look on the history table with a simple select on it:
temporal18
And the query plan show the simple select on this table:
temporal19
For the select command, you have 4 options to retrieve historical data.
I go through these 4 options….

FOR SYSTEM_TIME AS OF …

The first one is

FOR SYSTEM_TIME AS OF "date_time"

In my sample, you can see that this option gives the number of ‘African wild cat’ on a specific date.
To be sure, I test between all updates.
temporal20
Remarks: I have never joined the history table to my table. The join is automatically included in the SELECT query.
Have a look on the query plan.
In this case, I search information stored on both table:
temporal30
In this case, I search the current information and I don’t need to search in the History table but…
temporal31
With this option, the SELECT command go through both tables.

FOR SYSTEM_TIME FROM … TO …

The second is

FOR SYSTEM_TIME FROM "start_date_time" TO "end_date_time"

You can see in my sample that you have all values between the periods that you enter
temporal21
I’m a real tester and I inverse dates…. In my mind, the result must give an error or no rows…
temporal22
In my 2 first queries, I have no response and no error message.
My last query returns the current value…
Whoa, I inverse the date and I have a result and no error message! It is good to know, isn’t it?

FOR SYSTEM_TIME BETWEEN … AND …

The third option is

FOR SYSTEM_TIME BETWEEN "start_date_time" AND "end_date_time"

temporal23

You can see in my sample that you have all values between the periods that you enter.

Like the second test, I inverse the dates…
temporal24

And I obtain the same result. No error or no message for the 2 first queries and the current result for my last query.

FOR SYSTEM_TIME CONTAINED IN (…,…)

The last option is

FOR SYSTEM_TIME CONTAINED IN ("start_date_time" , "end_date_time")

temporal25

In my first query, I have a result. But for the two others, I have no rows. Why?
In the Msdn page from Temporal Table and this option, you find the explanation “Records that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.” Also, the result is logic with my queries.

Select for a deleted Value

As you can see in the chapter delete in the Part II , I have deleted the 2015-08-26 the AnimalId “3” which are double in my table.
temporal36

CORE MESSAGE

  • The SELECT commands on a ‘System-Versioned’ Table have a classical query plan and don’t go through the history table.
  • But if you use the option FOR SYSTEM_TIME and one of the 4 options, the SELECT command join automatically the history table in all cases, even if it is not necessarily required.
  • For options “FOR SYSTEM_TIME BETWEEN … AND …” and “FOR SYSTEM_TIME FROM … TO …” when the dates are inversed, the result does not returned every time ‘no rows’.