Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Which is better (Case, IsNull, COALESCE) Expand / Collapse
Author
Message
Posted Tuesday, November 19, 2013 4:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 10, 2014 9:09 PM
Points: 8, Visits: 51
1. Case When mycolumn IS Null Then 0 Else mycolumn END As mycolumn
OR
2. IsNull(mycolumn,0) As mycolumn
OR
3. COALESCE(mycolumn,0) As mycolumn
Post #1515837
Posted Tuesday, November 19, 2013 5:11 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:52 AM
Points: 1,780, Visits: 5,659
In this case, I would plump for 2.

Reason 1 : #1 and #3 are the same behind the scenes, but #3 is shorter and more readable, so eliminate #1
Reason 2 : ISNULL will convert your default value of zero to whatever datatype myColumn has, which in this case would be preferable.
Reason 3 : COALESCE can evaluate the expressions being coalesced more than once, which can lead to unexpected results.
Reason 4 : you are only handling one expression and a default value for null, so you don't need coalesce.


MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1515840
    Posted Tuesday, November 19, 2013 5:39 PM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Saturday, May 10, 2014 9:09 PM
    Points: 8, Visits: 51
    Thanks..
    So Do I understand correctly that coalesce would be best in a situation like this>
    COALESCE(myalpacolumn,mynumcolumn,mybinarycolumn,'') As mycolumn
    Post #1515849
    Posted Tuesday, November 19, 2013 5:42 PM


    SSCommitted

    SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

    Group: General Forum Members
    Last Login: Today @ 8:52 AM
    Points: 1,780, Visits: 5,659
    I didn't say that

    ...but yes, generally I would advocate COALESCE for that use.


    MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1515851
    Posted Tuesday, November 19, 2013 5:54 PM


    Hall of Fame

    Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

    Group: General Forum Members
    Last Login: Today @ 10:22 AM
    Points: 3,336, Visits: 7,200
    Remember to have in mind the data type that ISNULL and COALESCE return.
    ISNULL will try to convert the second expression to the data type of the first expression.
    COALESCE will convert all expressions to the expression with the highest data type precedence.



    Luis C.
    I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

    Forum Etiquette: How to post data/code on a forum to get the best help
    Post #1515854
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse