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
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:
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)
40 Comments. Leave new
Nice explanation of core part.
Glad you liked it Karthik!
I always have it enabled since it is required for indexed views.
@IanYates82 – Perfect!
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.
@Thomas – You can do that as well but how many places you would use ISNULL?
Can we not use isnull( ) in aggregated functions ????
ok ….got my answer…by reviewing comments above…thanksss…..
When you add ISNULL on aggregate AVG or Count will change in result
@sivamuru – I don’t think so.. any example script? I am assuming that you are replacing NULLs with zero.
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.
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.
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?
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.
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
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?
Can you capture the event.. ? Iow; if this happens anywhere in your SP? can you exit it with an error?
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…
Very interesting.
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?
I don’t think that’s possible.
The default for SS 2014 seems to be SET ANSI_WARNINGS ON
I think you are correct,.
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?
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.
excellent explanation.
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.
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.