Using ISNULL vs using COALESCE for checking a specific condition?

coalesce, isnull, sql, t-sql

Solution

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.

Problem

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?

Original source

Related problems