Make your queries SARGable

Having the right index is helpful, but are you using the predicate (WHERE clause) correctly to make efficient use of that index?

This is where the term SARGable comes into play. SARGable stands for Search ARGumentable. If SQL Server is able to limit the search space while evaluating the predicates and can seek right at the page(s) to get the values, then it is SARGable.

Alright let me explain in simple terms. If you want to look for a word in the dictionary, especially the first few letters are important so you know which page to turn to, instead of looking for the word from the first page.

Example 1: A SARGable Query (Seek)

Imagine you’re asked to look for a word ‘function’, then you quickly turn to the page where the first letter starts with ‘f’ and then search within that section. This is called seeking also called SARGable.(In my head, I read SARGable as ‘seekable’, as you’re able to seek at the right spot and find the values).
In this case, dictionary is the table and index is on the words column, which are sorted in ascending order by letters.
SQL Equivalent: SELECT * FROM dictionary WHERE word=’function’;
This results in an index seek, since the where clause is SARGable(seek-able).

Example 2: A Non-SARGable Query (Scan)

Now, you’re asked to look for words that end with ‘tion’. You have to scan the entire dictionary because words can start with any letter. This is similar to an index scan, which is non-SARGable(non-seekable).
SQL Equivalent: SELECT * FROM dictionary WHERE word LIKE ‘%tion’;

Some Examples to Write SARGable Queries

Avoid Using Functions on Indexed Columns

Example 1:

Using StackOverflow2013 database, Users table.
Note: Created a non clustered index on DisplayName and clustered index on column ID.

Instead of

SELECT ID
FROM Users
WHERE LEFT(DisplayName, 4) = 'Mary';

• The LEFT() function modifies the column and value must be evaluated for every row before filtering.

Use this

SELECT ID
FROM Users
WHERE DisplayName LIKE 'Mary%';

• The optimizer can now use an index seek, directly jumping to relevant pages.

Example 2:

Note: There is a non clustered index on CreationDate and clustered index on column ID.

Instead of

SELECT ID
FROM Users
WHERE YEAR(CreationDate) = '2012';

• YEAR(CreationDate) needs to be evaluated for every row before filtering.

Use this

SELECT ID
FROM Users
WHERE CreationDate >= '2012-01-01'
AND CreationDate < '2013-01-01';

• There is no function on the left side, and the range of dates on the right can help to seek at the right pages, thereby limiting the search space with the range of dates.

Example 3:

Instead of

SELECT ID
FROM Users
WHERE DATEADD(DAY, -1, CreationDate) = GETDATE();

• Again, a function on the left side means, scan and evaluate each row in the column to match function criteria.

Use this

SELECT ID
FROM Users
WHERE CreationDate = DATEADD(DAY, 1, GETDATE());

• The function is moved to the right side, now it can get a range of values and seek right at those pages.

Example 4:

This is an example of how LIKE operator can be either SARGable or non-SARGable, based on how it is used.

SELECT ID
FROM Users
WHERE DisplayName LIKE '%Mary%';

• In this case, first letter can be anything, so it needs to scan the whole index. This leads to an index scan and it is non-SARGable.

SELECT ID
FROM Users
WHERE DisplayName LIKE 'Mary%';

• In this case, it knows which pages to look at, since the first 4 letters are Mary.
  This leads to index seek as it SARGable or seekable. (Non clustered index is on DisplayName which is ordered.)

• If there are places where you can avoid the scan then you can make better use of indexes.

  

Summary: Best Practices for SARGability

• SARGable predicates include operators such as =, <, >, >=, <=, LIKE, BETWEEN,IN.

• Non-SARGable predicates include operators such as NOT, LIKE, NOT IN, <>.

• Move functions to the right-hand side of comparisons whenever possible.

• Use range conditions (>=, <, BETWEEN) instead of functions on columns.

• Ensure indexed columns are used in their raw form in WHERE conditions.

• Avoid wildcard searches with leading % (LIKE '%value').

  

Resources:

• https://gohigh.substack.com/p/does-index-help-when-there-are-functions

• https://www.brentozar.com/archive/2018/12/do-functions-stop-you-from-using-indexes/

• https://www.mssqltips.com/sqlservertutorial/3204/avoid-using-functions-in-where-clause/

• https://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

Thanks for reading,
Haripriya.