Take the 2-minute tour ×

By definition (at least from what I've seen) sargable means that a query is capable of having the query engine optimize the execution plan that the query uses. I've tried looking up the answers, but there doesn't seem to be a lot on the subject matter. So the question is, what does or doesn't make an SQL query sargable? Any documentation would be greatly appreciated.

For reference: Sargable

share|improve this question
+1 for "sargable". That's my word of the day for today. :-p –  BFree Apr 28 '09 at 20:03
I might also add to Adam's answer, that the mountains of information are in most cases extremely particular to each DB engine. –  hoagie Apr 28 '09 at 20:05
SARG = Search ARGument. Funny thing is: "SARG" in German means "Coffin", so I always have to smile when folks talk about SARGABLE - able to be put in a coffin? :-) –  marc_s Apr 28 '09 at 20:31
sargability depends on your environment. MySQL's is documented here: dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html –  Frank Farmer Jun 11 '10 at 22:13
add comment

3 Answers

up vote 70 down vote accepted

The most common thing that will make a query non-sargable is to include a field inside a function in the where clause:

WHERE Year(myDate) = 2008

The SQL optimizer can't use an index on myDate, even if one exists. It will literally have to evaluate this function for every row of the table. Much better to use:

WHERE myDate >= '01-01-2008' AND myDate < '01-01-2009'

Some other examples:

Bad: Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'
Fixed: Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))

Bad: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Fixed: Select ... WHERE DealerName Like 'Ford%'

Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30
Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate()) 
share|improve this answer
Will including a function inside of GROUP BY cause a query to become non-sargable? –  Mike Bantegui Jun 22 '12 at 12:26
Shouldn't your first example be WHERE (FullName = 'Ed Jones' AND FullName IS NOT NULL) instead of WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))? (This was spotted by new user @boris_wt, but his edit was rejected for changing the meaning of your post. Don't be put off, Boris—it was good advice! Just ask or answer a couple of good questions and you'll be able to leave comments soon enough. :) ) –  Jordan Gray Jan 8 at 13:23
The intent of the original version (with the isNull function) is to returns results that either match a specific name or have a missing name. The proposed edit changes the result, so isn't a valid edit. We could argue about why you would want to return that particular record set, maybe I could have picked a better example. –  BradC Jan 9 at 19:45
@BradC Not the isNull expression here! I've seen other versions using IsNull(Name, 'SomeName') = 'SomeName' which would be the "match a specific name or have a missing name" behaviour you describe, but this is not it. –  Mark Hurd Mar 6 at 4:08
Doh! Can't believe I didn't see the error earlier, even when it was pointed out by others. Thanks, @MarkHurd! Fixed. –  BradC Mar 10 at 20:31
show 1 more comment

Don't do this:

WHERE Field LIKE '%blah%'

That causes a table/index scan, because the LIKE value begins with a wildcard character.

Don't do this:


That causes a table/index scan.

The database server will have to evaluate FUNCTION() against every row in the table and then compare it to 'BLAH'.

If possible, do it in reverse:


This will run INVERSE_FUNCTION() against the parameter once and will still allow use of the index.

share|improve this answer
Your suggestion with flipping the function would really only work when the function round-trips data (meaning that f(f(n)) = n). –  Adam Robinson Apr 28 '09 at 20:16
True. I considered adding INVERSE_FUNCTION but didn't want to be confusing. I'll change it. –  beach Apr 28 '09 at 20:17
add comment

In this answer I assume the database has sufficient covering indexes. There are enough questions about this topic.

A lot of the times the sargability of a query is determined by the tipping point of the related indexes. The tipping point defines the difference between seeking and scanning an index while joining one table or result set onto another. One seek is of course much faster than scanning a whole table, but when you have to seek a lot of rows, a scan could make more sense.

So among other things a SQL statement is more sargable when the optimizer expects the number of resulting rows of one table to be less than the tipping point of a possible index on the next table.

You can find a detailed post and example here.

share|improve this answer
add comment

Your Answer


