CONTAINED IN (@start, @end) VS BETWEEN - temporal tables

user_1683270 0
Viewed 1163

Temporal Tables is a new feature in SQL Server 2016 and above , it is like an interesting way for SQL Server to automatically keep history of data in a table, However there are some new T-SQL syntax introduced in SQL Server 2016 to support Temporal Tables. I faced misunderstanding of some of them.

From official documentation the description of BETWEEN <start_date_time> AND <end_date_time> is as next:

Same as above in the FOR SYSTEM_TIME FROM TO description, except the table of rows returned includes rows that became active on the upper boundary defined by the endpoint.

and the description of CONTAINED IN (<start_date_time>, <end_date_time>) is as next:-

Returns a table with the values for all row versions that were opened and closed within the specified time range defined by the two datetime values for the CONTAINED IN argument. Rows that became active exactly on the lower boundary or ceased being active exactly on the upper boundary are included.

so I understood, the only Different between CONTAINED IN (@start,@end) and BETWEEN clause is the CONTAINED IN (@start, @end) is including the @start boundary and BETWEEN clause is not. Am I right?

temporal-tables sql-server-2016 sql-server
1 Answers

In this article, @Ameena Lalani explains and demonstrates the various options to search a temporal table are explained very well, make sure you read the whole article.

To briefly summarize:

  • FROM <start_datetime> TO <end_datetime>
    returns a result combing the temporal and history table, excluding the upper-boundary of the end-time.
  • BETWEEN <start_datetime> AND <end_datetime>
    Returns a combined result from both the temporal and history tables including the upper-boundary of the end-time.
  • CONTAINED IN (start_datetime, end_datetime)
    Returns results only from the history table and includes both the upper and lower boundaries of times.

Here's a screenshot taken from that article:

Shimmy_755000
Related