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)
or VARCHAR to NVARCHAR:
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 this | Do this |
| Column – 1 = @Value | Column = @Value + 1 |
| ABS(Column) = 1 | Column 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) = @Value | Column >= 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 🙂