James Lean (3/31/2010)
CirquedeSQLeil (3/30/2010)
However, you will find that many prefer the isnull due to an increase in performance.Do you have a supporting link or further information on the performance difference between ISNULL and COALESCE? I was unaware of any significant difference between the two?
COALESCE is implemented as a quite literal implementation of its definition in the ANSI standard (which is: COALESCE(a, b, ...) is equivalent to CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ... ELSE NULL END).
So if any of the arguments is a subquery, the implementation if COALESCE((subquery), ...) becomes CASE WHEN (subquery) IS NOT NULL THEN (subquery) ... ELSE NULL END. And the optimizer that sits a bit further down the line does apparently not realize that the two subqueries are identical, so it happily produces a plan that will evaluate the same subquery twice.
Without the subqueries, I have never witnessed a measurable performance difference.