Database Reference
In-Depth Information
You now see the columns added up, all three of the index key columns, and finally the primary key added on.
Instead of a width of 22 bytes, it's grown to 74. That reflects the addition of the JobTitle column, a VARCHAR(50) as
well as the 6-byte-wide datetime field.
Finally, looking at the statistics for the second alternate index, with the included columns you'll see the output in
Figure 11-14 .
CREATE UNIQUE NONCLUSTERED INDEX [AK_Employee_NationalIDNumber]
ON [HumanResources].[Employee]
(NationalIDNumber ASC )
INCLUDE (JobTitle,HireDate)
WITH DROP_EXISTING ;
Figure 11-14. DBCC SHOW_STATISTICS output for a covering index using INCLUDE
Now the key width is back to the original size because the columns in the INCLUDE statement are stored not with
the key but at the leaf level of the index.
There is more interesting information to be gleaned from the data stored about statistics, but I'll cover that in Chapter 12.
Using an Index Join
If the covering index becomes very wide, then you might consider an index join technique. As explained in Chapter 9,
the index join technique uses an index intersection between two or more indexes to cover a query fully. Since the index
join technique requires access to more than one index, it has to perform logical reads on all the indexes used in the
index join. Consequently, it requires a higher number of logical reads than the covering index. But since the multiple
narrow indexes used for the index join can serve more queries than a wide covering index (as explained in Chapter 9),
you can certainly consider the index join as a technique to avoid lookups.
To better understand how an index join can be used to avoid lookups, run the following query against the
PurchaseOrderHeader table in order to retrieve a PurchaseOrderID for a particular vendor on a particular date:
SELECT poh.PurchaseOrderID,
poh.VendorID,
poh.OrderDate
FROM Purchasing.PurchaseOrderHeader AS poh
WHERE VendorID = 1636
AND poh.OrderDate = '12/5/2007' ;
When run, this query results in a Key Lookup operation (Figure 11-15 ) and the following I/O:
Table 'Employee'. Scan count 1, logical reads 10
CPU time = 15 ms, elapsed time = 19 ms.
 
Search WWH ::




Custom Search