Part 3 of 5 · 1 min read
Functions on indexed columns break sargability
Wrapping an indexed column in a function forces the database to compute that function for every row, defeating the index.
Think of asking "find every name whose first three letters, converted to a secret code, equal XYZ" against the same alphabetized phone book. The code has to be computed fresh for every single name before it can be checked, so the alphabetical order gives no shortcut at all.
In plain words: when an indexed column is wrapped inside a function, such as YEAR(placed_on) or UPPER(customer_name), the index stores the original, unwrapped values in sorted order, not the function's output. The database cannot use that sort order to find matches for the wrapped condition, so it must compute the function for every single row and check the result, which is exactly the full scan behavior an index exists to avoid. This is the single most common way a perfectly good index gets silently bypassed.
A worked example. WHERE YEAR(placed_on) = 2026 cannot use an index on placed_on directly, since the index is sorted by the raw date, not by the year extracted from it; rewriting as WHERE placed_on >= '2026-01-01' AND placed_on < '2027-01-01' restores sargability.
The key lines.
-- Non-sargable: forces a function call per row, bypassing the index on placed_on
SELECT * FROM orders WHERE YEAR(placed_on) = 2026;
-- Sargable: an equivalent range condition the index can use directly
SELECT * FROM orders WHERE placed_on >= '2026-01-01' AND placed_on < '2027-01-01';
Non-sargable predicate
A non-sargable predicate wraps an indexed column inside a function or expression, forcing the database to compute that function for every row rather than using the index's existing sort order.
Example: computing a secret code from every name before checking it, instead of using the phone book's existing alphabetical order, mirrors a non-sargable predicate.