Querying Temporal Tables

One of the major benefits of temporal/versioning tables is that we can query and see how data in table looked at any given point in time. In this article, let’s see how to query this data.

There are five clauses we can use to query versioned data.

• AS OF <date_time>
• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time> , <end_date_time>)
• ALL

Demo

To get started I am going to set up a temporal/versioning table dbo.TBM_RATE_FEE_HISTORY for the base table [dbo].[TBM_RATE_FEE] as shown below.

-- TBM_RATE_FEE
ALTER TABLE [dbo].[TBM_RATE_FEE] ADD
StartTime DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN DEFAULT GETUTCDATE(),
EndTime DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
PERIOD FOR SYSTEM_TIME (StartTime,EndTime)
GO
-- TBM_RATE_FEE_HISTORY
ALTER TABLE [dbo].[TBM_RATE_FEE]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TBM_RATE_FEE_HISTORY));
GO

Let’s perform few update operations to generate some history in the table which we can use in our demos to understand how querying works.

UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 410.0000000000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2786
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 420.0000000000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2786
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 430.0000000000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2786
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 440.0000000000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2786
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 10.0000000000
,[MK_PCNT] = 1.0000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2784
GO
WAITFOR DELAY '00:05:05';
GO
UPDATE [dbo].[TBM_RATE_FEE]
set
[RATE] = 20.0000000000
,[MK_PCNT] = 2.0000
,[LAST_MODIFIED] = GETDATE()
,[END_DATE] = GETDATE()+365
where ROW_UNO = 2784
GO

Using AS OF

Use the AS OF sub-clause when you need to see the state of data as it was at any specific date and time in the past. The AS OF sub-clause clause can be used with constant literals or with variables, which allows you to dynamically specify time condition. The values provided are interpreted as UTC time.

So, we want to see how the data looked at a given point in time 2019-08-21 22:00:00.000, before we did any changes.

/*State of data AS OF specific date in the past*/
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED]
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME AS OF '2019-08-21 22:00:00.000'
 WHERE ROW_UNO IN (2784,2786)

Let’s look at execution plan for this query and see what exactly it is doing under the hood.

If we right click and get the predicate for first table TBM_RATE_FEE – it looks like this.

[Expert].[dbo].[TBM_RATE_FEE].[StartTime]<=’2019-08-21 22:00:00.0000000′ AND [Expert].[dbo].[TBM_RATE_FEE].[EndTime]>’2019-08-21 22:00:00.0000000′

The TBM_RATE_FEE_HISTORY history table’s predicate looks like below.

[Expert].[dbo].[TBM_RATE_FEE_HISTORY].[StartTime]<=’2019-08-21 22:00:00.0000000′ AND ([Expert].[dbo].[TBM_RATE_FEE_HISTORY].[ROW_UNO]=(2784) OR [Expert].[dbo].[TBM_RATE_FEE_HISTORY].[ROW_UNO]=(2786))

SQL Server is combining data with start time less than or equal to passed date from main table with history table for start date less than date time passed for this key.

Using FROM <> TO

Below is an example of a query that uses a date range instead of ‘as of’ a specific date.

/* Query using FROM...To sub-clause*/
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED]
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME FROM '2019-08-21 22:00:00.000' TO '2019-08-21 22:06:50.1872483'
 WHERE ROW_UNO IN (2784,2786)

The following criteria must be fulfilled:

StartTime < <end_date_time> AND EndTime > <start_date_time>

Using BETWEEN …AND 

/* Query using BETWEEN...AND sub-clause*/
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED],StartTime,EndTime
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME BETWEEN '2019-08-21 22:00:00.000' AND '2019-08-21 22:06:50.1872483'
 WHERE ROW_UNO IN (2784,2786)

The following criteria must be fulfilled:

StartTime <= <end_date_time> AND EndTime > <start_date_time>

The between clause, therefore, returns data that was changed after or equal to start date and is valid beyond the end date.

Using CONTAINED IN  

/*  Query using CONTAINED IN sub-clause */ 

 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED],StartTime,EndTime
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME CONTAINED IN ('2019-08-21 22:00:00.000' , '2019-08-21 22:06:50.1872483')
 WHERE ROW_UNO IN (2784,2786)

CONTAINED IN returns only those that existed within specified period boundaries.

The following criteria must be fulfilled:

StartTime >= <start_date_time> AND EndTime <= <end_date_time>

The temporal sub-clauses FROM…TO, BETWEEN…AND and CONTAINED IN are useful when you want to perform a data audit, i.e. when you need to get all historical changes for a specific row in the current table.

Using ALL

/*  Query using ALL sub-clause */ 
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED],StartTime,EndTime
 FROM [dbo].[TBM_RATE_FEE] 
 FOR SYSTEM_TIME ALL
 WHERE ROW_UNO IN (2784,2786)

ALL returns both the current and historical data without any restrictions. Both tables are being scanned to pull all this data. This can be an expensive query.

If you search for non-current row versions (historical data changes) only, then we recommend you query the history table directly as this will yield the best query performance as shown below.

  /*  Query historical data changes only */ 
 SELECT ROW_UNO,[RATE],[MK_PCNT],[END_DATE],[LAST_MODIFIED],StartTime,EndTime
 FROM [dbo].[TBM_RATE_FEE_HISTORY] 
 WHERE ROW_UNO IN (2784,2786)

References

https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-2017