November 28, 2013 at 12:08 am
Please find the following example.
I want to know the difference between sum(ISNULL(i,0)), ISNULL(sum(i),0)
Both will return the same value.
Which one will be correct and also tell me about the query cost.
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
Thanks in advance 🙂
November 28, 2013 at 12:21 am
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
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
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 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply