Optimizing `WHERE` Clauses for Index Usage

Hello! Welcome back.

In our last lesson, you learned how to use a query's execution plan to spot the most common performance bottleneck: the Seq Scan (or Full Table Scan). This is a clear sign that a query is inefficiently reading every row in a table, often because an index is missing.

But what happens if you have a perfectly good index on a column, yet the execution plan still shows a dreaded scan? This is a common and frustrating scenario. It often means that while the index is fine, the query itself is written in a way that prevents the database from using it.

Today, we'll tackle this exact problem. Your goal is to learn how to rewrite a non-SARGable WHERE clause condition to enable index usage. This is a crucial skill for writing high-performance SQL.

What Does "SARGable" Mean?

The term "SARGable" is a bit of database jargon, but it stands for a simple and powerful idea: Search ARGument Able.

A WHERE clause is "SARGable" if the database's query optimizer can use an index to efficiently find the data it needs. A non-SARGable query forces the optimizer to ignore the index and scan the entire table, even if an index exists.

Let's start with a short video that defines the term and shows a crystal-clear example of a non-SARGable query being transformed into a SARGable one.

Performance of SARGable queries in SQL Server

This video from Bert Wagner provides an excellent, concise introduction to SARGable queries and the fundamental rule for writing them.

Watch the entire video. Pay close attention to the core principle explained: the difference between applying a function to the table's column versus applying it to the input value.

The "Why": Indexes and Functions Don't Mix

The key takeaway from the video is: Do not manipulate the indexed column in the WHERE clause.

To understand why this is so critical, let's connect this back to your low-level programming knowledge. An index, like a B-Tree, is essentially a sorted data structure. Searching a sorted list is incredibly fast because you can jump directly to the right location.

Now, imagine you have a phone book sorted by LastName. If you're looking for "Smith," you can open it to the 'S' section and find it quickly. This is an Index Seek—it's fast and efficient.

But what if your search condition was REVERSE(LastName) = 'htimS'? The phone book is no longer useful. Its sorted order is based on the original last names, not the reversed ones. To satisfy this query, you have no choice but to start at the first page, take every single name, reverse it, and then check if it equals 'htimS'. You have to read the entire book. This is an Index Scan (or Table Scan).

When you apply a function like YEAR(order_date), UPPER(name), or CAST(id AS text) to a column in your WHERE clause, you are doing the exact same thing. You are telling the database, "You can't use your sorted index. You must first compute a new value for every single row in the table and then perform the comparison." This negates the entire benefit of having an index.

The solution is always to perform the manipulation on the literal or value side of the equation, leaving the indexed column "naked."

YEAR(order_date) = 2023 (Non-SARGable)
becomes
order_date >= '2023-01-01' AND order_date < '2024-01-01' (SARGable)

Here, the order_date column is left alone, and the optimizer can efficiently seek the date range within its index.

Common Non-SARGable Patterns and How to Fix Them

Let's dive into some of the most common non-SARGable patterns you'll encounter and how to rewrite them. The following article provides a great summary of these patterns with clear examples.

SQL SERVER - Catching Non-SARGable Queries in Action

This blog post from SQLAuthority provides a catalog of common non-SARGable patterns and their SARGable rewrites. It's a great reference to have.

Read the sections titled 'Rewrite as SARGable', 'Why This Happens', 'Other Common Non-SARGable Patterns', and 'Testing With Indexes On Different Columns'. Focus on the list of problematic functions and the before-and-after query examples.

Based on that reading and our discussion, let's solidify the main anti-patterns.

1. Functions on the Column

This is the most common mistake. It includes date functions (YEAR, MONTH, DATEDIFF), string functions (LEFT, SUBSTRING, UPPER), and type casting (CAST).

Non-SARGable: WHERE YEAR(HireDate) = 2022
SARGable: WHERE HireDate >= '2022-01-01' AND HireDate < '2023-01-01'
Non-SARGable: WHERE LEFT(LastName, 1) = 'S'
SARGable: WHERE LastName LIKE 'S%'

2. Leading Wildcards in `LIKE`

A trailing wildcard is fine, but a leading wildcard makes a query non-SARGable.

Non-SARGable: WHERE LastName LIKE '%son' (Cannot use the index because the search could start anywhere).
SARGable: WHERE LastName LIKE 'John%' (Can use the index to seek to 'John').

This next video explains and demonstrates this specific pattern very clearly.

6 Query Tuning Techniques - Solve 75% SQL Server Performance Problems (by Amit Bansal)

This clip from SQLMaestros focuses specifically on how wildcards affect sargability, showing the execution plan difference between a leading and trailing wildcard.

Watch from 12:30 to 13:20 and then from 21:39 to 23:06. This will reinforce why a leading '%' prevents an index seek.

3. Calculations on the Column

Applying arithmetic to a column also prevents index usage.

Non-SARGable: WHERE Salary / 12 > 4000
SARGable: WHERE Salary > 48000 (Calculation moved to the other side: 4000 * 12)

Test your understanding!

You have a products table with an index on the sku (a text column) and last_updated (a datetime column). Your colleague has written the following queries, but they are running slowly. Identify why each is non-SARGable and rewrite it to be SARGable.

SELECT * FROM products WHERE SUBSTRING(sku, 1, 3) = 'XYZ';
SELECT * FROM products WHERE last_updated + INTERVAL '1 day' > NOW();
SELECT * FROM products WHERE sku LIKE '%-001';

Show answer

Problem: The SUBSTRING function is applied to the indexed sku column.
Rewrite: SELECT * FROM products WHERE sku LIKE 'XYZ%';
Problem: An arithmetic operation (+) is applied to the indexed last_updated column.
Rewrite: SELECT * FROM products WHERE last_updated > NOW() - INTERVAL '1 day'; (The calculation is moved to the right side).
Problem: The LIKE pattern starts with a leading wildcard (%).
Rewrite: This one is tricky. There is no direct SARGable rewrite if you truly need to find SKUs ending in "-001". This might indicate a need for a different indexing strategy (like a reverse-text index, which is an advanced topic) or a data model change (like storing the suffix in a separate column). However, if the goal was just to find SKUs starting with "-001", the rewrite would be WHERE sku LIKE '-001%'. This highlights that sometimes a rewrite isn't possible and you have to reconsider your approach.

Conclusion

You've just learned one of the most practical and high-impact query optimization techniques. By keeping your indexed columns "clean" in your WHERE clauses, you allow the database to do what it does best: use its indexes for lightning-fast data retrieval.

Key Takeaways:

SARGable stands for Search ARGument Able, meaning a WHERE clause can efficiently use an index.
The golden rule is to never apply functions, calculations, or transformations to the indexed column in your predicate. Always manipulate the literal value instead.
Common non-SARGable patterns include using functions like YEAR(), LEFT(), CAST(), calculations like column + 5, and LIKE patterns with a leading wildcard (%value).
Always use EXPLAIN ANALYZE to confirm that your rewritten query has changed from a Scan to a Seek, validating your performance improvement.

Preview of the Next Lesson:
So far, we've focused on optimizing how we filter data from a single table. But most real-world queries involve getting data from multiple related tables. The way you combine this data can have a huge impact on performance. In the next lesson, we will explore another common performance decision: determining when to use a JOIN instead of a correlated subquery.

Recommended resources