Databases Reference
In-Depth Information
a multi-column index to seek on both columns in the following cases, supposing that a
multi-column index exists on both columns in the order presented:
• ProductID = 771 AND SalesOrderID > 34000
• LastName = 'Smith' AND FirstName = 'Ian'
That being said, if there is no equality predicate on the first column, or if the predicate
can not be evaluated on the second column, as is the case in a complex expression, then
SQL Server may still only be able to use a multi-column index to seek on just the first
column, as in the following examples:
• ProductID = 771 AND ABS(SalesOrderID) = 34000
• ProductID < 771 AND SalesOrderID = 34000
• LastName > 'Smith' AND FirstName = 'Ian'
However, SQL Server is
not
able to use a multi-column index for an Index Seek in the
following examples, as it is not even able to search on the first column:
• ABS(ProductID) = 771 AND SalesOrderID = 34000
• LastName LIKE '%Smith' AND FirstName = 'Ian'
Finally, take a look at the following query, and the Index Seek operator properties in
Figure 4-5.
SELECT
ProductID
,
SalesOrderID
,
SalesOrderDetailID
FROM
Sales
.
SalesOrderDetail
WHERE
ProductID
=
771
AND
ABS
(
SalesOrderID
)
=
45233
Listing 4-5.
