Friday, 4 April 2014

SQL Development : Sargability

When developing SQL queries you ideally want your WHERE clauses and JOIN conditions to be sargable.

The definition from Wikipedia of Sargability is -
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 (using index seeks, not covering indexes). The term is derived from a contraction of Search ARGument Able.
Sargable conditions (can use indexes) are ;
  • =
  • >
  • <
  • >=
  • <=
  • IN
  • LIKE 'searchterm%'

NOT conditions are not Sargable ;
  • <>
  • !=
  • !>
  • !<
  • NOT
  • NOT IN

Expressions not matching the left of an index will not use an index either
  • LIKE '%searchterm'
  • LIKE '%searchterm%'

Using Functions on columns or comparing columns to other columns is also not Sargable.
Common examples of these are SUBSTRING, DATEDIFF in the WHERE-clause.
These prevent index use and cause table scans.

From Conor vs. more SARGable predicates -
  1. Avoid table variables for large results sets (as at Dec 2010)
  2. Avoid ORs – Prevent queries using a single range.
  3. Use IS NULL NOT ISNULL(expression, replacement_value)

Further Reading -

No comments: