在 SQL Server 中使用 ISNULL() 时是否有任何性能问题？

Is there is any Performance issue while using ISNULL() in SQL Server?

提问by kbvishnu

I am using ISNULLin MS SQl server 2008, since my table is too huge, whether using of ISNULLmay cause any thing to the performance ?.

我ISNULL在 MS SQl server 2008 中使用，由于我的表太大，使用 of 是否ISNULL会对性能造成任何影响？。

Thanks in advance

提前致谢

采纳答案by gbn

If you need to use it, then any differences between ISNULL and alternatives like COALESCE or CASE are minuscule. Don't worry about it

如果您需要使用它，那么 ISNULL 和 COALESCE 或 CASE 等替代方案之间的任何差异都是微不足道的。别担心

Any differences come from how datatypes are handled. COALESCE/CASE can add implicit data type conversions whereas ISNULL has simpler rules.

任何差异都来自于数据类型的处理方式。COALESCE/CASE 可以添加隐式数据类型转换，而 ISNULL 具有更简单的规则。

Edit

编辑

ISNULL in the SELECT list to suppress NULLS is trivial. The main work will done in processing rows and data. An extra ISNULL won't be measurable: Don't optimise prematurely

在 SELECT 列表中使用 ISNULL 来抑制 NULLS 是微不足道的。主要工作将在处理行和数据方面完成。额外的 ISNULL 将无法衡量：不要过早优化

回答by Heino Zunzer

ISNULL() in the select-clause has neglible influence on the performance. In the where-clause on the other hand it can have a very huge impact on performance, since it prevents the optimizer for using an index on that column.

select 子句中的 ISNULL() 对性能的影响可以忽略不计。另一方面，在 where 子句中，它会对性能产生非常巨大的影响，因为它会阻止优化器在该列上使用索引。

where isnull(col1, 0) = 0 -- unable to use index, because every 
                          -- row has to be evaluated

where col1 = isnull(@myVar, 0) -- index will be used, since isnull(@myVar, 0) 
                               -- returns the same static value for every row and 
                               -- not every row has to be evaluated by the function.

So, when using isnull() in a where-clause, evaluate if it prevents the query optimizer from using an index. If so, consider creating a computed column with the result if isnull(col1, 0) and index the computed column and use it in your where-clause.

因此，在 where 子句中使用 isnull() 时，请评估它是否阻止查询优化器使用索引。如果是这样，请考虑创建一个具有结果 if isnull(col1, 0) 的计算列并索引计算列并在您的 where 子句中使用它。

回答by Oleg Dok

Yes it can. For optimizer is better rewrite the query (if possible) to form

是的，它可以。对于优化器最好重写查询（如果可能）以形成

(Field = @x OR @x IS NULL)

Because using functions in certain cases prevents from optimizer to use statistics and sometimes forced implicit datatype conversions

因为在某些情况下使用函数会阻止优化器使用统计信息，有时会强制隐式数据类型转换

回答by user2501716

Avoid using isNull in where clause. Refer This article.

避免在 where 子句中使用 isNull。参考这篇文章。

回答by Teng L

Yes there is a performance issue afaik, in SQL Server Studio 2012.

是的，SQL Server Studio 2012 中存在性能问题。

The problem is quite glaring when I used ISNULLin combination with OVER. After optimizing (i.e. putting ISNULLin the sub-query I am using OVERon) run time reduced from (estimated) 25.2 hours down to 102 seconds.

当我ISNULL与OVER. 优化后（即放入ISNULL我正在使用的子查询OVER）运行时间从（估计）25.2 小时减少到 102 秒。

My guess is ISNULLis OK when you run it over an entire column (for example, in a plain-ol' SELECT). But when you run it with OVERit is called anew every time, thus dragging down performance.

ISNULL当您在整个列上运行它时（例如，在一个普通的 ol' 中SELECT），我的猜测是可以的。但是当你运行它OVER时，每次都会重新调用它，从而拖累性能。

Not ready to drill down further. Simply putting it here for others' reference.

不准备进一步深入研究。简单地放在这里供其他人参考。

回答by Steve A

Watch out if using ISNULL with a sub query as the replacement_value.

注意是否使用带有子查询的 ISNULL 作为replacement_value。

Seems like it runs the sub query even if the check_expression value is not NULL which doesn't help performance.

似乎它运行子查询，即使 check_expression 值不是 NULL 这对性能没有帮助。

CASE performs as expected.

CASE 按预期执行。

回答by vmvadivel

As it has been already mentioned it depends on how and where you are using it in your query. May be you might want to show the way you are using it in your query.

正如已经提到的，这取决于您在查询中使用它的方式和位置。可能您想显示您在查询中使用它的方式。

Also I would recommend you to go over this - What makes a SQL statement sargable?

此外，我建议您阅读此内容 -是什么让 SQL 语句变得可搜索？

回答by demas

It depends on how you are useing it, but you can build execution plansin the both cases (with ISNULL() and without it) and compare the results.

这取决于您如何使用它，但您可以在两种情况下（使用 ISNULL() 和不使用它）构建执行计划并比较结果。