SQL Server 2016 temporal tables #2 - Following the standard
A colleague of mine was doing a presentation on the new SQL Server 2016 feature "temporal tables," and we talked about why it is called system-versioned and why Microsoft have chosen the DDL syntax. I decided to investigate a little, and found to my immense joy (!), that it all comes from the SQL:2011 standard.
First of all, the name "system-versioned (temporal) table" is taken from the standard, because the kind of temporal support implemented in SQL Server 2016 is just this. According to , any table that contains a period definition with the standard-specified name, SYSTEM_TIME, and includes the keywords WITH SYSTEM VERSIONING in its definition is a system-versioned table.
The syntax for creating a system-versioned temporal table is illustrated in the following DDL (temporal keywords are highligted in bold):
CREATE TABLE Department ( DeptID int NOT NULL PRIMARY KEY CLUSTERED /* ... other columns */ , SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime) ) WITH (SYSTEM_VERSIONING = ON) ;
From the example we can see, that the chosen keywords simply comply with the SQL:2011 standard.
Read more about system-versioned temporal tables in the SQL2011 standard in this paper:
 Kulkarni, Krishna, and Jan-Eike Michels. "Temporal features in SQL: 2011". ACM SIGMOD Record 41.3 (2012): 34-43: