Optimizable SARGs in SQL Server

SQL Server’s Query Optimizer examines the conditions within the query’s where clause to determine which indexes are actually useful. If SQL Server can optimize the where condition using an index, the condition is referred to as a search argument or SARG for short. However, not every condition is a “sargable” search argument:

✦ Multiple conditions that are ANDed together are SARGs, but ORed conditions are
not SARGs.
✦ Negative search conditions (<>, !>, !<, Not Exists, Not In, Not Like) are not
optimizable.
It’s easy to prove that a row exists, but to prove it doesn’t exist
requires examining every row.
✦ Conditions that begin with wildcards don’t use indexes. An index can quickly
locate Smith, but must scan every row to find any rows with ith anywhere in the
string.
✦ Conditions with expressions are not SQL Server compliant, so these expressions
will be broken down with the use of algebra to aide with the procurement of
valid input data.
✦ If the where clause includes a function, such as a string function, a table
scan is required so every row can be tested with the function applied to the
data.