本文介绍了ISNULL和COALESCE正确用法的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

由于我们有两个选择拦截从数据库里的空值...

Following are the ways to write the query for the above two functions...

Select IsNull(Columnname, '') As validColumnValue From TableName

Select Coleasce(Columnname, '') As validColumnValue From TableName

Query - Which should be prefered in which situation and why?

解决方案

This has been hashed and re-hashed. In addition to the tip I pointed out in the comment and the links and explanation @xQbert posted above, by request here is an explanation of COALESCE vs. ISNULL using a subquery. Let's consider these two queries, which in terms of results are identical:

SELECT COALESCE((SELECT TOP (1) name FROM sys.objects), N'foo');

SELECT ISNULL((SELECT TOP (1) name FROM sys.objects), N'foo');

(Comments about using TOP without ORDER BY to /dev/null/ thanks.)

In the COALESCE case, the logic actually gets expanded to something like this:

SELECT CASE WHEN (SELECT TOP (1) ...) IS NULL
    THEN (SELECT TOP (1) ...)
    ELSE N'foo'
END

With ISNULL, this does not happen. There is an internal optimization that seems to ensure that the subquery is only evaluated once. I don't know if anyone outside of Microsoft is privy to exactly how this optimization works, but you can this if you compare the plans. Here is the plan for the COALESCE version:

And here is the plan for the ISNULL version - notice how much simpler it is (and that the scan only happens once):

In the COALESCE case the scan happens twice. Meaning the subquery is evaluated twice, even if it doesn't yield any results. If you add a WHERE clause such that the subquery yields 0 rows, you'll see similar disparity - the plan shapes might change, but you'll still see a double seek+lookup or scan for the COALESCE case. Here is a slight different example:

SELECT COALESCE((SELECT TOP (1) name FROM sys.objects
    WHERE name = N'no way this exists'), N'foo');

SELECT ISNULL((SELECT TOP (1) name FROM sys.objects
    WHERE name = N'no way this exists'), N'foo');

The plan for the COALESCE version this time - again you can see the whole branch that represents the subquery repeated verbatim:

And again a much simpler plan, doing roughly half the work, using ISNULL:

You can also see this question over on dba.se for some more discussion:

http://dba.stackexchange.com/questions/4274/performance-difference-for-coalesce-versus-isnull

My suggestion is this (and you can see my reasons why in the tip and the above question): trust but verify. I always use COALESCE (because it is ANSI standard, supports more than two arguments, and doesn't do quite as wonky things with data type precedence) unless I know I am using a subquery as one of the expressions (which I don't recall ever doing outside of theoretical work like this) or I am experiencing a real performance issue and just want to compare to see if COALESCE vs. ISNULL has any substantial performance difference (which outside of the subquery case, I have yet to find). Since I am almost always using COALESCE with arguments of like data types, I rarely have to do any testing other than looking back at what I've said about it in the past (I was also the author of the aspfaq article that xQbert pointed out, 7 years ago).

这篇关于ISNULL和COALESCE正确用法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-05 17:31
查看更多