What is a SARGable Query?
The word SARGable (Search ARGument) is quite odd at best. This refers to the ability a SQL query can seek on an index. This directly relates to how the predicate is developed.
The opposite would be non-SARGable. This is when your query scans an index because your query is not descriptive enough when asking for results from the database engine and it must look through all the records it has for possible matches.
What impact do non-SARGable queries have on my system?
Well… non-SARGable queries will scan your indexes thus using system resources that would be put towards other processes. Having these queries will increase the time it takes to return the data. How much time depends on the amount of data the query must scan.
But I have Indexes?
Great! You should have indexes for your queries, but you need to take advantage of them by properly writing your SQL statements. Lets quickly go over Indexes as it relates to this article.
Indexes – Well defined indexes allow a request (SQL query) to extract data fast and efficiently. You can define multiple indexes per table but can only have one clustered index. There is an average recommended number of indexes you should not exceed, but that’s going deeper than this article is about.
Index Scan – This action will look at every row in your index trying to match what you are requesting.
Index Seek – This action knows exactly what you are asking and where to find it in the index. Generally, this is what you would like to see.
Let’s take a peek at some common examples.
Typical Search Parameter Allows for NULLS.
Sargable – Here we are selecting by an exact match for Customer Name. The execution plan shows an Index Seek.
Non-Sargable – Assuming @CustName could be NULL, we have added the check “OR @CustName IS NOT NULL” that will bypass the existence of @Custname. As you can see, SQL Server will choose to scan the primary key index, thus creating more overhead and generally, slower performance.
Typical Search Using LIKE
Sargable – We will use LIKE with a trailing ‘%’ that will return everything that starts with Kent. Now one would think this would result in an index scan. It does not. Basically, the more characters you supply, the narrower the expression becomes, thus filtering our paths to the data.
Non-Sargable – Let's add a ‘%’ to the front of the expression. Yes, indeed the query plan chose an index scan as it needed to look at every row in the index and evaluate the match.
Typical Reporting Predicate
Sargable – Using date ranges with less than or greater than will result in an Index Seek. Between works as well.
Non-Sargable – When you wrap a function around the column transforming the data, this will result in an Index Scan. SQL Server has to convert, in this case to month, and then run the evaluation for each row in the index.
Heads up! This script will help you track down sargable queries in your database.
We went over what a SARGable and non-SARGable query is and how it affects your system performance. We also looked at some examples in SQL Server and ways to see if your query is SARGable by viewing the execution plan. Lastly, we looked at a better approach to writing your queries to ensure you are using your index correctly and provided a script to help track down these poorly written queries.
If you take your time and understand how the data engine will interpret your query, your system and application will thank you. Additionally, it will keep costs down whether it’s a physical server or in the cloud.