1031
Chapter 45: Indexing Strategies
45
Query Path 10 — Non-SARG-Able Expressions
SQL Server’s Query Optimizer examines the conditions within the query’s predicatesto
determine which indexes are useful. If SQL Server can optimize the criteria statements,
such as a WHERE clause, using an index, the condition is referred to as a search argument
(SARG). However, not every condition is a “SARG-able” search argument:The fi nal query path walks through a series of antipatterns, designing WHERE clauses with
conditions that can’t use index seek operations for one or more reasons. The result is an
index scan, when an index seek is more advantageous. The following is a list of common
types of “non-SARG-able” expressions:■ (^) Including the table search column in an expression forces SQL Server to evaluate
the outcome of the expression for every row before it can determine if the row
passes the WHERE clause criteria:
SELECT WorkOrderID
FROM Production.WorkOrder
WHERE ProductID + 2 = 759;
■ (^) The solution to this non-SARG-able issue is to rewrite the query so that the expres-
sion is no longer dependent on the table column.
SELECT WorkOrderID
FROM Production.WorkOrder
WHERE ProductID = 759 - 2;
■ Multiple inclusive criteria is typically SARG-able; however, the optimizer may have
a more diffi cult time creating a seekable plan with criteria composed of OR logic.
SELECT WorkOrderID, StartDate
FROM Production.WorkOrder
WHERE ProductID = 757
OR StartDate = '2006-01-04';
■ Negative search conditions (<>, !>, !<, Not Exists, Not In, Not Like) are not
easily optimized. It’s easy to prove that a row exists, but to prove it doesn’t exist
requires examining every row.
SELECT WorkOrderID, StartDate
FROM Production.WorkOrder
WHERE ProductID NOT IN (400,800, 950);
It is possible that exclusive criteria can be SARG-able, so it’s worth testing. Often,
it’s the number of rows returned that forces a scan, not the exclusive criteria.
■ Search predicates that begin with wildcards aren’t SARG-able. An index can quickly
locate WorkOrderID = 757, but must scan every row to fi nd any WorkOrderID’s
ending in 7 :
SELECT WorkOrderID, StartDate
FROM Production.WorkOrder
WHERE WorkOrderID like '%7';
c45.indd 1031c45.indd 1031 7/31/2012 10:16:43 AM7/31/2012 10:16:43 AM
http://www.it-ebooks.info