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.

SET NOCOUNT ONGODECLARE @ItemSale TABLE (ID INT, Qty INT, Price MONEY)INSERT INTO @ItemSale (ID, Qty, Price) SELECT 1, 25, 100INSERT INTO @ItemSale (ID, Qty, Price) SELECT 2, NULL, 200INSERT INTO @ItemSale (ID, Qty, Price) SELECT 3, 5, NULLGOSELECT SUM(Qty) 'Sum - 1' FROM @ItemSale WHERE ID IN (1,3)-- no warningGOSELECT SUM(Price) 'Sum - 2' FROM @ItemSale WHERE ID IN (1,3)-- Warning: Null value is eliminated by an aggregate or other SET operation.GOSELECT 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

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 OFFGO

Here is the output after adding the set option:

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.