||This article may require cleanup to meet Wikipedia's quality standards. The specific problem is: Article is MS SQL-specific, other databases have slightly varying behaviors.. (November 2014)|
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 on query time, so one of the steps in query optimization is to convert them to be sargable.
The typical situation that will make a SQL query non-sargable is to include in the WHERE clause a function operating on a column value. 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.
- 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 wildcards
Rules of thumb
- Avoid functions using table values in an SQL WHERE condition.
- Avoid non-sargable predicates and replace them with sargable equivalents.
Find date values in a certain year:
- Non-sargable: SELECT ... WHERE EXTRACT(YEAR FROM date) = 2012
- Sargable: SELECT ... WHERE date >= '2012-01-01' AND date < '2013-01-01'
- Non-sargable: SELECT ... WHERE COALESCE(FullName, 'John Smith') = 'John Smith'
- Sargable: SELECT ... WHERE (FullName = 'John Smith') OR (FullName IS NULL)
String prefix search:
- Non-sargable: SELECT ... WHERE SUBSTRING(DealerName FROM 1 FOR 6) = 'Toyota'
- Sargable: SELECT ... WHERE DealerName LIKE 'Toyota%'
Find rows from last 20 days:
- Non-sargable: SELECT ... WHERE EXTRACT(DAY FROM (CURRENT_DATE - date)) < 20
- Sargable: SELECT ... WHERE date >= (CURRENT_DATE - INTERVAL '20' DAY)
|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")
- Microsoft SQL Server 2012 Internals by Kalen Delaney, Connor Cunningham, Jonathan Kehayias, Benjamin Nevarez, Paul S. Randal (O'Reily, 2013) ISBN 978-0-7356-5856-1 (Chapter 11, The Query Optimizer)