298

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

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
DForck42
  • 19,789
  • 13
  • 59
  • 84
  • 72
    +1 for "sargable". That's my word of the day for today. :-p – BFree Apr 28 '09 at 20:03
  • 44
    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: http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html – Frank Farmer Jun 11 '10 at 22:13
  • 1
    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
  • Having free-text fields instead of "lookup tables" also goes against the spirit of making a query sargable. Users misspell stuff when entering free-text (e.g. town name), whereas lookup-tables force users to choose a correctly spelled entry. Well worth the slight extra trouble, because this can be properly indexed instead of using LIKE '%...%' in the predicate. – Reversed Engineer May 14 '18 at 07:28

4 Answers4

303

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

SELECT ... FROM ...
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()) 
BradC
  • 39,306
  • 13
  • 73
  • 89
  • 7
    Will including a function inside of `GROUP BY` cause a query to become non-sargable? – Mike Bailey Jun 22 '12 at 12:26
  • @MikeBantegui Just including a field in a GROUP BY won't necessarily make it non-sargeable, no. The right indexes will definitely help a GROUP BY query. – BradC Mar 10 '14 at 20:32
  • 1
    *Some* database engines (Oracle, PostgreSQL) support indexes on expressions, dontcha know? – Craig Tullis Mar 27 '14 at 14:50
  • 3
    Would an even better version of `WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))` be `SELECT... FROM ... WHERE FullName = 'Ed Jones' UNION SELECT...FROM...WHERE FullName IS NULL`? I was once told by an optimisation guy that using OR in the where clause can unsarg queries..? – High Plains Grifter Nov 09 '15 at 09:30
  • The UNION query would create temporary tables, which in fact may decrease overall performance for greater sets – ad4s Jan 24 '17 at 19:38
  • @HighPlainsGrifter @ad4s I would recommend using `explain analyze` or similar to actually look at the query plans. Optimizers are pretty good at handling queries like that, so it's hard to say which will be faster in practice. There's also an element of non-determinism in there, as the query planner will give up trying to find a plan after a certain amount of time, and also base its decision off of table statistics. Always profile it to see! – John Gibb Jan 12 '18 at 15:23
  • 3
    @HighPlainsGrifter you should use a UNION ALL on that query - union has an implicit distinct, which makes a query much more expensive than it needs to be when you have to mutually exclusive datasets – Devin Lamothe May 24 '18 at 18:02
  • 2
    @BradC In MSSQL 2016, there is no execution plan difference between `Select ... WHERE isNull(FullName,'Ed Jones') = 'Ed Jones'` and `Select ... WHERE ((FullName = 'Ed Jones') OR (FullName IS NULL))`. They both use the index on FullName and do an index seek. – CEGRD Sep 19 '18 at 07:53
  • also beware if you need to convert a SQL condition yourself to make it *"sargable"* in most cases you have to add parenthesis consider this `YEAR(myDate) = 2008 AND any_columns = 1` you would need to use `(myDate >= '2008-01-01' AND myDate < '2009-01-01') AND any_columns = 1` to keep the SQL meaning the same.. But i believe SQL Server 2016+ has a pretty good *"sargable"* optimisation rewrite step in the optimizer which handle have most cases.. – Raymond Nijland Nov 16 '19 at 16:19
95

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:

WHERE FUNCTION(Field) = 'BLAH'

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:

WHERE Field = INVERSE_FUNCTION('BLAH')

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

Doug Coats
  • 6,255
  • 9
  • 27
  • 49
beach
  • 8,330
  • 3
  • 29
  • 25
  • 6
    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
  • 6
    True. I considered adding INVERSE_FUNCTION but didn't want to be confusing. I'll change it. – beach Apr 28 '09 at 20:17
11

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.

Community
  • 1
  • 1
5

For an operation to be considered sargable, it is not sufficient for it to just be able to use an existing index. In the example above, adding a function call against an indexed column in the where clause, would still most likely take some advantage of the defined index. It will "scan" aka retrieve all values from that column (index) and then eliminate the ones that do not match to the filter value provided. It is still not efficient enough for tables with high number of rows. What really defines sargability is the query ability to traverse the b-tree index using the binary search method that relies on half-set elimination for the sorted items array. In SQL, it would be displayed on the execution plan as a "index seek".

user2011845
  • 71
  • 1
  • 2