Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# ISNULL vs COALESCE

Posted on 2014-03-17
Medium Priority
221 Views
Hello Experts;

Two questions, are the 2 queries below performing or giving the same results?  And, if so which one should I go with?  When I display the execution plans they are both identical.

Thanks

SELECT (CASE WHEN (COALESCE(c1, '') = '') THEN '' ELSE COALESCE(c1, '') END ) AS c1,
(CASE WHEN (COALESCE(c1, '') = '') THEN '' ELSE COALESCE(c2, '') END ) AS c2,
(CASE WHEN (COALESCE(c1, '') = '') THEN '' ELSE COALESCE(c3, '') END ) AS c3,
(CASE WHEN (COALESCE(c1, '') = '') THEN '' ELSE COALESCE(c4, '') END ) AS c4
, Userid
, ID
, LID
FROM Mant.dbo.View1

SELECT ISNULL(c1, '') AS c1,
ISNULL(c2, '') AS c2,
ISNULL(c3, '') AS c3,
ISNULL(c4, '') AS c4
, Userid
, ID
, LID
FROM Mant.dbo.View1
0
Question by:wdbates
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 66

Accepted Solution

Jim Horn earned 336 total points
ID: 39934621
The only big difference I've discovered between ISNULL and COALESCE is that ISNULL has only two parameters (value, value if null), whereas COALESCE can handle multiple parameters, returning the first non-null one it finds (value1, value2, value3, valueN)

No idea on performance.  I'd guess ISNULL would be quicker, but you'll have to test that yourself.
0

LVL 38

Assisted Solution

Jim P. earned 332 total points
ID: 39934643
The IsNull function can only handle one option. The Coalesce can handle multiple as in COALESCE(c1, c2, c3, c4,'') so it will pick the first non-null value.

So if there is just a need to display an empty string or a 0 then use the IsNull. The Coalesce is for checking against multiple choices. But as you are using them, there is no significant difference.
0

LVL 11

Assisted Solution

John_Vidmar earned 332 total points
ID: 39934671
As stated by the previous experts, COALESCE is an ISNULL that allows for more than one NULL replacement.

I would hate to maintain something written by whoever wrote the 1st query... use the 2nd query.  In fact, you can take the 2nd query and replace ISNULL with COALESCE (all that stuff with CASE-statement in the 1st query is useless).
0

Author Closing Comment

ID: 39934701
Thank you all for the confirmation.
0

LVL 38

Expert Comment

ID: 39934733

0

Author Comment

ID: 39934984
It's because I must have had a brain fart while driving the mouse.  I will ask for it to be changed.

thank you for catching my error.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…