I have posted a question for July 2012 at Question of the month July 2012 - How does COALESCE(null,null+1,null) work? Did you know why the second query works without any error?
A NULL value can be of two types. Typed NULL and non-Typed NULL.
SELECT SUM(NULL)
will throw the error
Msg 8117, Level 16, State 1, Line 2
Operand data type NULL is invalid for sum operator.
But when you execute the following
declare @i int
set @i=null
select sum(@i)
You get the output as NULL. Eventhough SUM is applied over NULL value, the first one throwed error and second one ran successfully. It is becuase in first case NULL did not have any datatype so SUM couldn't assume whether the NULL is of Number datatypes (INT,FLOAT, DECIMAL,etc). But in the second example @i has NULL value but it is of INT datatype so SUM function worked correctly and returned the value NULL
You can also use implicit convertion
SELECT SUM(NULL+0)
will return NULL becuase adding 0 to it makes it to have datatype of INT. If you try to run this code
SELECT SUM(NULL+GETDATE())
it will throw an error
Msg 8117, Level 16, State 1, Line 1
Operand data type datetime is invalid for sum operator
It is because NULL value is of datatype DATETIME which can not be used on aggregate column
Also default datatype of NULL is INT. Refer
Default datatype of NULL for more information