Using Index Intersection to Boost SQL Server Performance

Without Index Intersection Imagine you have a table with two columns that you search on regularly as a pair, for example:
USE PUBS
GO SELECT *
FROM authors
WHERE au_fname = ‘Akiko’ AND au_lname = ‘Yokomoto’
The Pubs database has a non-clustered compound index (i.e. an index with more than one column) on this table, which suits this query perfectly because the index is defined on the columns au_lname and au_fname, in that order. SQL Server will use this index to return results for this query. The ordering of the indexes columns is important because to use a compound index, the leftmost column of the index must be considered in the WHERE clause (or the JOIN clause of a multi-table query) Because of this, SQL Server will handle the following two queries in different ways.
SELECT *
FROM  authors
WHERE au_lname = ‘Yokomoto’ SELECT *
FROM authors
WHERE au_fname = ‘Akiko’
The first query from this pair will use the same index to search the table as the first example, because au_lname is the first column defined in the index. However, SQL Server cannot use the same index for the second query, because au_fname is not leftmost in the index definition, and so the optimizer will pick another execution plan, or do a full table scan (au_fname is normally not indexed). You can prove this to yourself by running these queries in Query Analyzer and displaying an execution plan.