Yesterday we did a review on Temporal tables. In short, a temporal table has two new columns added to it that are maintained by the database engine, instead of by the queries you write yourself. It maintains time segment columns with a beginning date/time and an ending Date/Time. You could add these columns yourself and maintain them. The difference here is that the columns, being a temporal table, are maintained on your behalf.
So, that’s really cool. It’s similar to having an identity column. SQL Server automatically maintains the value for you. You simply specify a column in your table having an Identity value, and the rest is done for you.
With the temporal columns, there are some new enhancements available to you in your query allowing you to easily select the specific time instance of the record you want. You can emulate these filters by rolling your own, but this implementation is a lot cleaner. When you create a temporal table two system managed columns are created called SysStartTime and SysEndTime. You may query these values, and include them in your output, but they are system managed, and read only to the user.
The way you handle temporal values is by using a new Clause in the SQL Statement. The clause is FOR and uses the SYSTEM_TIME value, represented by the time segment created in your table. Us the FOR SYSTEM_TIME syntax, you now have for new comparison methods
- AS OF
- BETWEEN AND
- FROM TO
- CONTAINED IN ()
- ALL
FOR SYSTEM_TIME AS OF [DateTimeValue] returns only one time segment for a record where the DateTimeValue is between the SysStartTime and SysEndTime
BETWEEN AND and FROM TO are similar. They compare the time segment in the table with a time segment in the comparison. They differ slightly. A good demonstration of how they differ may be found https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables.
The CONTAINED IN () method returns only those records where the time segment specified in CONTAINED IN is fully encapsulated in the record time secment. At most, only one version record will be returned.
Finally, the FOR ALL clause returns all versions of a record without any filtering.
This additional clause for temporal tables makes it much easier to understand what is intended by a query. By using the temporal table, and the temporal clauses, SQL Server knows where to go to retrieve your data and has optimized performance for different scenarios.
Cheers,
Ben