Please stop writing Non-SARGable SQL queries
Non-SARGable queries are killing your databases performance. Let's talk about what they are and how we can resolve them.
SARGability is a critical concept to understand when writing SQL queries, as it impacts their performance significantly.
SARGable is short for Search ARGument Able.
Basically, when we say a query is SARGable, we are saying that the query can utilise an index seek operation (if the appropriate index is available). This is typically what we want our queries to be doing.
Let’s look at the SARGability of the below queries by analysing the WHERE predicates:
-- Query 1
SELECT customer_id,last_name
FROM sales.customers
WHERE last_name = 'Baldwin'
-- Query 2
SELECT customer_id,last_name
FROM sales.customers
WHERE UPPER(last_name) = 'Baldwin'
-- Query 3
SELECT customer_id,last_name
FROM sales.customers
WHERE last_name LIKE 'Baldwin%'
-- Query 4
SELECT customer_id,last_name
FROM sales.customers
WHERE last_name LIKE '%Baldwin%'Now all the above queries are simple ones. Pulling ‘customer_id’ and ‘last_name’ from the ‘sales.customers’ table.
I have gone ahead and created an index on the ‘last_name’ column of the ‘sales.customers’ table. I structured the index like this as the column is being searched for in each query in the WHERE predicate. The index is named ‘lastname_customerId’.
Query 1 is a SARGable query as SQL Server will be able to use an index seek operation for the record with the ‘last_name’ column being ‘Baldwin’.
Query 2 is a non-SARGable query. This is because SQL Server can’t SEEK for the record ‘Baldwin’. It can’t SEEK due to the fact we are using an UPPER function on the left-hand side of the predicate. Basically, SQL Server will go ahead and apply the UPPER function to all the records in the ‘last_name’ column and while it is applying the function to each row, it is checking if the value equals ‘Baldwin’. This is why SQL Server will do a SCAN rather than a SEEK for this query (as proved below), as it needs to apply the UPPER function to each row.
Can we turn Query 2 into a SARGable query?
Yes, we can. We can rewrite the query slightly, in order to make SQL Server use the index SEEK operation.
The SARGable version of Query 2:
-- Query 2 SARGable version
SELECT customer_id,last_name
FROM sales.customers
WHERE last_name = UPPER('Baldwin')All I did was move the UPPER function to the right-hand side of the WHERE predicate. Doing this means that SQL Server will not need to apply the UPPER function to each row value in the ‘last_name’ column. Instead, SQL Server will only need to apply the UPPER function to the value we have given, which is ‘Baldwin’. This, in turn, allows SQL Server to perform an index SEEK operation (as seen below).
Query 3 is a SARGable query and query 4 is a non-SARGable query. Both queries look very similar but perform very differently and if these queries were working on large data sets, the speed of execution could vary greatly.
Why is the performance so different between queries 3 and 4? What makes the queries different?
The queries are written slightly differently in the WHERE predicate.
Query 3 has
WHERE last_name LIKE 'Baldwin%'Query 4 has
WHERE last_name LIKE '%Baldwin%'The difference is that ‘Baldwin’ has a % sign in front of it in query 4 but not in query 3. So query 3 is searching for all records that start with ‘Baldwin’ but it can end with anything. Whereas, query 4 is searching for records that contain the string ‘Baldwin’ somewhere in the middle of the row value. So in the case of query 4, the searched result can start with anything as long as the value ‘Baldwin’ exists somewhere in the value.
Since the starting value can be anything for query 4, SQL Server will need to check every single record. This is why query 4 is not able to do an index SEEK and will have to do an index SCAN. Queries like query 4 are not able to become SARGable. This is why it is suggested to avoid using the % sign in front of values in predicates. It doesn’t do well for performance.
Hope this helped your understanding of SARGability!