Microsoft SQL Server 2008 R2 Unleashed- P130:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1234 CHAPTER 35 Understanding Query Optimization When the index union strategy is used on a heap table such as the sales_noclust table you see a query plan similar to the one shown in Figure . Notice that the merge join is replaced with a concatenation operation and the stream aggregate is replaced with distinct sort operation. Although the steps are slightly different from the index intersection strategy the result is similar a list of unique RIDs is returned and they are used to retrieve the matching data rows in the table itself. FIGURE An execution plan for an index union strategy on a heap table. When the OR in the query involves only a single column and a nonclustered index exists on the column the Query Optimizer in SQL Server 2008 typically resolves the query with an index seek against the nonclustered index and then a bookmark lookup to retrieve the data rows. Consider the following query select from sales where ord_date in 6 15 2005 9 28 2008 6 25 2008 This query is the same as the following select from sales where ord_date 6 15 2005 or ord_date 9 28 2008 or ord_date 6 25 2008 To process this query SQL Server performs a single index seek that looks for each of the search values and then joins the list of bookmarks returned with either the clustered index or the RIDs of the target table. No removal of duplicates is necessary because each OR condition matches a distinct set of rows. Figure shows an example of the query plan for multiple OR conditions against a single column. Index Joins Besides using the index intersection and index union strategies another way of using multiple indexes on a single table is to join two or more indexes to create a covering index. This is similar to an index intersection except that the final bookmark lookup is not required because the merged index rows contain all the necessary information. Consider the following example Download from Row Estimation and Index Selection 1235 FIGURE An .
