{{Short description|Condition in databases}} {{Multiple issues| {{original research|date=August 2015}} {{onesource|date=April 2013}} }} 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''. It was first used by IBM researchers as a contraction of Search ARGument, and has come to mean simply "can be looked up by an index."{{ref|Gulutzan|1}}<ref name=":0">{{Cite web |last=Andy |first=Pavlo |date=Spring 2023 |title=CMU 15-721 :: Advanced Database Systems (Spring 2023) :: Lecture #16 Optimizer Implementation (Part 1) - Slide |url=https://15721.courses.cs.cmu.edu/spring2023/slides/16-optimizer1.pdf |url-status=live |archive-url=https://web.archive.org/web/20230601074825/https://15721.courses.cs.cmu.edu/spring2023/slides/16-optimizer1.pdf |archive-date=2023-06-01 |access-date=2024-01-25}}</ref><ref>{{Cite book |last1=Selinger |first1=P. Griffiths |last2=Astrahan |first2=M. M. |last3=Chamberlin |first3=D. D. |last4=Lorie |first4=R. A. |last5=Price |first5=T. G. |chapter=Access path selection in a relational database management system |date=1979 |title=Proceedings of the 1979 ACM SIGMOD international conference on Management of data - SIGMOD '79 |chapter-url=http://portal.acm.org/citation.cfm?doid=582095.582099 |language=en |publisher=ACM Press |pages=23 |doi=10.1145/582095.582099 |isbn=978-0-89791-001-9}}</ref>

For database query optimizers, '''sargable''' is an important property in OLTP workloads because it suggests a good query plan can be obtained by a simple heuristic{{ref|heuristic|2}} matching query to indexes instead of a complex, time-consuming cost-based search,<ref name=":0"></ref> thus it is often desired to write sargable queries. 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 an 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 list, on the other hand, can contain non-sargable expressions without adversely affecting the performance.

Some database management systems, for instance [https://www.postgresql.org/docs/current/indexes-expressional.html PostgreSQL, support functional indices]. Conceptually, an index is simply a mapping between a value and one or more locations. With a functional index, the value stored in the index is the output of the function specified when the index is created. This capability expands what is sargable beyond base column expressions.

* Sargable operators: {{code|lang=sql|1==, >, <, >=, <=, BETWEEN, LIKE, IS [NOT] NULL, IN}} * Sargable operators that rarely improve performance: {{code|lang=sql|1=<>, NOT, NOT IN, NOT LIKE}}

==Simple example== {{code|lang=sql|WHERE}} clauses that are sargable typically have column values on the left of the operator, and scalar values or expressions on the right side of the operator.

Not sargable:

<syntaxhighlight lang="tsql"> SELECT * FROM myTable WHERE SQRT(myIntColumn) > 11.7 </syntaxhighlight>

This is ''not sargable'' because myIntColumn is embedded in a function. If any indexes were available on myIntColumn, they could not be used. In addition, {{code|lang=sql|SQRT()}} would be called on every row in myTable.

Sargable version:

<syntaxhighlight lang="tsql"> SELECT * FROM myTable WHERE myIntColumn > 11.7 * 11.7 </syntaxhighlight>

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

==Text example== {{code|lang=sql|WHERE}} ... {{code|lang=sql|LIKE}} clauses that are sargable have column values on the left of the operator, and {{code|lang=sql|LIKE}} text strings that do not begin with the {{code|lang=sql|%}} on the right.

Not sargable:

<syntaxhighlight lang="tsql"> SELECT * FROM myTable WHERE myNameColumn LIKE '%Wales%' -- Begins with %, not sargable </syntaxhighlight>

This is ''not'' sargable. It must examine every row to find the column containing the substring {{code|lang=sql|'Wales'}} in any position.

Sargable version:

<syntaxhighlight lang="tsql"> SELECT * FROM myTable WHERE myNameColumn LIKE 'Jimmy%' -- Does not begin with %, sargable </syntaxhighlight>

This is sargable. It can use an index to find all the myNameColumn values that start with the substring {{code|lang=sql|'Jimmy'}}.

== See also == * Block Range Index * Query optimization

== Notes == :{{note|Gulutzan|1}} Gulutzan and Pelzer, ([https://www.informit.com/articles/article.aspx?p=30247 Chapter 2, ''Simple "Searches"'']) :{{note|heuristic|2}} <ref>{{Cite book |last1=Silberschatz |first1=Abraham |title=Database system concepts |last2=Korth |first2=Henry F. |last3=Sudarshan |first3=S. |date=2020 |publisher=McGraw-Hill Education |isbn=978-1-260-08450-4 |edition=7th |location=New York, NY |pages=773}}</ref> gives an example of such simple heuristic.

== External links ==

* [https://www.sqlshack.com/how-to-use-sargable-expressions-in-t-sql-queries-performance-advantages-and-examples/ SQL Shack - How to use sargable expressions in T-SQL queries; performance advantages and examples] * [https://dba.stackexchange.com/questions/162263/what-does-the-word-sargable-really-mean DBA.StackExchange.com - What does the word “SARGable” really mean?]

== References == {{Reflist}} * ''SQL Performance Tuning'' by Peter Gulutzan, Trudy Pelzer (Addison Wesley, 2002) {{ISBN|0-201-79169-2}} ([https://www.informit.com/articles/article.aspx?p=30247 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)

Category:Database management systems Category:Relational model