Index Analysis - SQL Server Query Performance Tuning - page 146

Index Joins
The index join is a variation of index intersection, where the covering index technique is applied to the index intersection.
If no single index covers a query but multiple indexes together can cover the query, SQL Server can use an index
join to satisfy the query fully without going to the base table.
Let's look at this indexing technique at work. Make a slight modification to the query from the “Index
Intersections” section like this:
SELECT soh.SalesPersonID,
soh.OrderDate
FROM Sales.SalesOrderHeader AS soh
WHERE soh.SalesPersonID = 276
AND soh.OrderDate BETWEEN '4/1/2005' AND '7/1/2005';
The execution plan for this query is shown in Figure 9-5 , and the reads are as follows:
Table 'SalesOrderHeader'. Scan count 1, logical reads 689
CPU time = 0 ms, elapsed time = 55 ms.
Figure 9-5. Execution plan with no index join
As shown in Figure 9-5 , the optimizer didn't use the existing nonclustered index on the SalesPersonID column.
Since the query requires the value of the OrderDate column also, the optimizer selected the clustered index to retrieve
values for all the columns referred to in the query. If an index is created on the OrderDate column like this:
CREATE NONCLUSTERED INDEX IX_Test
ON Sales.SalesOrderHeader (OrderDate ASC);
and the query is rerun, then Figure 9-6 shows the result, and you can see the reads here:
Table 'SalesOrderHeader'. Scan count 2, logical reads 4
CPU time = 0 ms, elapsed time = 35 ms.
Figure 9-6. Execution plan with an index join