ISNULL vs COALESCE

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
wdbatesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jim P.Commented:
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
John_VidmarCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

wdbatesAuthor Commented:
Thank you all for the confirmation.
0
Jim P.Commented:
Can I ask why a B grade?

What grade should I award?
0
wdbatesAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.