COALESCE in a WHERE Clause Costs You the Row Estimate
Optional filter parameters are everywhere in reporting procedures. Pass a value and you want that value; pass NULL and you want everything.
There are two common ways to write it, and I have carried a rule for years that says use the first one:
|
1 2 3 4 5 6 7 8 9 10 |
/* The OR pattern */ WHERE ( [ro].[office_number] = @office_number OR @office_number IS NULL ); /* The COALESCE pattern */ WHERE [ro].[office_number] = COALESCE(@office_number, [ro].[office_number]); |
My note explaining the rule said COALESCE “blocks index seeks”. I went to measure that so I could write it up properly, and the measurement disagreed.
The setup
100,000 rows, 500 distinct office numbers, so 200 rows per value. A clustered index on the filter column. Four stored procedures, differing only in the WHERE clause and whether they carry OPTION (RECOMPILE).
Don’t use local variables for this test. A procedure parameter is sniffed at compile time so the optimizer can use the histogram. A DECLARE variable isn’t, which changes how the query executes.[1]
Each one executed with @office_number = 42, captured as an actual plan.
What actually happened
| Variant | Operator | Estimated rows | Actual rows |
|---|---|---|---|
OR @param IS NULL |
Clustered Index Scan | 200 | 200 |
OR @param IS NULL + RECOMPILE |
Clustered Index Seek | 200 | 200 |
COALESCE(@param, col) |
Clustered Index Scan | 10,000 | 200 |
COALESCE(@param, col) + RECOMPILE |
Clustered Index Seek | 200 | 200 |
Measured on SQL Server 2019 (15.0.4480.2). The COALESCE rows and both RECOMPILE rows reproduced identically on SQL Server 2025 (17.0.1125.2).
Read the second column first. Neither pattern seeks. Both scan all 100,000 rows to return 200. And both seek once OPTION (RECOMPILE) is added, COALESCE included.
So COALESCE does not block index seeks. My note was wrong, and it had been wrong for a long time, because the rule it justified happened to be right.
Where the difference actually is
Look at the third column instead.
The OR form estimated 200 rows and returned 200. The COALESCE form estimated 10,000 and returned 200, an overestimate of about fiftyfold.
10,000 is 10% of 100,000, and a round fraction of table cardinality is the fingerprint of an estimate made without a usable statistic.[2] The histogram was not read, so this is a fallback guess rather than a lookup that went wrong.
The reason is visible in the predicate the plan carries for the COALESCE scan:
|
1 2 3 |
[office_number] = CASE WHEN [@office_number] IS NOT NULL THEN [@office_number] ELSE [office_number] END |
COALESCE is shorthand for a CASE expression.[3] The column now appears on both sides of the comparison, wrapped in a conditional, and the estimator cannot resolve that to a value it could look up. It falls back to a fixed guess.
The OR form leaves [office_number] = @office_number intact as a predicate over a column, and the sniffed value goes straight to the histogram.
Why an estimate is worth caring about
On this query the wrong estimate changes nothing measurable. Both plans scan, both take about 5 ms, and the row estimate is a number in a tooltip.
It matters because the estimate is an input to every decision above it. Join a fiftyfold overestimate to another table and the optimizer may choose a hash join where a loop join would have been right, or the reverse. It sizes the memory grant from estimated rows, so a query that needs a few hundred rows worth of workspace can request enough for ten thousand and hold that reservation while it runs. It compares the estimated cost against the parallelism threshold.
None of that shows up in a two-table test. All of it shows up in the procedure that test was standing in for.
The plan you get is usually the correct plan for the row counts the optimizer was handed. When the plan is wrong, the row counts are usually where to look first.
What about RECOMPILE
Both patterns seek with OPTION (RECOMPILE), so it is fair to ask whether the choice of pattern matters at all once you add it.
For a single-table query like this one, much less than I assumed. RECOMPILE lets the optimizer embed the runtime value and optimize for the call in front of it, and from there both forms are seeks with accurate estimates.[4]
The cost is a compile on every execution. For a reporting procedure that runs a few times an hour that is nothing. For one that runs hundreds of times a second it is a real CPU cost, and that is the case where the pattern still matters, because you are unlikely to be adding RECOMPILE.
Without RECOMPILE, one cached plan has to serve both the filtered call and the unfiltered one. It will be a scan either way. The OR form at least gets the row count right on the way through.
The rule
Use OR @param IS NULL for optional filters. Add OPTION (RECOMPILE) when the parameter is genuinely optional and the selective case is worth a seek.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT [ro].[office_id] , [ro].[office_name] FROM [dbo].[registered_office] AS [ro] WHERE ( [ro].[office_number] = @office_number OR @office_number IS NULL ) OPTION (RECOMPILE); |
The rule holds. The reason I had written down for it was wrong, and I would rather have the right reason, because the wrong one leads you to apply the rule where it does not fit.
The same instinct to wrap a column in a function shows up in the ISNULL habit, which has its own way of losing data: ISNULL Truncates Your Replacement Value; COALESCE Doesn’t. This is one of a series on the T-SQL conventions I actually use and why.
Have you found a rule of your own that was right for the wrong reason? Bluesky or LinkedIn.
References
- Query Processing Architecture Guide - Microsoft Learn. Covers parameter sniffing at compile time and why local variables are estimated differently from parameters. ↩
- Cardinality Estimation (SQL Server) - Microsoft Learn. How row estimates are produced, and what happens when no useful statistic is available for a predicate. ↩
- COALESCE (Transact-SQL) - Microsoft Learn. Documents that COALESCE is rewritten as a CASE expression, which is what the plan predicate shows. ↩
- Query Hints (Transact-SQL) - Microsoft Learn. RECOMPILE and its effect on optimizing for the values supplied at execution. ↩