What happens faster: SUM over NULL or more than 0?

Question

I have a query like this:

select sum(case when col1=@arg1 then value else null end) from t 

Is there a performance difference in using 0 instead of NULL ? Like this:

 select sum(case when col1=@arg1 then value else 0 end) from t 

Answer 1

In the test below, I found that NULL bit faster.

  SET STATISTICS TIME ON; DECLARE @i int = null; /*Or set to zero*/ WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ) -- 1*10^1 or 10 rows , E2(N) AS (SELECT 1 FROM E1 a, E1 b) -- 1*10^2 or 100 rows , E4(N) AS (SELECT 1 FROM E2 a, E2 b) -- 1*10^4 or 10,000 rows , E8(N) AS (SELECT 1 FROM E4 a, E4 b) -- 1*10^8 or 100,000,000 rows SELECT SUM(@i) FROM E8 OPTION (MAXDOP 1) 

At an average of 608 ms to summarize 100,000,000 values. (i.e. 6 nanoseconds per aggregation).

NULL people spend more time on

 sqllang.dll!CESRunTimeErrorSink::SetAggFnSkippedNull 

Presumably set a flag that will result in a message

Warning: Null is excluded by an aggregate or other SET operation.

But overall it seemed faster (elapsed time in ms lower).

 +---------+--------+------++-------+ | | NULL | 0 || Diff | +---------+--------+------++-------+ | Trial 1 | 7027 | 7592 || 565 | | Trial 2 | 6981 | 7743 || 762 | | Trial 3 | 7451 | 8015 || 564 | | Trial 4 | 6997 | 7591 || 594 | | Trial 5 | 7018 | 7574 || 556 | +---------+--------+------++-------+ | Avg | 7094.8 | 7703 || 608.2 | +---------+--------+------++-------+ 

Of course, in this case (where all the input is NULL ) they return different results, and you need ISNULL(SUM(@i),0) if you want to consider these two interchangeably.

Answer 2

 DECLARE @type CHAR(2) = 'U' -- [Expr1042] = Scalar Operator(CASE WHEN [Expr1048]=(0) THEN NULL ELSE [Expr1049] END) SELECT SUM(CASE WHEN [type] = @type THEN 1 END) FROM sys.objects -- [Expr1042] = Scalar Operator(CASE WHEN [Expr1048]=(0) THEN NULL ELSE [Expr1049] END) SELECT SUM(CASE WHEN [type] = @type THEN 1 ELSE NULL END) FROM sys.objects -- [Expr1042] = Scalar Operator(CASE WHEN [Expr1048]=(0) THEN NULL ELSE [Expr1049] END) SELECT SUM(CASE WHEN [type] = @type THEN 1 ELSE 0 END) FROM sys.objects 

results:

 Table 'sysschobjs'. Scan count 1, logical reads 1556, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 7 ms. Table 'sysschobjs'. Scan count 1, logical reads 1556, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 6 ms. Table 'sysschobjs'. Scan count 1, logical reads 1556, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 7 ms. 

so ... the answer is similar (if we only talk about performance)