section and the next section describe a couple of interesting examples
that are affected by this behavior.
Suppose that you need to write an expression that returns the result
of a subquery when it’s not NULL and returns zero otherwise. Before
trying different solutions, run the following code in order to request
I/O statistics for queries in your session:
SET STATISTICS IO ON;
Run the following query (call it Query 1):
SELECT SUM(qty) FROM Sales.OrderDetails;
Observe the plan for Query 1 in Figure 1 (the first of the three plans
in the figure).
Figure 1
COALESCE vs. ISNULL
with Subqueries
The clustered index of the OrderDetails table is scanned once,
resulting in a scan count of 1 and a logical reads count of 11. Next,
try using COALESCE to implement the task at hand by running the
following query (call it Query 2):
www.sqlmag.com
sql server Pro / may 2013
