Solved

ISNULL vs COALESCE

Posted on 2014-03-17
7
217 Views
Last Modified: 2014-03-20
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
Comment
Question by:wdbates
7 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 84 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

by:Jim P.
Jim P. earned 83 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

by:John_Vidmar
John_Vidmar earned 83 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

Author Closing Comment

by:wdbates
ID: 39934701
Thank you all for the confirmation.
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39934733
Can I ask why a B grade?

What grade should I award?
0
 

Author Comment

by:wdbates
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question