||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 effect is similar to searching for a specific term in a book that has no index, beginning at page one each time, instead of jumping to a list of specific pages identified in an index.
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, IS [NOT] NULL
- Sargable operators that rarely improve performance:
<>, IN, OR, NOT IN, NOT EXISTS, NOT LIKE
Rules of thumb
- Avoid functions using table values in an SQL WHERE condition and in ORDER BY, GROUP BY or HAVING clauses.
- Avoid non-sargable predicates and replace them with sargable equivalents.
Find date values in a certain year:
SELECT ... WHERE EXTRACT(YEAR FROM date) = 2012
SELECT ... WHERE date >= '2012-01-01' AND date < '2013-01-01'
SELECT ... WHERE COALESCE(FullName, 'John Smith') = 'John Smith'
SELECT ... WHERE (FullName = 'John Smith') OR (FullName IS NULL)
String prefix search:
SELECT ... WHERE SUBSTRING(DealerName FROM 1 FOR 6) = 'Toyota'
SELECT ... WHERE DealerName LIKE 'Toyota%'
String postfix search:
SELECT ... WHERE Email LIKE '%wikipedia.org'
SELECT ... WHERE REVERSE(Email) LIKE REVERSE('%wikipedia.org')
Find rows from last 20 days:
SELECT ... WHERE EXTRACT(DAY FROM (CURRENT_DATE - date)) < 20
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)
- Code Project: Sargable query in SQL Server