sql - 使用 ISNULL 与使用 COALESCE 来检查特定条件？

Question

我知道可以将多个参数传递给COALESCE，但是当您想要时仅检查一个表达式以查看它是否不存在，您是否使用默认值，或者使用 ISNULL 代替是更好的做法？

两者之间有性能提升吗？

Answer 1

This problem reported on Microsoft Connect揭示了 COALESCE 和 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.