Microsoft® SQL Server 2012 Unleashed

Using Multiple Indexes

SQL Server allows the creation of multiple indexes on a table. If a query has multiple SARGs that can each be efficiently searched using an available index, the Query Optimizer in SQL Server can make use of multiple indexes by intersecting the indexes or using the index union strategy.

Index Intersection

Index intersection is a mechanism that allows SQL Server to use multiple indexes on a table when you have two or more SARGs in a query and each can be efficiently satisfied using an index as the access path. Consider the following example:

Click here to view code image

SELECT SalesOrderID, OrderDate    FROM Sales.SalesOrderHeader    WHERE SalesPersonID = 276      AND CustomerID = 30107

In this example, the Query Optimizer ...