‘SARGable’ is a weird term. It gets bandied around a lot when talking about indexes and whether queries can seek on indexes. The term’s an abbreviation, ‘SARG’ stands for Search ARGument, and it means that the predicate can be executed using an index seek.
Lovely. So a predicate must be SARGable to be able to use an index seek, and it must be able to use an index seek to be SARGable. A completely circular definition.
So what does it actually mean for a predicate to be SARGable? (and we’ll assume for this discussion that there are suitable indexes available)
The most general form for a predicate is <expression> <operator> <expression>. To be SARGable, a predicate must, on one side, have a column, not an expression on a column. So, <column> <operator> <expression>
SELECT * FROM Numbers WHERE Number = 42;
SELECT * FROM Numbers WHERE Number + 0 = 42;
SELECT * FROM Numbers WHERE Number = 42 + 0;
Any1 function on a column will prevent an index seek from happening, even if the function would not change the column’s value or the way the operator is applied, as seen in the above case. Zero added to an integer doesn’t change the value of the column, but is still sufficient to prevent an index seek operation from happening.
While I haven’t yet found any production code where the predicate is of the form ‘Column + 0’ = @Value’, I have seen many cases where there are less obvious cases of functions on columns that do nothing other than to prevent index seeks.
UPPER(Column) = UPPER(@Variable) in a case-insensitive database is one of them, RTRIM(COLUMN) = @Variable is another. SQL ignores trailing spaces when comparing strings.
The other requirement for a predicate to be SARGable, for SQL Server at least, is that the column and expression are of the same data type or, if the data types differ, such that the expression will be implicitly converted to the data type of the column.
SELECT 1 FROM SomeTable WHERE StringColumn = 0;
SELECT 1 FROM SomeTable WHERE StringColumn = ‘0’;
There are some exceptions here. Comparing a DATE column to a DATETIME value would normally implicitly convert the column to DATETIME (more precise data type), but that doesn’t cause index scans. Neither does comparing an ascii column to a unicode string, at least in some collations.
In generally though, conversions should be explicit and decided on by the developer, not left up to what SQL server decides.
What about operators?
The majority are fine. Equality, Inequality, IN (with a list of values), IS NULL all allow index usage. EXIST and IN with a subquery are treated like joins, which may or may not use indexes depending on the join type chosen.
LIKE is a slight special case. Predicates with LIKE are only SARGable if the wildcard is not at the start of the string.
SELECT 1 FROM SomeStrings WHERE ASCIIString LIKE 'A%'
SELECT 1 FROM SomeStrings WHERE ASCIIString LIKE '%A'
There are blog posts that claim that adding NOT makes a predicate non-SARGable. In the general case that’s not true.
SELECT * FROM Numbers WHERE NOT Number > 100;
SELECT * FROM Numbers WHERE NOT Number <= 100;
SELECT * FROM Numbers WHERE NOT Number = 137;
These index seeks are returning most of the table, but there’s nothing in the definition of ‘SARGable’ that requires small portions of the table to be returned.
That’s mostly that for SARGable in SQL Server. It’s mostly about having no functions on the column and no implicit conversions of the column.
(1) An explicit CAST of a DATE column to DATETIME still leaves the predicate SARGable. This is an exception that’s been specifically coded into the optimiser.
