Home Forums Article Discussions Article Discussions by Author Discuss content posted by Patrick Cahill Returning 0's in a SUM (SQL Spackle) RE: Returning 0's in a SUM (SQL Spackle)

  • June 14, 2011 at 10:06 am

    #1338505

    Gatekeeper (6/14/2011)

    Alexey Voronin (6/14/2011)

    What's this COALESCE(SUM(SalesTestData.SaleQty),0) ?

    You do not know ISNULL ?

    ISNULL(SUM(SalesTestData.SaleQty),0)

    They are interchangeable. Both are the same thing except COALESCE() allows for more than one evaluation.

    Actually, they're not interchangeable at the semantic level. COALESCE is slower than ISNULL (although it takes a substantial number of rows to tell). COALESCE is in the ANSI standard where ISNULL is proprietary. COALESCE can change the datatype of the result based on which non-null element is selected (which can cause a major performance problem, BTW). ISNULL will keep the datatype of the return the same as the datatype of the first operand. COALESCE takes more keystrokes to type than ISNULL (BIG selling point for me. :-P)

    Heh... other than that, they're interchangeable when two operands are necessary. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)