We have discussed what is a temporal table, how we can create it, and what are the limitations of a temporal table. We have also discussed how we can convert an existing table to a temporal table in our previous posts.
In this post “querying temporal table data” we are going to learn how to query the time varying data from a temporal table.
In SQL Server 2016, we have a new clause FOR SYSTEM_TIME, which has four new sub clauses to query temporal table data:
- AS OF <datetime>
- FROM <start_datetime> TO <end_datetime>
- BETWEEN <start_datetime> AND <end_datetime>
- CONTAINED IN (start_datetime, end_datetime)
Let’s create a temporal table with dummy data to query with FOR SYSTEM_TIME clause and its sub clauses with examples. Below script is used to create a temporal table and to insert few dummy rows in the table with SQL Server default datetime value (1900-01-01) … More