Best Practice : coalesce vs. isnull?

  • Just wondering,

    I like to use ISNULL(column_name, 0) while my friend like to use COALESCE(column_name, 0).

    We both try using ISNULL and COALESCE in our query and there is no different result.

    In Books online, it's said that both behave differently. There's also the explanation, but I just don't

    understand it completely :p

    Is there any article regarding this topic?

    And which one you usually use (ISNULL or COALESCE) and why you use it?

    For now, I'll keep my habit using ISNULL, so will my friend keep using COALESCE 😀

    Thanx for any explanation, link, or you opinion 🙂

  • Prior to SQL 2008 ISNULL was considerably faster than COALESCE. This makes sense as the code behind ISNULL has to deal with a fixed number of input variables (2) and COALESCE has to be designed to work with any number of variables, so this will invariably involve extra computations.

    In SQL 2008, I've seen a thread where people say that the performance is now for all intent and purpose the same for both (I wonder if the optimiser just converts it to ISNULL if there are only 2 variables).

    Personally, if I'm only working with two values, then I'll use ISNULL and if I need more I'll use COALESCE in the spirit in which the two functions were designed.

  • Howard has provided a pretty good explanation. Use ISNULL when you only have 2 option, COALESCE with more than 2. They also handle data types and lengths differently. Try this:

    SELECT

    ISNULL(Nullif('abc', 'abc'), '123456') AS using_isnull,

    COALESCE(Nullif('abc', 'abc'), '123456') AS using_coalesce,

    ISNULL(Nullif('abc', 'abc'), 123456) AS int_using_isnull,

    COALESCE(Nullif('abc', 'abc'), 123456) AS int_using_coalesce

  • Since I prefer as much consistency as possible, I try to stick to Coalesce. There are times when I can't use IsNull and have to use Coalesce, but there aren't times when I have to use IsNull and can't use Coalesce. So, I try to use Coalesce.

    - 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

  • Just to expand on Jack's point a bit, because it is an important one:

    COALESCE( expression [ ,...n ] ) returns the data type of the expression with the highest data type precedence.

    ISNULL(check_expression, replacement_value) returns the same type as check_expression.

    So:

    SELECT ISNULL(CAST(NULL AS INT), 5.5) -- Returns 5

    SELECT COALESCE(CAST(NULL AS INT), 5.5) -- Returns 5.5

    SELECT DATALENGTH(ISNULL(CAST(NULL AS VARCHAR(5)), N'Hello')) -- Returns 5

    SELECT DATALENGTH(COALESCE(CAST(NULL AS VARCHAR(5)), N'Hello')) -- Returns 10

  • actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.

    coalesce(custname, last, first) = isnull( isnull( custname, last ), first )

    Therefore a coalesce is basically a nested isnull.

  • BaldingLoopMan (12/14/2009)


    actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.

    coalesce(custname, last, first) = isnull( isnull( custname, last ), first )

    Therefore a coalesce is basically a nested isnull.

    True. Would be a pain to type out, be essentially unreadable, and I'd hate to have to maintain something that nested 5 or 10 deep, but it could be done.

    I'll still stick with Coalesce.

    - 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

  • To each their own.

  • BaldingLoopMan (12/14/2009)


    actually u can use isnull in all situations if u wanted to. A coalesce is a nested isnull.

    coalesce(custname, last, first) = isnull( isnull( custname, last ), first )

    Therefore a coalesce is basically a nested isnull.

    That is not totally accurate as noted by the posts by myself and Paul White where you can see the IsNull and Coalesce functions are different. If you run this query:

    DECLARE @test-2 TABLE (colA CHAR(3))

    INSERT INTO @test-2 (

    colA

    )

    SELECT

    'abc'

    UNION ALL

    SELECT

    NULL

    SELECT

    ISNULL(colA, '123456') AS using_isnull,

    COALESCE(colA, '123456') AS using_coalesce

    FROM

    @test-2

    and look at the exection plan you will see that SQL Server keeps the IsNull function for ISNULL in Compute Scalar operator, but converts the COALESCE column to a CASE statement.

  • Yea. i didn't follow what u guys were saying before.

    I see what your saying now. Wow. That's crazy and good to know.

  • BaldingLoopMan (12/14/2009)


    To each their own.

    Even in cases where the data type precedence won't cause a problem, I find the Coalesce version below much easier to read, understand, and maintain, than the second:

    if object_id(N'tempdb..#People') is not null

    drop table #People;

    create table #People (

    ID int identity primary key,

    NamePrefix varchar(100),

    NameFirst varchar(100),

    NameMiddle varchar(100),

    NameLast varchar(100),

    NameSuffix varchar(100));

    insert into #People (NamePrefix, NameFirst, NameMiddle, NameLast, NameSuffix)

    select null, 'Adam', null, 'Abrahms', null union all

    select '', 'Bob', null, 'Birch', null union all

    select 'Dr', 'Carl', 'C', 'Carlson', 'PhD' union all

    select 'Rev', null, null, 'Dodgy', 'Jr';

    ;with Cleanup (Prefix, First, Middle, Last, Suffix) as

    (select IsNull(NullIf(NamePrefix, ''), 'Mr/Ms'), NullIf(NameFirst, ''), NullIf(NameMiddle, ''),

    NullIf(NameLast, ''), NullIf(NameSuffix, '')

    from #People)

    select

    coalesce(

    Prefix + ' ' + First + ' ' + Middle + ' ' + Last + ', ' + Suffix,

    Prefix + ' ' + First + ' ' + Middle + ' ' + Last,

    Prefix + ' ' + First + ' ' + Last + ', ' + Suffix,

    Prefix + ' ' + First + ' ' + Last,

    Prefix + ' ' + Last + ', ' + Suffix,

    Prefix + ' ' + Last)

    from Cleanup;

    ;with Cleanup (Prefix, First, Middle, Last, Suffix) as

    (select IsNull(NullIf(NamePrefix, ''), 'Mr/Ms'), NullIf(NameFirst, ''), NullIf(NameMiddle, ''),

    NullIf(NameLast, ''), NullIf(NameSuffix, '')

    from #People)

    select

    IsNull(Prefix + ' ' + First + ' ' + Middle + ' ' + Last + ', ' + Suffix,

    IsNull(Prefix + ' ' + First + ' ' + Middle + ' ' + Last,

    IsNull(Prefix + ' ' + First + ' ' + Last + ', ' + Suffix,

    IsNull(Prefix + ' ' + First + ' ' + Last,

    IsNull(Prefix + ' ' + Last + ', ' + Suffix,

    Prefix + ' ' + Last)))))

    from Cleanup;

    Yes, they produce the same results, but if you want to add another combination and precedence to the first one, it's easier than the second one.

    (This is an actual function I had to build for processing names for mailing lists. It's a real-world example.)

    Even if you change the layout of the second so that the precedences line up nicely like the first one:

    ;with Cleanup (Prefix, First, Middle, Last, Suffix) as

    (select IsNull(NullIf(NamePrefix, ''), 'Mr/Ms'), NullIf(NameFirst, ''), NullIf(NameMiddle, ''),

    NullIf(NameLast, ''), NullIf(NameSuffix, '')

    from #People)

    select

    IsNull(Prefix + ' ' + First + ' ' + Middle + ' ' + Last + ', ' + Suffix,

    IsNull(Prefix + ' ' + First + ' ' + Middle + ' ' + Last,

    IsNull(Prefix + ' ' + First + ' ' + Last + ', ' + Suffix,

    IsNull(Prefix + ' ' + First + ' ' + Last,

    IsNull(Prefix + ' ' + Last + ', ' + Suffix,

    Prefix + ' ' + Last)))))

    from Cleanup;

    It's still not as obvious what needs to be done to add a new combination in the middle of the list.

    - 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

  • yea. i'm still a little freaked out that the isnull returns the first params type and length no mater what. Don't know how i didnt run into this before. Run the below. Crazy,

    declare @colA varCHAR(3)

    declare @colb varCHAR(10)

    set @colA = 'abc'

    set @colb = '123456789'

    select 'when @colA is not null', ISNULL(@colA,@colB) AS using_isnull,

    COALESCE(@colA,@colB) AS using_coalesce

    set @colA = null

    set @colb = '123456789'

    select 'They should both be equal'

    , case when ISNULL(@colA,@colB) = @colb then 'They Equal' else 'They Not Equal using isnull' end

    , case when coalesce(@colA,@colB) = @colb then 'They Equal using coalesce' else 'They Not Equal' end

  • Trying to think back where this wouldf have burned me in the past.

    declare @colA CHAR(3)

    declare @colb CHAR(10)

    set @colA = null

    set @colb = '123456789'

    select 'when @colA is null', case when ISNULL(@colA,@colB) = @colB then 'Their equal' else 'Their not equal, ISNULL(@colA,@colB) <> @colB' end AS using_isnull,

    case when COALESCE(@colA,@colB) = @colB then 'Their equal, COALESCE(@colA,@colB) = @colB' else '' end AS using_coalesce

    this would be a scenerio i can forsee getting burnt.

  • One reason to use COALESCE rather than IsNull... COALESCE is in the ANSI standards, IsNull is a Microsoft extension to the standard.

  • The only time I would use isNull is where I have a very simple case, I would never try to do anything at all complicated with isNull (like nesting it). I guess this is probably because isNull is (a) not portable and (b) not pretty if you have to modify something that uses it in a complicated manner. I do use it in simple cases (for example where producing ad hoc diagnostic text to log) despite the non-portability, because it's 2 fewer characters to type than coalesce and I'm extremely lazy.

    Tom

Viewing 15 posts - 1 through 15 (of 41 total)

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