Microsoft® SQL Server 2008 R2 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:

In this example, two additional nonclustered indexes are created on the sales table: ...