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.
- 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())
||This article relies largely or entirely upon a single source. (April 2013)|
- SQL Performance Tuning by Peter Gulutzan, Trudy Pelzer (Addison Wesley, 2002) ISBN 0-201-79169-2 (Chapter 2, Simple "Searches")