Post reply

want to know Difference between isnull(sum(xx),0), sum(isnull(xx,0))

  • November 28, 2013 at 12:21 am

    #1669619

    The SUM function ignores NULL values.

    But, a regular sum also ignores 0 values (x + 0 = x).

    So both expressions are equal. sum(ISNULL(i,0)) has a few operations more than the other one, as ISNULL is called for every row.

    But with the CPUs nowadays, this difference is negligible. Besides, the query optimizer might maybe change the order of the functions to optimize the query.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 28, 2013 at 2:52 am

    #1669642

    One extra point, although the expressions will usually return the same value, there is a difference if there are no rows in the table.

    The first expression would return NULL but the second expression would return 0.

    _______________________________________________________________

    Website : www.sqlmatters.com

  • December 6, 2013 at 1:27 am

    #1671398

    Hi All,

    I find the difference.

    declare @t as table (i numeric(5,0),j int)

    insert into @t

    values (null,null),(NULL,1),(NULL,NULL),(NULL,3)

    select * from @t

    select sum(i),sum(j) from @t

    select sum(ISNULL(i,0)),sum(ISNULL(j,0)) from @t

    select ISNULL(sum(i),0),ISNULL(sum(j),0) from @t

    If table has no records the following query will return NULL

    select sum(ISNULL(i,0)),sum(ISNULL(j,0)) from @t

    the following query will return 0

    select ISNULL(sum(i),0),ISNULL(sum(j),0) from @t

    Thanks to all.

Viewing 3 posts - 1 through 4 (of 4 total)

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