Sargable

This article has multiple issues. Please help or discuss these issues on the talk page. (Learn how and when to remove these template messages) 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. Please help if you can. (November 2014) (Learn how and when to remove this template message) This article possibly contains original research. Please by verifying the claims made and adding inline citations. Statements consisting only of original research should be removed. (August 2015) (Learn how and when to remove this template message) This article relies largely or entirely on a single source. Relevant discussion may be found on the talk page. Please help by introducing citations to additional sources. Find sources:  –  ·  ·  · scholar · JSTOR (April 2013) The topic of this article may not meet Wikipedia's general notability guideline. Please help to demonstrate the notability of the topic by citing reliable secondary sources that are independent of the topic and provide significant coverage of it beyond a mere trivial mention. If notability cannot be shown, the article is likely to be merged, redirected, or deleted. Find sources:  –  ·  ·  · scholar · JSTOR (January 2020) (Learn how and when to remove this template message) (Learn how and when to remove this template message)

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 a non-sargable query and typically has a negative 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 LIKE

Simple example[]

WHERE clauses that are sargable typically have field values on the left of the operator, and scalar values or expressions on the right side of the operator.

Not sargable:

SELECT *
FROM   myTable
WHERE  SQRT(myIntField) > 11.7

This is not sargable because myIntField is embedded in a function. If any indexes were available on myIntField, they could not be used. In addition, SQRT() would be called on every record in myTable.

Sargable version:

SELECT *
FROM   myTable
WHERE  myIntField > 11.7 * 11.7

This is sargable because myIntField is NOT contained in a function, making any available indexes on myIntField potentially usable. Furthermore, the expression is evaluated only once, rather than for each record in the table.

See also[]

• Block Range Index
• Query optimization

References[]

• 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)

External links[]

• SQL Shack - How to use sargable expressions in T-SQL queries; performance advantages and examples
• DBA.StackExchange.com - What does the word “SARGable” really mean?