合并与案例 [英] Coalesce vs Case

问题描述

我有 5 个字段，它们带回了值和 NULL 的混合.出于报告目的，我需要用一个值替换任何潜在的 NULLS.我正在查询的数据库每晚通过 SSIS 包更新.

I have 5 fields which are bringing back a mixture of values and NULLS. For reporting purposes I need to replace any potential NULLS with a value. The database that I am interrogating is updated nightly via an SSIS package.

显然，我需要更改 .dtsx 文件，以通过添加一些 SQL 来阻止每天引入 NULL.

Obviously I will need to alter the .dtsx file to stop NULLS being brought through each day by adding some SQL.

我的问题是:

就性能而言，处理这些 NULL 的最有效方法是什么.到目前为止，我确定了 COALESCE 和 CASE 来处理它们，我倾向于 COALESCE 因为我对 NULL 的替代是不会改变，但我很想知道这是否以及为什么这是最有效的方法.

What is the most efficient way of dealing with these NULLS in terms of performance. So far ive identified COALESCE and CASE to deal with them and im leaning towards COALESCE because my alternative to NULL is not going to change, but I would be interested to hear if and why this would be the most efficient method.

推荐答案

COALESCE() 字面上是 CASE 语句的简写，它们将执行相同的操作.

COALESCE() is literally shorthand for a CASE statement, they will perform identically.

然而，正如 podiluska 提到的，ISNULL() 有时可能比 CASE 语句更快，但这可能是微不足道的增加，因为这些函数不太可能阻碍您的程序.

However, as podiluska mentioned, ISNULL() can be occasionally faster than a CASE statement, but it's likely to be a miniscule increase as these functions are very unlikely to bottleneck your procedure.

在此处阅读有关性能差异的更多信息.

Read here more about the performance differences.

这篇关于合并与案例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！