Databases Reference
In-Depth Information
An index cannot be used to seek on some complex expressions, expressions using
functions, or strings with a leading wildcard character, as in the following predicates:
• ABS(ProductID) = 771
• UnitPrice + 1 < 3.975
• LastName LIKE '%Allen'
• UPPER(LastName) = 'Allen'
Compare the following query to the previous example; by adding an
ABS
function to
the predicate, SQL Server is no longer able to use an Index Seek operator and chooses,
instead, to do an Index Scan as shown on the plan in Figure 4-3.
SELECT
ProductID
,
SalesOrderID
,
SalesOrderDetailID
FROM
Sales
.
SalesOrderDetail
WHERE
ABS
(
ProductID
)
=
771
Listing 4-3.
Figure 4-3:
Plan with an Index Scan.
