Sargable

From Wikipedia, the free encyclopedia
Jump to: navigation, search

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 situation 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.

  • 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 an SQL condition.
  • Avoid non-sargable predicates and replace them with sargable equivalents.

Examples[edit]

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

References[edit]

See also[edit]