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 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)

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

38 Comments. Leave new

  • Nice explanation of core part.

    Reply
  • Ian Yates (@IanYates82)
    February 15, 2015 9:14 am

    I always have it enabled since it is required for indexed views.

    Reply
  • would it not be better to use IsNull() inside the aggregate function instead of turning the warnings off? As far I know IsNull should have only very small impact on CPU costs.

    Reply
  • Can we not use isnull( ) in aggregated functions ????

    Reply
  • sivamurugan (@sivamuru)
    May 28, 2015 2:07 am

    When you add ISNULL on aggregate AVG or Count will change in result

    Reply
    • @sivamuru – I don’t think so.. any example script? I am assuming that you are replacing NULLs with zero.

      Reply
      • It will change the answer:

        select avg(num), avg(isnull(num,0))
        from (select num = 1.0
        union all select 4
        union all select 5
        union all select null
        union all select null) N

        —————-
        avg(num) avg(isnull(num,0))
        3.333333 2.500000

      • TAKE CARE!

        I completely agree with sivamurugan and Brad: while it’s true ISNULL will not affect the results of some aggregate functions such as SUM, MIN, MAX, it is equally true that other aggregate functions such as COUNT and AVG will indeed be affected by ISNULL.

        Follow this:

        (pseudo sql)

        SUM of 1 + 0 = 1
        SUM of 1 + null = 1
        SUM of 1 + ISNULL(null,0) = 1

        COUNT of 1 and 2 = 2
        COUNT of 1 and null = 1
        COUNT of 1 and ISNULL(null,0) = 2

        (real tsql)

        SELECT sum(a) FROM ( VALUES (1), ( null) ) AS MyTable(a);
        SELECT sum(a) FROM ( VALUES (1), (isnull(null,0)) ) AS MyTable(a);
        — results: 1 and 1 <— that's ok

        SELECT count(a) FROM ( VALUES (1), ( null) ) AS MyTable(a);
        SELECT count(a) FROM ( VALUES (1), (isnull(null,0)) ) AS MyTable(a);
        — results: 1 and 2 <— oh-oh!!!

        Just imagine ISNULL(null,0) pulling down the AVG towards zero.

        Nick

      • lol, all these “use ISNULL”… Why not just omit the nulls from aggregate they affect?

        select avg(num), avg(isnull(num,0))
        from (select num = 1.0
        union all select 4
        union all select 5
        union all select null
        union all select null) N
        where n.num is not null

  • I have a stored proc using tables in the same server and a linked server, so I believe that is the cause of this error message when turned on: Msg 7405, Level 16, State 1, Procedure proc_ProcName, Line 55
    Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

    Reply
  • I turned the warning back on because an insert statement I ran later did not warn me that it truncated a field. For me, this is necessary information.

    Reply
  • I have a question related to SUM of NULLS. When I do select SUM(flag) from a table that has NULL values for Flag, I end up getting a correct SUM for all the flag values.

    But, when i do an individual SUM(1+3+NULL), the result i see is a NULL. I understand that when there is NULL included in the list, the SUM needs to be NULL.

    What about the discrepancy above when I am performing the same operation from column of a table? How does SQL Server engine neg up ignoring it in the former scenario but not in latter one?

    Reply
  • Hi There,
    I got an error when i try to execute a proc.
    Error : FAILURE: Exec SQL|Warning: Null value is eliminated by an aggregate or other SET operation.
    the SP got failed. can any have the solution Please.

    Thanks In Advance.

    Reply
  • Msg 512, Level 16, State 1, Line 1
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.
    Warning: Null value is eliminated by an aggregate or other SET operation.

    i am facing this error while i am using one database stored procedure column insert into the another database table

    Reply
  • Jeremiah Daigle
    July 6, 2016 6:55 pm

    Can you explain why you mention this “Tuning it ON can be dangerous as well. Stay tuned!” From my understanding of it, the default setting of OFF would be more dangerous in many different ways, than to have it turned ON?

    Reply
  • Marcel Drost
    July 18, 2016 7:03 pm

    Can you capture the event.. ? Iow; if this happens anywhere in your SP? can you exit it with an error?

    Reply
  • I ran into a situation where this affected performance.

    My database had ANSI_WARNINGS set to ON by default. I added “SET ANSI_WARNINGS OFF” to my stored procedure. This added about 200ms to the execution time. I removed this statement and the execution time dropped back to normal. Not sure why…

    Reply
  • Carolina Lindoso
    February 9, 2017 9:34 pm

    What if I want to throw this warning as an error so I can analyse the data from this particular query in an SP with multiple nested queries?

    Reply
  • The default for SS 2014 seems to be SET ANSI_WARNINGS ON

    Reply
  • Mahesh Reddy Palem
    May 2, 2018 3:34 am

    I have a situation where I need to enable this in my stored procedure since it is joining with a linked server table.
    How about in such scenario?

    Reply
  • Hi Pinal, When I mentioned “SET ANSI_WARNINGS OFF” it suppress warning messages but creates new problem.
    The problem is it suppress the “String or binary data would be truncated ” error.
    Please provide a solution , Don’t suppress the error message, It suppress only warnings.

    Reply
  • excellent explanation.

    Reply
  • Or just omit the nulls from the select in the where clause. No need for isnull, if the [column] is nullable then include where [column] is not null – when using a count or average.

    There is also times when the isnull([column],0) still doesn’t work, say the table only has null records as an example.

    Null: not zero, not nothing, not something, not blank, it’s literally the equivalent of “space” to a quantum physicist.

    Reply
  • Annoyingly, this message is triggered when doing a COUNT of a field from an OUTER JOIN, which is a legitimate operation and specifically used when you might have a count of 0.

    Reply

Leave a Reply