Is it safe to use SUM() without ISNULL()

Question

I am trying to hence the performance of an SP. I have a doubt in my mind about SUM and ISNULL. When I sum up a column, should I use ISNULL? Is it SAFE to use SUM() without ISNULL. My example is below

SUM(ISNULL(COL1,0))

Since ISNULL costs a lot, I intend to use SUM without ISNULL like below

SUM(COL1)

I did some small tests and I couldnt see results

Answer 1

Yes its safe . You can use Sum without handling NULL Value. You can also check that.

You can use like that also.

ISNULL(SUM(COL1),0).

Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

For Reference : https://msdn.microsoft.com/en-IN/library/ms187810.aspx

Answer 2

Its not mandatory to use NULL and COALESCE while doing SUM because SUM always ignore null values.

Refer the link : https://msdn.microsoft.com/en-IN/library/ms187810.aspx for more info.

Answer 3

Updated

If you have [1, 2, NULL, 5] in 4 columns, it will give the output as 8.

However, it is not safe to use SUM() without checking for NULLS in many cases.

You can receive null when it has no matching content for a given clause. And if you are using this SUMMED value in another function, that maybe a point of concern.

More details here: https://msdn.microsoft.com/en-GB/library/ms187810.aspx

Please also look at COALESCE method https://msdn.microsoft.com/en-IN/library/ms190349.aspx

PS: Also check out this post - My Select SUM query returns null. It should return 0

Here are 3 images that shows without checking for NULL it returns NULL and not 0.

SUM with ISNULL CHECK

SUM without ISNULL CHECK

SUM with COALESCE

Answer 4

Its better to use COALESCE method before the SUM aggregation.