SARGable expressions and performance

Understanding the concept of SARGable expressions can dramatically help you speed up query performance. Here’s how:

What is SARGable?

SARG is an acronym of sorts for “search argument”. In plain english, a SARGable query is where you can use an index seek, because you’re looking for a single value or range of values in the index. The opposite of a SARGable query is when you perform some type of function or calculation on each row in the table, and match the result of this calculation to your search criteria. When you can’t use an index to look for a single value or range, you’re performing a table scan or index scan, which means you’re traversing the entire table or index, which is a costly operation if there are a lot of rows.

Of course, the entire performance benefit from SARGable expressions comes from having an index that covers the columns you’re querying in the first place. Without such an index, you will automatically end up with a table scan or index scan anyway.

Some test data

Here’s a table with some test data, used in the rest of the article:

CREATE TABLE #testData (
    [ID]        int IDENTITY(1, 1) NOT NULL,
    [date]        datetime NOT NULL,

CREATE UNIQUE INDEX #IX_testData ON #testData ([date]);

DECLARE @date datetime={d '2014-01-01'};
WHILE (@date<{d '2014-01-02'}) BEGIN;
    INSERT INTO #testData ([date]) VALUES (@date);
    SET @date=DATEADD(ss, 30, @date);

INSERT INTO #testData ([date]) SELECT DATEADD(dd,  1, [date]) FROM #testData;
INSERT INTO #testData ([date]) SELECT DATEADD(dd,  2, [date]) FROM #testData;
INSERT INTO #testData ([date]) SELECT DATEADD(dd,  4, [date]) FROM #testData;
INSERT INTO #testData ([date]) SELECT DATEADD(dd,  8, [date]) FROM #testData;
INSERT INTO #testData ([date]) SELECT DATEADD(dd, 16, [date]) FROM #testData;

Scalar calculations

Here’s an example using a scalar function, the DATEPART() function.

In the first example, we’re looking for records in a table dated january 15th. In the top query, we’re using DATEPART() to filter the correct year, month and day. These function calls are called once for every row in the table, which means that we’re performing a costly index scan.

In the lower of the two queries, we’re looking for a range of dates instead, which allows us to benefit from the index on the date column.

--- Not SARGable expression: Index scan
SELECT [date]
FROM #testData
WHERE DATEPART(yy, [date])=2014 AND
      DATEPART(mm, [date])=1 AND
      DATEPART(dd, [date])=15;

--- SARGable expression: Index seek
SELECT [date]
FROM #testData
WHERE [date]>={d '2014-01-15'} AND
    [date]<{d '2014-01-16'};

You’ll notice the performance difference when you look at the query plan for these two queries:

Query plan of SARGable vs non-SARGable query

Arithmetic operators

The same logic applies when you’re using arithmetic operations. Note the difference in the following examples between adding 1000 to the ID column, as opposed to subtracting 1000 from the search criteria.

--- Not SARGable expression: Index scan
SELECT [ID], [date]
FROM #testData
WHERE [ID]+1000>=41321 AND [ID]+1000<44201;

--- SARGable expression: Index seek
SELECT [ID], [date]
FROM #testData
WHERE [ID]>=41321-1000 AND [ID]<44201-1000;

Wildcard comparisons

Obviously, the same is true for wildcards and string operations.

--- Not SARGable expression: Index scan
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName+'' LIKE 'Hill%';

--- SARGable expression: Index seek
SELECT FirstName, LastName
FROM Person.Person
WHERE LastName LIKE 'Hill%';

Except, here, you wouldn’t expect the second query to be SARGable, would you? The fact is, however, it is. The reason is that placing the wildcard operator at the end of the string enables the server to look for a range of values. Here’s how the query is actually interpreted by the server (look at the bottom of the tooltip in the query plan):

SELECT FirstName, LastName
FROM Person.Person
WHERE LastName>='Hill' AND LastName<'HilM' AND LastName LIKE 'Hill%';

So, because the wildcard in this expression is at the end of the string, the server can evaluate a range or records (from Hill to HilM) and then apply the wildcard to this range, which is still a lot more effective than performing an index scan.

How to eliminate non-SARGable queries

A lot of functions and scalar operations have a corresponding inverse function or operation. You can make a query SARGable if you can replace the use of these scalar functions (in the WHERE or JOIN clauses) with their inverse functions.


--- Replace this:
WHERE salesAmount/1000>50.00
--- ... with this:
WHERE salesAmount>50000.00

Until next week!

5 thoughts on “SARGable expressions and performance

  1. Pingback: Partitioned views over table partitioning « Sunday morning T-SQL

  2. Hi Daniel. This code WHERE [date]>={d ‘2014-01-15’} AND [date]<{d '2014-01-16'} above has saved my bacon. I was looking for sargable way to query a date range. Perfect. My question is where is the documentation for this? Been looking online and I cannot find anything.

    • Can’t remember where I learned it, but it’s the standard ODBC API notation for writing dates and times “natively”, which is also supported by ADO and OLEDB. Bear in mind that the example you note will still work even if you don’t use the curly braces – the part that makes it SARGable is that the [date] column stands alone (without any type conversions or calculations).

      Here’s a more detailed explanation:

  3. Pingback: About wildcards and data type precedence |

  4. Pingback: Speed up SQL query

Let me hear your thoughts!

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.