Difference between ISNULL(SUM(x),0) OR SUM(ISNULL(x,0) in SQL server

Question

Which one of the following is correct?

        SUM(ISNULL(Sales,0)) AS Sales,
        ISNULL(SUM(Sales),0) AS Sales,

Or are they both correct?

Answer 1

They both return the same except if you are running a query on an empty result set.

WITH Sales(Sales) AS
(
SELECT 1
)
SELECT
        SUM(ISNULL(Sales,0)) AS Sales,
        ISNULL(SUM(Sales),0) AS Sales
FROM     Sales    
WHERE 1=0

Returns

Sales       Sales
----------- -----------
NULL        0

The SUM(ISNULL(Sales,0)) version would avoid the ANSI WARNINGS about aggregating NULL.

One other subtle difference is that the datatype of the result column of ISNULL(SUM(Sales),0) is not regarded as nullable.

Answer 2

The first one says

SUM up every Sales field, and if the field is NULL, treat it as zero.

The second says Sum up the sales field, and if the total is NULL, report a zero instead...

However, the SUM() command skips NULL (although you'll be warned about it), so the first one will cause you not to get the error message

Answer 3

Try this:

DECLARE @table TABLE
(
  id INT IDENTITY,
  Alborz INT
)
INSERT  INTO @table
        SELECT  1
        UNION ALL
        SELECT  1
        UNION ALL
        SELECT  1
        UNION ALL
        SELECT  1
        UNION ALL
        SELECT  1
        UNION ALL
        SELECT  NULL

SELECT  ISNULL(SUM(Alborz), 0)
FROM    @table
SELECT  SUM(ISNULL(Alborz, 0))
FROM    @table

DELETE  FROM @table

SELECT  ISNULL(SUM(Alborz), 0)
FROM    @table
SELECT  SUM(ISNULL(Alborz, 0))
FROM    @table

You'll get 21, 21, 0, and NULL. If you don't want to handle nulls, then ISNULL(SUM(X),0) is the way to go.