Hey guys,
Goodnight!

In this post I will talk about something that is very common to find when developing queries using Transact-SQL (T-SQL) in SQL Server, which are aggregation or grouping functions (Ex: SUM, MAX, MIN, AVG) on columns that have NULL values ​​in their records. When this happens, an alert is generated with this message:

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

Although it is just an alert, some applications may present errors by interpreting the Warning as an error and returning an exception. In this post I will show you how to avoid this message.

Using SET ANSI_NULLS OFF

Although it is a “workaround”, unfortunately it is the “solution” that I see developers using the most to avoid this type of warning, mainly because it is more practical, faster and better known than other solutions.

SQL Server - Warning Null value is eliminated by an aggregate or other SET operation 2
SQL Server - Warning Null value is eliminated by an aggregate or other SET operation 2

Although this is a very bad “solution”, there are much worse solutions, such as SET ANSI_WARNINGS OFF.

If you use this resource in any query you have ever developed, you NEED to read the post SQL Server – Why NOT use SET ANSI_WARNINGS OFF.

Using filters

The most correct solution is to apply filters to your query to return only valid records in the aggregation functions. This solution is not widely used because it needs to be done manually in all queries and not all developers want to do this work, although it is the recommended and most correct solution.

SQL Server - Resolvendo Solved Warning Null value is eliminated by an aggregate or other SET operation
SQL Server - Solving Solved Warning Null value is eliminated by an aggregate or other SET operation

While the solution using ISNULL is harmless and will always satisfy this need, be very careful when using the filter on WHERE as was done in example 2. Although it appears to be coherent, note that the value of the final result has been changed.

Using ISNULL ensures that the sum is done correctly and NULL values ​​are converted to zero without compromising the final result. On the other hand, it is less performant than using a filter in WHERE to remove cases that should not be added, but these filters must be used very well so as not to alter the result. So, use the best solution according to your needs.

That's it, folks!
I hope you liked the post.