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 ;

• =
• >
• <
• >=
• <=
• EXISTS
• IN
• BETWEEN
• LIKE 'searchterm%'

NOT conditions are not Sargable ;

• IS NULL
• <>
• !=
• !>
• !<
• NOT
• NOT EXISTS
• NOT IN
• NOT LIKE

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 -

• Mr. Wharty's Ramblings : Sargable Predicates
• Rob Farley : SARGable functions in SQL Server
• Richard Lees : Sargable Predicates are usually best practice
• Brent Ozar : Sargability: Why %string% Is Slow
• Rob Farley : Inverse Predicates - look both ways before you cross