Database Reference
In-Depth Information
Also, as shown in the missing index statements in the execution plan in Figure
18-15
, these two queries can
benefit from having indexes created on their tables. Creating test indexes like the following should satisfy the
requirements:
CREATE INDEX TestIndex1
ON Person.Person (MiddleName);
CREATE INDEX TestIndex2
ON Person.Person (FirstName);
When the queries are reexecuted, Figure
18-17
shows the resultant execution plan for the two
SELECT
statements.
Figure 18-17.
Effect of the IS NULL option being used
As shown in Figure
18-17
, the optimizer was able to take advantage of the index
TestIndex2
on the
Person.FirstName
column to get a nice clean
Index Seek
operation. Unfortunately, the requirements for processing
the
NULL
columns were very different. The index
TestIndex1
was not used in the same way. Instead, three constants
were created for each of the three criteria defined within the query. These were then joined together through the
Concatenation
operation, sorted and merged prior to scanning the index three times through the
Nested Loop
operator to arrive at the result set. Although it appears, from the estimated costs in the execution plan, that this was
the less costly query (42 percent compared to 58 percent),
STATISTICS 10
and
TIME
tell the more accurate story,
which is that the
NULL
queries were more costly.
Table 'Person'. Scan count
2
, logical reads 66 CPU time = 0 ms, elapsed time = 126 ms.
vs.
Table 'Person'. Scan count
3
, logical reads 42 CPU time = 0 ms, elapsed time = 137 ms.
