Hello Friends,

In my previous blog posts, we have seen multiple ways for the creation of temporal tables and DML operation impact. Today we will have a look at Query Time Based Data on Temporal Table. First, let me create the table and then I’ll perform some DML operations, so that can query the data.

Now lets take a look at all the records of Temporal table and historical data:

Temporal Table 1

To see the data in the tables at a specific time (I am specifying the time when the record was  updated). You can see that record for EmpID 1002 was deleted, so it has been retrieved from historical table:

Temporal Table 2

Temporal Table 3

In the above section we have seen the data at a specific time. We can also find out the details of a specific record between a date time range:

Temporal Table 4

From the above output, you can see that first record is from the current table while the rest of the records from historical table (which is before the update operation). If you want to see only historical records, then you can use CONTAINED IN as mention below. It will fetch the records only from historical table.

Temporal Table 5

From the above output, you can see that we are getting only a single record that is historical record not the current record. We can use ALL if we want to see all the data from current and historical table:

Temporal Table 6


Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook