isnull and coalesce Comparison

Question

isnull and coalesce Comparison

r.dilliprasad

Valued Member

Points: 71
More actions
May 24, 2013 at 7:32 am

#293743

in my case, some source columns values are nulls, i want to populate "NULL" to "0"
where isnull([column_name],0)
coalesce([column_name],0)
i want know both queries giving same result or different
and if same which one is perfomance wise better
and else different what is the difference?:hehe:

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Matthew Darwin SSCertifiable Points: 5513 More actions · Answer 1

With COALESCE you can specify multiple columns and it will return the first non-null value; with ISNULL you can only specify one and is also non standard.

In terms of performance, there's been loads of various posts from time to time over this; eg http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

Personally, I like ISNULL for a single column test and COALESCE for multiple columns as it makes it clear when I'm checking a single column or multiple columns, but that's just me.

Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Also it's important to notice that there is difference in a way which datatype will be used for result

ISNULL will return value of the same datatype as of the first parameter

COALESCE does the same as CASE WHEN expression and returns type of value with highest precedence

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

ISNULL is t-sql dialect and COALESCE is ANSI standard.

The return types are also slightly different.

http://msdn.microsoft.com/en-us/library/ms184325.aspx

http://msdn.microsoft.com/en-us/library/ms190349.aspx

--EDIT--

Eugene beat me to it again. 😛

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/