What is a SARGABLE Query? (and why you should care!)

Non-SARGABLE queries are killing the performance of your production databases.

If you’ve ever worked with databases and noticed your queries running slower than you’d like, chances are you’ve stumbled across the concept of a SARGABLE query. But what exactly does that mean?

SARGABLE = Search ARGuments-ABLE

It’s a combination of three words: Search, Arguments, and Able. Now let’s break that down to what that actually means.

So what does SARGABLE actually mean?

In simple terms, a query is SARGABLE if the condition (the predicate) in the query can use an index seek. Now, what’s an index seek? Think of it like this: imagine having an alphabetically ordered phone book. If you’re looking for someone whose name starts with “M,” you can flip right to the “M” section, skipping over all the A-L names. That’s exactly what an index seek does—it allows the database engine to jump directly to the records it needs.

Now, if your query isn’t SARGABLE, the database engine will have to use an index scan instead. That’s like flipping through every page of the phone book from the first page all the way down to “M.” This takes way more time and resources.

Why should you care about SARGABLE queries?

Because they make your queries faster! When your query is SARGABLE, the database can find the data much more efficiently using an index seek. Non-SARGABLE queries, on the other hand, force the engine to do a lot more work by scanning everything, which can significantly slow down performance.

How do you know if a query is SARGABLE?

It all depends on how you structure your conditions—specifically in your WHERE, JOIN, or HAVING clauses. If these conditions allow the database to quickly look up the data it needs, congratulations, your query is SARGABLE. If not, well, get ready for an index scan.

Examples of SARGABLE Queries

SELECT * 
FROM orders 
WHERE order_id = 23400;

• Why it’s SARGABLE: The query uses ‘=’ on order_id, which is likely indexed. The database can use an index seek to jump directly to order_id 23400.

SELECT * 
FROM orders 
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';

• Why it’s SARGABLE: The range condition on order_date can still use an index seek, as long as order_date is indexed. This allows the database to jump directly to the right range of dates instead of scanning all records.

Non-SARGABLE Queries (What to Avoid)

SELECT * 
FROM orders 
WHERE YEAR(order_date) = 2024;

Why it’s not SARGABLE: The YEAR() function is applied to order_date column, so the database can’t directly use the index. Instead, it will have to scan every record and apply the function to every single record.

SELECT * 
FROM customers 
WHERE customer_name LIKE '%Smith';

• Why it’s not SARGABLE: The leading wildcard (%) in LIKE prevents the database from using an index seek. This is because we are looking for rows with anything in front of ‘Smith’. So the database engine has to scan every record, making it less efficient.

In a nutshell: SARGABLE queries = more efficient queries.

So next time you’re working on your SQL, keep an eye on those conditions and aim to make your queries SARGABLE whenever possible.