使用 ISNULL 还是使用 COALESCE 检查特定条件? [英] Using ISNULL vs using COALESCE for checking a specific condition?

问题描述

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

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?

推荐答案

Microsoft Connect 上报告的这个问题 揭示了COALESCE 和ISNULL 之间的一些差异:

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

我们处理的早期部分将 COALESCE( expression1, expression2 ) 重写为 CASE WHEN expression1 IS NOT NULL THEN expression1 ELSE expression2 END.在[这个例子]中:

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 )

我们生成:

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

一种解决方法，虽然我不想建议它，但将 COALESCE 更改为 ISNULL，因为后者不复制子查询.

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

这篇关于使用 ISNULL 还是使用 COALESCE 检查特定条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！