Examine the plan for Query 2 in Figure 1 and observe that the clustered index of the OrderDetails table is scanned twice. This results in
a scan count of 2 and a logical reads count of 22. This is a direct result
of the fact that COALESCE was internally translated to a CASE expression that refers to the subquery twice—once to check if its result is
NULL and again to return the result because it isn’t NULL.
Implement a solution using ISNULL by running the following query
(call it Query 3):
SELECT ISNULL( (SELECT SUM(qty) FROM Sales.OrderDetails), 0 );
Examine the plan for Query 3 in Figure 1 and observe that the clustered index of the OrderDetails table is scanned only once. This results
in a scan count of 1 and a logical reads count of 11.
In this particular case, you could first store the result of the subquery in a variable and use the variable in the COALESCE expression,
but that’s not always possible. For example, consider a case where
the subquery is a correlated one appearing in some outer query
against another table. So from this perspective, ISNULL is preferred
to COALESCE.
When you’re done, run the following code to turn off reporting I/O
statistics in the session:
SET STATISTICS IO OFF;
Atomicity vs. Isolation
This section covers another example for the implications of SQL
Server’s conversion of a COALESCE expression to a CASE expression.
Before I present the example, try to think of a scenario in which the
expression COALESCE( <subquery>, 0 ) returns a NULL.
