SQL SERVER – Warning: Null value is Eliminated by an Aggregate or Other SET Operation

Working with NULL’s are always a challenge – not many developers understand how NULL works sometimes. In a related note, long back I wrote below blog in which I explained two set options Quoted Identifier and ANSI NULL.

SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

In future blogs, I would try to explain another one called ANSI_WARNINGS. Let’s look at the various warnings which can be suppressed when working with NULL.

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

Let us run below script in SQL Server Management Studio to see the effect.

Solarwinds

SET NOCOUNT ON
GO
DECLARE @ItemSale TABLE (ID INT, Qty INT, Price MONEY)
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 1, 25, 100
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 2, NULL, 200
INSERT INTO @ItemSale (ID, Qty, Price) SELECT 3, 5, NULL
GO
SELECT SUM(Qty) 'Sum - 1' FROM @ItemSale WHERE ID IN (1,3)
-- no warning
GO
SELECT SUM(Price) 'Sum - 2' FROM @ItemSale WHERE ID IN (1,3)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
GO
SELECT AVG(Qty) 'Avg' FROM @ItemSale WHERE ID IN (1,2)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
GO

Here is the output

SQL SERVER - Warning: Null value is Eliminated by an Aggregate or Other SET Operation ansi-warning-01

As the error says, NULLs are being ignored because we are using aggregate function (SUM, AVG). To avoid the warning we can use “set ansi_warnings off” before the script. Here is the modified script.

SET ANSI_WARNINGS OFF
GO

Here is the output after adding the set option:

SQL SERVER - Warning: Null value is Eliminated by an Aggregate or Other SET Operation ansi-warning-02

First of all we should remember that default value of the setting is OFF, which is actually good. In subsequent blogs, we would cover an error which can be suppressed using same set option. Tuning it ON can be dangerous as well. Stay tuned!

What would be interesting is if anyone out there has turned this setting ON deliberately. Is there a scenario where you found it useful? Let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – The Basics of the Execute Package Task – Notes from the Field #067
Next Post
SQL SERVER – Msg 8152, Level 16, State 14 – String or Binary Data Would be Truncated

Related Posts

35 Comments. Leave new

  • The logical example case for when you might think you want to ignore the warning: Let’s say you have a list of 100 companies in the database. Some of them may have insurance policies. Those that have insurance have a “deductible” value, those that don’t have insurance have a NULL deductible value. This is important because a deductible of “0” is NOT at all the same thing as not having insurance with “no” deductible since there is not insurance. Some may HAVE insurance with a 0 deductible. You want to know the average deductible value for those that DO have insurance. This is easy by just doing AVG(deductible) and it does exactly what you want, counting only those that DO have insurance — but it emits a pesky warning that in this case is not actually a problem. You don’t want to count those for the denominator that don’t have insurance, so using a coalesce is inappropriate and not needed. I will admit I don’t know as I write what the aggregate function would do if there are all nulls, but I think it would probably return null as the result without an error — that would be fine and is informative. IMO It could be nice to “dissect” this warning out of the system for a brief time while leaving all other warnings intact, but apparently you can’t. On the other hand, I understand the warning since people may not know that’s how the aggregate function works. I would say for most cases the solution may be to use a WHERE that explicitly eliminates the NULL values, for example: “WHERE deductible IS NOT NULL”. This way you are telling the system you “know” what you want and it won’t warn you, because you eliminated the null values yourself — the aggregate function never sees them and there is no need to warn you. The time when this may not work is when you have multiple columns where you’d like to get an aggregate value from them but there will be mixed nulls on a per row basis. You can’t eliminate the row and you’d like to have the aggregate function handle that, simply not counting the null entries. In this case, maybe, you could be forced to consider SET ANSI_WARNINGS OFF, for only a brief time. It just seems like a poor option because you can’t selectively suppress the single warning, so I’d want to look harder for another solution if possible. It’s too bad we can’t have some kind of a query hint for that.

    Reply
  • robbie mcgregor
    December 18, 2019 7:00 am

    Hi Dave! We met last year. Your last sentence regarding turning it on deliberately – although not explicitly turning it on, you can’t set ANSI_WARNINGS to OFF if you are using heterogeneous queries.

    Reply

Leave a Reply

Menu