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 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:
SELECT SalesOrderID, OrderDate FROM Sales.SalesOrderHeader WHERE SalesPersonID = 276 AND CustomerID = 30107
In this example, the Query Optimizer ...