Hello people,
Good night!
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 aggregate or grouping functions (Ex: SUM, MAX, MIN, AVG) in columns. which 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 a warning, some applications may have errors interpreting Warning as an error and returning an exception. In this post I will show how to avoid this message.
Using SET ANSI_NULLS OFF
Although it is a "gambiarra", unfortunately it is the "solution" that I see most developers using to avoid this type of warning, mainly because it is more practical, fast and known than the other solutions.
Although this is a very bad “solution”, there are far worse solutions, such as SET ANSI_WARNINGS OFF.
If you use this feature on any query you have ever developed, you MUST 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 aggregate functions. This solution is not widely used because it needs to be done manually in all queries and not all developers want to have this work, although it is the recommended and most correct solution.
While the solution using ISNULL is harmless and will always satisfy that need, be very careful when using the filter on WHERE as was done in the 2 example. Although it seems to be coherent, note that the value of the final result has changed.
Using ISNULL ensures that the sum is done correctly and NULL values are converted to zero without compromising the end result. By contrast, it is less performative than using a filter in WHERE to remove cases that should not be summed, but these filters should be very well used to not alter the result. So use the best solution as you need it.
That's it folks!
I hope you enjoyed the post.
