Null value is eliminated by an aggregate or other SET operation

This warning comes usually while using aggregated function on column containing one or more NULL values.

To understand run following code :

Declare @R Int

Set @R = (Select SUM(N) From (Select NULL AS N UNION Select 1) aa)

---- OUTPUT
----- Warning: Null value is eliminated by an aggregate or other SET operation.
 
There are two solutions to get rid of this warning :
1. SET ANSI_WARNINGS OFF
Turn Off the warning using command.
 
2. ISNULL
Use ISNULL function on column.
e.g :
 
Declare @R Int

Set @R = (Select SUM(ISNULL(N,0)) From (Select NULL AS N UNION Select 1) aa)

--- OUTPUT
--- Command(s) completed successfully.

I like to use second method always , what you use ?
Note : Make sure to use the ISNULL function before using aggregate function otheriwe you will face the same warning again.

 

How to increase progarmmer productivity

http://www.blogya.in/how-to-increase-programmers-productivity

How to be smart programmer

http://www.blogya.in/smart-programmer