sql-server - 包含在 (@start, @end) VS BETWEEN - 时态表中

Question

Temporal Tables 是 SQL Server 2016 中的一项新功能以上，对于SQL Server来说是一种有趣的方式自动将数据历史记录保存在表中，但是在 SQL Server 2016 中引入了一些新的 T-SQL 语法支持临时表。我面临着对其中一些人的误解。

来自 official documentation BETWEEN <start_date_time> AND <end_date_time>的描述如下:

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.

和CONTAINED IN (<start_date_time>, <end_date_time>)的描述如下:-

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.

所以我明白了，CONTAINED IN (@start,@end)之间唯一的不同和 BETWEEN子句是 CONTAINED IN (@start, @end)包括@start 边界和BETWEEN子句不是。我说得对吗？

Answer 1

在this文章，@Ameena Lalani解释和演示搜索时态表的各种选项解释得很好，请务必阅读整篇文章。

简单总结一下:

• FROM <start_datetime> TO <end_datetime>
  返回结合时态表和历史表的结果，不包括结束时间的上限。
• BETWEEN <start_datetime> AND <end_datetime>
  返回时间表和历史表的组合结果，包括结束时间的上限。
• CONTAINED IN (start_datetime, end_datetime)
  仅从历史表返回结果，包括时间的上限和下限。

这是从那篇文章中截取的屏幕截图: