ISNULL vs. COALESCE - which to use?

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    This has probably been discussed to death in this forum already, but I have the following question.

    I'm aware of the differences between ISNULL and COALESCE.

    I have read this article:

    Four Rules for NULLs

    http://www.sqlservercentral.com/articles/Advanced+Querying/fourrulesfornulls/1915/

    Here is my question:

    I have a sproc that contains 265 occurencies of COALESCE (and none of ISNULL). No matter how miniscule the performance difference between ISNULL and COALESCE (ISNULL is supposed to be slightly faster), wouldn't it make sense replacing all COALESCE occurencies with ISNULL?

    COALESCE is being used in this case in its simplest form:

    COALESCE (colName, '')

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Jason Selburg

    SSC-Insane

    Points: 24560

    My rulle of thumb is regardless of what "everyone says" is best to run both ways and compare the results. Then you'll know for sure. 😀

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • jezemine

    SSCrazy Eights

    Points: 8059

    perf difference is negligible, not important. pick one you like and use that.

    I prefer coalesce as you can pass multiple args, plus it's ANSI. Also it's harder to spell.

    furthermore, ISNULL is named stupidly. any function that starts with IS should return a BOOL.

    ---------------------------------------
    elsasoft.org

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    what's negligible? 1%?

    even if COALESCE is 1% faster, having it occur hundreds/thousands of times in frequently run code, should still incur a small overhead, would it not?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • jezemine

    SSCrazy Eights

    Points: 8059

    Adam says ISNULL is about 10% faster:

    http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

    but 10% of what? The time your query spends evaluating ISNULL or COALESCE will generally be very tiny compared to the time it spends doing other things, like seeks, scans, joins, etc.

    if you are really curious, write two procs, one using ISNULL only, and the other using COALESCE only. Run each one many times and compare. What I am saying is that there will be no noticable difference.

    ---------------------------------------
    elsasoft.org

  • Johan Bijnens

    SSC Guru

    Points: 134254

    prefer coalesce.

    Not only because it is ansi, but also because it can do more for you.

    isnull(thecol, thereplacement)

    coalesce(thecol,thefirstreplacement, usethisifthefirstreplacementisNULL,...)

    and I've had some issues with sql2000 when using isnull is certain scenarios :Whistling:

    (usage of view on view using isnull and parallel plans)

    ... workaround .... rewrite code to coalesce.:doze:

    Johan


    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt ?

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me[/url] :alien: but most of the time this is me :hehe:

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    jezemine (4/22/2008)


    Adam says ISNULL is about 10% faster:

    http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/performance-isnull-vs-coalesce.aspx

    but 10% of what? The time your query spends evaluating ISNULL or COALESCE will generally be very tiny compared to the time it spends doing other things, like seeks, scans, joins, etc.

    if you are really curious, write two procs, one using ISNULL only, and the other using COALESCE only. Run each one many times and compare. What I am saying is that there will be no noticable difference.

    It's probably also a question of query complexity, size of tables involved, indexing etc.

    This blog reported much more dramatic results:

    http://www.bennadel.com/blog/196-SQL-COALESCE-Very-Cool-But-Slower-Than-ISNULL-.htm

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • GSquared

    SSC Guru

    Points: 260824

    I just ran a simple test:

    create table #NullTest (

    ID int identity primary key,

    Date datetime)

    insert into #nulltest (date)

    select

    case

    when number%10 > 0 then dateadd(day, number, '1/1/2000')

    else null

    end

    from common.dbo.bignumbers

    set statistics io on

    set statistics time on

    declare @Date datetime

    select @date = isnull(date, getdate())

    from #nulltest

    I used both coalesce and isnull in the final query. Coalesce took an average of 350 milliseconds, isnull took an average of 328 milliseconds.

    More complex queries might make a bigger difference, but coalesce was consistently about 8-9% slower in this case.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • jezemine

    SSCrazy Eights

    Points: 8059

    if a few msec on a query is important to you, then use ISNULL.

    I guess what I am saying is, I never came across a system where the bottleneck was use of COALESCE instead of ISNULL. There are always bigger problems than this. Which one you choose to use is not relevant from a practical standpoint.

    ---------------------------------------
    elsasoft.org

  • Grant Fritchey

    SSC Guru

    Points: 395316

    Until you're down to squeezing the last 10-20ms out of a query, I don't think it matters. COALESCE has a lot more flexibility than ISNULL, so, I'd say, if you need the added functionality and you can pay the extra milliseconds, using COALESCE just isn't a problem.

    Still, it's all very interesting information.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • GSquared

    SSC Guru

    Points: 260824

    Grant Fritchey (4/23/2008)


    Until you're down to squeezing the last 10-20ms out of a query, I don't think it matters. COALESCE has a lot more flexibility than ISNULL, so, I'd say, if you need the added functionality and you can pay the extra milliseconds, using COALESCE just isn't a problem.

    Still, it's all very interesting information.

    Keep in mind, this is 20 ms difference on 1-million rows. On smaller queries, the difference is unmeasurable.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    Thanks all for your replies.

    This seems to be more of an academic question then.

    Cheers!

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 12 posts - 1 through 12 (of 12 total)

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