Mastering Query Rewriting and Simplification
In this lesson, you will learn how to rewrite complex SQL expressions into simpler, semantically equivalent forms that allow the database engine to utilize indexes effectively.
The Problem: Obscured Predicates
When you apply a function or perform math on a column within a WHERE clause, you create what is known as a non-SARGable (Search ARGumentable) predicate. Even if an index exists on that column, the database engine often cannot use it because it must calculate the result of the function for every single row in the table before it can compare it. This forces the engine to perform a costly Full Table Scan instead of an efficient Index Seek.
The Solution: Algebraic Rewriting
To help the optimizer, we rewrite these expressions to isolate the indexed column. By shifting the calculation to the constant side of the operator, the database can look up the value directly in the index.
Consider an Employees table with an index on BirthDate. If you want to find everyone born in 1990, you might be tempted to write:
-- Inefficient: Function on the column prevents Index Seek
SELECT EmployeeName
FROM Employees
WHERE YEAR(BirthDate) = 1990;
Because of the YEAR() function, the engine ignores the index. We can rewrite this by defining a range, which keeps the BirthDate column "naked" (free from functions):
-- Efficient: Column is isolated, enabling Index Seek
SELECT EmployeeName
FROM Employees
WHERE BirthDate >= '1990-01-01' AND BirthDate < '1991-01-01';
Why This Works
By converting the logic into a range comparison, we transform the query from a "compute-every-row" task into a "find-the-boundaries" task. The database engine can navigate the index tree to find the exact starting point ('1990-01-01') and stop immediately after the end point ('1991-01-01'), dramatically reducing the number of I/O operations.
Rewriting isn't just about cleaning up code; it is about respecting the data structure of your indexes. Whenever you are tempted to wrap a column in a function, ask yourself if you can mathematically rearrange the logic to keep the column untouched.
Key Takeaway: Always isolate indexed columns in your predicates by avoiding functions or calculations on the left-hand side of your comparison operators.