Post reply

Twist in ISNULL function

  • February 2, 2012 at 4:29 am

    #1441662

    I never use IsNull.

    I always use COALESCE.

  • February 2, 2012 at 5:02 am

    #1441682

    carlos.magno (2/2/2012)

    I never use IsNull.

    I always use COALESCE.

    Are you just sharing that fact, or suggesting that as a practice others should follow?

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 2, 2012 at 5:23 am

    #1441705

    Good article...but I would also like to see an article or explanation on the issues raised by SQL Kiwi because, even after reading those two links, I'm still not 100% sure if I understand why COALESCE exhibits those behaviors...especially regarding the isolation levels.

    Thanks, George

  • February 2, 2012 at 5:32 am

    #1441718

    Having this:

    "The report that needs to be produced should have the questions in the column headers and the responses in the data cells."

    "These questions and their responses are fed through a front end interface by the agent handling the customer. Hence, there may be a question popping up that is not there in the pre-decided list. For example:"

    I thought you were going to explain how to build that kind of report, where the number of columns is dynamic.... maybe in other article.

    Thank you.

  • February 2, 2012 at 5:33 am

    #1441720

    I don't thought as a suggestion. Just as a fact.

    But, anyway, may you consider as a suggestion from this moment 🙂

  • February 2, 2012 at 5:35 am

    #1441722

    The "BIG" problems that this Microsoft articles describes with Coalesce are just if you are using a subquery inside the function.

    But I guess that is not the most common use of this function.

    So I will continue using COALESCE instead of ISNULL.

  • February 2, 2012 at 6:19 am

    #1441759

    Thanks! I had run into a similar problem, ISNULL was truncating the results.

  • February 2, 2012 at 6:20 am

    #1441760

    I prefer to use COALESCE in all cases. However, up to this, the only major difference I saw was the fact that ISNULL could be used in calculated columns for keys. Since ISNULL is considered unnullable where as COALESCE is not. But this was academic since I have never needed to have a calculated column in a primary key that was based on coalescence of another column.

  • February 2, 2012 at 6:51 am

    #1441777

    Not only is it documented behavior, but expected behavior. As a replacement value it only seems logical that it would replace the value in the variable, and not replace the entire variable itself. However, it is a good example of how this could be accidentally misused, and considering it may save someone time debugging I wouldn't suggest that it's poor.

  • February 2, 2012 at 6:53 am

    #1441780

    George H. (2/2/2012)

    Good article...but I would also like to see an article or explanation on the issues raised by SQL Kiwi because, even after reading those two links, I'm still not 100% sure if I understand why COALESCE exhibits those behaviors...especially regarding the isolation levels.

    Hi George, it would make an interesting blog post or SSC question of the day, perhaps, but I'm not sure there's enough value in it for an article. The thing with COALESCE is mainly that people in general don't realize it is just shorthand for a CASE statement, and the CASE 'test' can be repeated with unexpected results, especially if a non-deterministic function like RAND is used, or there is a subquery. I'm not against COALESCE particularly; both it and ISNULL have different advantages in different circumstances. Anyone that says to *always* use one or the other has much left to learn about SQL Server 🙂

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 2, 2012 at 6:54 am

    #1441781

    carlos.magno (2/2/2012)

    I don't thought as a suggestion. Just as a fact.

    But, anyway, may you consider as a suggestion from this moment 🙂

    I'll just ignore it, since you haven't presented any arguments either way 😛

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 2, 2012 at 6:57 am

    #1441785

    carlos.magno (2/2/2012)

    The "BIG" problems that this Microsoft articles describes with Coalesce are just if you are using a subquery inside the function.

    But I guess that is not the most common use of this function.

    So I will continue using COALESCE instead of ISNULL.

    Spot the subquery:

    SELECT COALESCE(CASE WHEN RAND() <= 0.5 THEN 999 END, 999);

    Explain how that should return NULL from time to time (as it does).

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • February 2, 2012 at 6:57 am

    #1441784

    Sorry, but I had too much trouble getting past the poor design.

    i.e. instead of writing a query and appending the short descriptions via query, the original table should have been modified to carry the short version of the questions.

    I won't even get into the fact tha the questions should be in their own tables with the answers related to them via foreign keys and the rest of normalization.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • February 2, 2012 at 7:01 am

    #1441790

    Isn't COALESCE ANSI Standard?

  • February 2, 2012 at 7:06 am

    #1441796

    rwright-834262 (2/2/2012)

    ...However, it is a good example of how this could be accidentally misused, and considering it may save someone time debugging I wouldn't suggest that it's poor.

    When you rate SSC articles, there are five possible star ratings: awful, poor, average, good, and excellent. I went for poor, on the basis that it isn't awful, but I hope it isn't as good as average either 😉

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply