Optimizing temporal queries

The problem with temporal queries is that when reading from a table, SQL Server can use only one index, successfully eliminate rows that are not candidates for the result from one side only, and then scan the rest of the data. For example, you need to find all intervals in the table that overlap with a given interval. Remember, two intervals overlap when the beginning of the first one is lower than or equal to the end of the second one, and the beginning of the second one is lower than or equal to the end of the first one, or mathematically when (b1 ≤ e2) AND (b2 ≤ e1).

The following query searches for all of the intervals that overlap with the interval (10, 30). Note that the second condition (b2 ≤ e1) is turned ...

Get Mastering SQL Server 2017 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.