Database Reference
In-Depth Information
All three queries return the same results. On the face of it, the execution plan of all three queries appear to be the
same, as shown in Figure
18-3
.
Figure 18-3.
Execution plan for a simple
SELECT
statement using a
BETWEEN
clause
However, a closer look at the execution plans reveals the difference in their data-retrieval mechanism, as shown
in Figure
18-4
. The top box is the
IN
condition, and the bottom box is the
BETWEEN
condition.
Figure 18-4.
Execution plan details for an
IN
condition (top) and a
BETWEEN
condition (bottom)
As shown in Figure
18-4
, SQL Server resolved the
IN
condition containing four values into four
OR
conditions.
Accordingly, the clustered index (
PKSalesTerritoryTerritoryld
) is accessed four times (
Scan count 4
) to retrieve
rows for the four
IN
and
OR
conditions, as shown in the following corresponding
STATISTICS 10
output. On the other
hand, the
BETWEEN
condition is resolved into a pair of
>=
and
<=
conditions, as shown in Figure
18-4
. SQL Server
accesses the clustered index only once (
Scan count 1
) from the first matching row until the match condition is true,
as shown in the following corresponding
STATISTICS 10
and
QUERY TIME
output.
