Sargable

In relational databases, a condition (or predicate) in a query is said to be sargable if the DBMS engine can take advantage of an index to speed up the execution of the query. The term is derived from a contraction of Search ARGument ABLE.

A query failing to be sargable is known as Non-Sargable query and has an effect in query time, so one of the steps in query optimization is convert them to be sargable.

The typical thing that will make a sql query non-sargable is to include in the WHERE clause, a function operating on a column value. Note that the WHERE clause is not the only clause where sargability can matter; it can also have an effect on ORDER BY, GROUP BY and HAVING clauses. The SELECT clause, on the other hand, can contain non-sargable expressions without adversely affecting the performance to retrieve and return the results.

• Sargable operators: =,>,<,>=,<=,BETWEEN,LIKE without leading %
• Sargable operators that rarely improve performance: <>,IN,OR,NOT IN, NOT EXISTS, NOT LIKE
• Non-sargable operators: LIKE with leading %

Rules of thumb

• Avoid functions using table values in a sql condition.
• Avoid non-sargable predicates and replace them with sargable equivalents.

Examples

• Non-Sargable: Select ... WHERE Year(date) = 2012

• Sargable: Select ... WHERE date >= '01-01-2012' AND date < '01-01-2013'

• Non-Sargable: Select ... WHERE isNull(FullName,'John Smith') = 'John Smith'

• Sargable: Select ... WHERE ((FullName = 'John Smith') OR (FullName IS NULL))

• Non-Sargable: Select ... WHERE SUBSTRING(DealerName,6) = 'Toyota'

• Sargable: Select ... WHERE DealerName Like 'Toyota%'

• Non-Sargable: Select ... WHERE DateDiff(mm,Date,GetDate()) >= 20

• Sargable: Select ... WHERE Date < DateAdd(mm,-20,GetDate())

References

• SQL Performance Tuning by Peter Gulutzan, Trudy Pelzer (Addison Wesley, 2002) ISBN 0-201-79169-2 (Chapter 2, Simple "Searches")

See also

• Query optimizer