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
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.
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.
Interesting… I’ve mostly taken the ANSI_WARNINGS setting for granted.
While the databases may default to having this setting OFF, SSMS defaults connections to having ther setting ON. Having it ON seems like a good thing to me because, as it says in Books Online…
“ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.”
It also states that, luckily (or a lot of what we do would fail)…
“Clients, such as the SQL Server Native Client ODBC driver, the SQL Server Native Client OLE DB Provider for SQL Server, and the Microsoft JDBC Driver for SQL Server automatically set ANSI_WARNINGS to ON with a connection flag.”
So you ask “What would be interesting is if anyone out there has turned this setting ON deliberately. Is there a scenario where you found it useful?”. Considering that it defaults to on in almost everything that accesses a database and has some seriously bad things happen when you turn it off, it would be more interesting to ask if anyone has explicitly turned it off in all those other places.
As for me, I’m very thankful that it is set to ON in all those other places especially since (but not limited to) we have a shedload of computed columns in our databases. ;-)
I Would like to raise an error when this occurs. In my case, I would need to fix the missing data. Is this possible?
I’ve always wanted to better understand if there was a way to know which aggregated column had the NULL. Is there is a way to get that from the ANSI WARNING somehow? I know I can remove all of the aggregation functions one-by-one but I was wondering if there is a better way.
on counting, NULL values are not counted, thus it could be very handy if you let the field as is
COUNT(DISTINCT CASE WHEN expr THEN field ELSE NULL END)
But this article fails to take into account when a query involves a linked server. In that case you get an error:
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 have not found any solution to this. I do need to ignore ansi_warnings, especially regarding “string or binary data would be truncated” in insert statements using a select [column_list] for the values source. (Why does Microsoft always think they know better and force a rule like this on developers who in fact know what they are doing and have a real-world use-case reason for doing it!