Using ISNULL vs using COALESCE for checking a specific condition?

Question

I know that multiple parameters can be passed to COALESCE, but when you want to to check just one expression to see if it doesn't exist, do you use a default or is it a better practice to use ISNULL instead?

Is there any performance gain between the two?

Answer 1

This problem reported on Microsoft Connect reveals some differences between COALESCE and ISNULL:

an early part of our processing rewrites COALESCE( expression1, expression2 ) as CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END. In [this example]:

COALESCE ( ( SELECT Nullable
             FROM Demo
             WHERE SomeCol = 1 ), 1 )

we generate:

SELECT CASE
          WHEN (SELECT Nullable FROM Demo WHERE SomeCol = 1) IS NOT NULL
          THEN (SELECT Nullable FROM Demo WHERE SomeCol = 1)
          ELSE 1
       END

Later stages of query processing don't understand that the two subqueries were originally the same expression, so they execute the subquery twice...

One workaround, though I hate to suggest it, is to change COALESCE to ISNULL, since the latter doesn't duplicate the subquery.