Using ISNULL vs using COALESCE for checking a specific condition?

Solution 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.

Solution 2:

I think not, but COALESCE is in the SQL '92 standard and supported by more different databases. If you go for portability, don't use ISNULL.

Solution 3:

In COALESCE you can have multiple expressions, where as in ISNULL you can check only one expression

COALESCE ( expression [ ,...n ] ) 

ISNULL ( check_expression , replacement_value )