Today I will share with you some suggestions about using SARGable predicates. First of all let’s start explaining what is a SARGable predicate…
SARGable stands for Search Argument able: in a nutshell a predicate is SARGable if Sql Server can use an index to improve the execution of the query.

Use the following operators to build SARGable predicates: =, >, >=, <, <=, IN, BETWEEN, and LIKE (only when used in the form ‘abc%’).

On the contrary the following operators are not SARGable: NOT, NOT IN, <>, and LIKE when used in the form ‘%abc%’.

But pay attention: you can make a predicate non SARGable even if you use a SARGable operator. This could happen, for example, in these cases:

  • when you use a function in the predicate of the query:
    WHERE ABS(Value) = 1
  • when you build a predicate where there is an implicit conversion, like integer to VARCHAR:
    WHERE Value = 200
    (in this case we assume that the column Value is of type VARCHAR)

    WHERE Value = ‘ABC’
    (in this case we assume that the column Value is of type NVARCHAR)

Here you can find some examples of non SARGable predicates that can be refactored to a SARGable one:

Don’t do thisDo this
Column – 1 = @ValueColumn = @Value + 1
ABS(Column) = 1Column BETWEEN -1 AND 1

Column >= -1 AND Column <= 1

Column IN (-1, 1)
CONVERT(DATE, Column) = @Value
(where Column is DateTime)
Column >= @Value AND Column < DATEADD(DAY, 1, @Value)
YEAR(Column) = @ValueColumn >= CONVERT(DATE, @Value + ‘0101’) AND Column <= CONVERT(DATE, @Value + ‘1231’)
LEFT(Column, 3) = ‘ABC’Column LIKE ‘ABC%’
Column = ‘ABC’ (column is of type NVARCHAR)Column = N’ABC’

So be careful when writing your predicates… you might run into a non SARGable one 🙂