未加星标

A quick note comparing ISNULL vs COALESCE

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

There are hundreds of blogs that compare the T-SQL ISNULL function with the ANSI standard COALESCE function. There are also plenty of arguments of why you should use one over the other.

I’m in the ISNULL camp. I once argued about this with a Microsoft MVP. He was adamant that you should use COALESCE because ISNULL is not an ANSI standard function. My argument was simple. COALESCE is slow. He even mentioned something silly about ISNULL potentially getting deprecated down the road and I responded that it’s still not deprecated in SQL 2016 so it will still be there in SQL 2018 so why not write faster code for what you have today. Right?

He didn’t agree. I left it with the idea that you could write every application to be completely platform agnostic; this way you can just keep moving from platform to platform when you realize the performance is so poor from not taking advantages of platform specific code. Hopefully the next platform will be much faster. Right? hehe.

Let’s discuss why ISNULL is often faster

Without running this or looking below, examine the following statement and tell me what the result for each column should be:

DECLARE @string char (1) = null

SELECT ISNULL (@string, ‘This is NULL’ ) as A,

COALESCE (@string, ‘This is NULL’

)

as

B

Take some time.

Think about it.

Do they return the same result?

Why would I use this example if I’m so concerned about demonstrating speed?

Have you had enough time to think?

Did you come up with an answer?

Was your answer “This is NULL” for both columns?

Was your answer different for each column?

Have I spaced out the answer far enough down the post to where you haven’t instantly read it yet?

The answer is:

Column A = “T”

and Column B = “This is NULL”

How is this possible? Well, I can say that we’ve just demonstrated one extreme difference between the two functions that you may have never noticed before.

ISNULL respects the datatype of the input. COALESCE does not.

What happens if you replace “This is NULL” with an int value such as 1234? Column A would return “*” and B would return 1234.

Understanding this is huge step closer to understanding why ISNULL can often be faster than its ANSI standard cousin COALSECE.

If you enjoyed this post, be sure to check out my other posts on SQL Server Central andSQL Tech Blog.

Also be sure to follow me on Twitter and LinkedIn . I am always happy to get new endorsements and chat with my followers.

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLSNUSQL ServerLinkedInTwitter
分页:12
转载请注明
本文标题:A quick note comparing ISNULL vs COALESCE
本站链接:http://www.codesec.net/view/482812.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(34)