SQL Server Performance

Coalesce

Discussion in 'Getting Started' started by RVG, Jan 20, 2010.

  1. RVG Member

    Hi,
    I was reading about coalesce some books were referring that this function is similar to that of ISNULL. Im getting confused.
    Example:
    COALESCE(expression, 0, 1 // expression can be Varchar, int or any other datatype//
    Isnull(expression,9)
    this ISNull compares whether this expression is null or not and if the expression is null then it replaces with 9.
    Thanks&Regards,
    Ravi Varma.
  2. Adriaan New Member

    ISNULL looks at the first parameter. The second parameter is returned in place of the first parameter only if the first parameter is null.
    COALESCE looks at all parameters, left to right, and returns the first parameter that is not null (if any).
    So similar, but not the same. (Even if COALESCE with the same two parameters in the same order is effectively the same.)
  3. FrankKalis Moderator

    There is one common pitfall when using COALESCE as compared to ISNULL. The return type of COALESCE is the one with the highest data type precedence of all the expressions, while ISNULL returns the same type as the expression that is checked. So, you may run into implicit data type conversion issues when you use COALESCE instead of ISNULL that may or may not lead to suboptimal execution plans.
  4. preethi Member

    Addition to what others have said, COALESCE is something like nested ISNULL functions.
    COALESCE(expression1, expression2,0) is similar to ISNULL(ISNULL(expression1, expression2),0)
    Ask frank mentioned, I have seen COALESCE producing sub optimal plans and I was forced to change them into nested ISNULL
  5. RVG Member

    [quote user="preethi"]
    Addition to what others have said, COALESCE is something like nested ISNULL functions.
    COALESCE(expression1, expression2,0) is similar to ISNULL(ISNULL(expression1, BLOCKED EXPRESSION,0)
    Ask frank mentioned, I have seen COALESCE producing sub optimal plans and I was forced to change them into nested ISNULL
    [/quote]
    Hi Mr. Preethiviraj,
    In Isnull what does this blocked expression means?
    and could not understand what does coalesce does...
    Thanks & Regards,
    Ravi G Varma.
  6. preethi Member

    Hi Ravi,
    [quote user="RVG"]In Isnull what does this blocked expression means?[/quote]
    I just saw that. Its a typo. It should be read as this
    COALESCE(expression1, expression2,0) is similar to ISNULL ( ISNULL(expression1 , expression2 ) ,0)
    I was typing another document at that time and looks like I have placed some of the words from there here. My bad. Now I need to look at that document on what I have written there. It is the characters I have used, has created that expression.
    Sorry for confusing you.
    Hope this helps.
  7. Adriaan New Member

    Say you have this: COALESCE(column1, column2, column3)
    SQL will check the values from column1, column2 and column3 - in that order. The function then returns the first value that is not null.
    So if column1 is null and column2 is 'X', then COALESCE returns 'X' (ignoring column3).
    If both column1 and column2 are null, then the value from column3 is returned.
    You can add as many parameters as you need.
  8. Madhivanan Moderator

    Also the name ISNULL() is misleading. It sounds as if it should return 0 or 1 like ISDATE() [;)]

Share This Page