Improving performance by using sargable conditions

Sargable stands for Search ARGument Able. Sargable conditions help query optimizers to use the index defined on column(s) effectively. Sargable conditions have a higher chance of meeting index seek than index or table scan.

Getting ready

Writing a sargable condition is also a preventive step. Consider this factor while writing the query or while working on performance tuning projects. There are some operators that make your query sargable or non-sargable. Here is the list:

Sargable operators:

• =

• >

• >=

• <

• <=

• BETWEEN

• LIKE (only those LIKE conditions that have a wildcard character as a suffix, for example, FirstName LIKE 'R%')

Non-sargable operators:

• !=

• !<

• !>

• <>

• NOT EXISTS

• IN

• NOT IN

• LIKE (LIKE conditions that have a wildcard character as a prefix in the filter, for example, FirstName LIKE '%R')

• NOT LIKE

• Functions on column name in predicate

The main intention for giving a list of operators for both situations...