What is the impact of setting SET ANSI_WARNINGS OFF?

Question

What is the impact of setting SET ANSI_WARNINGS OFF?

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

roger.plowman SSChampion Points: 10265 More actions · Answer 1

sknox (8/23/2016)
roger.plowman (8/23/2016)
ANSI_WARNINGS are basically concerned with Null values (ignoring the divide by 0 issue which is really a seperate one). I believe the better solution is never allowing Nulls in data columns in the first place.
Of course that requires some kind of flag values for "unknown value", "n/a" and so on, but to my mind this is a much better solution that the multi-value nature of Null anyway.
Null may be important from a relational model standpoint but has some serious downsides in implementation.
1. NULL IS the flag for "unknown value".
2. NULL is NOT multi-value. It is no value.
3. In the long run, the workarounds needed to maintain non-NULL values as flags for no value are much more cumbersome than simply taking the time to understand NULL.

Conceptually, null means "no value" BUT it doesn't tell you why there's no value. Was it never put in? Does it not apply to this particular record? Was the data lost so it can't ever be known (an asset was purchased years ago but the receipt was lost, for example).

Null doesn't supply *any* information, and sometimes having a nuanced "null" (flag value) is important. In our company it's usually extremely important in the majority of cases.

Besides that, null creates tri-value logic in a world that mainly deals with bi-value logic, and developers often neglect the third case. Better to have flag values in the domain than introduce tri-value logic.

Of course nulls are handy when you really will have an empty field most of the time (such as a comment field that is only rarely filled out).

Nulls are one of those debate topics that never stop. 🙂

sknox SSChampion Points: 12388 More actions · Answer 2

roger.plowman (8/23/2016)
sknox (8/23/2016)
roger.plowman (8/23/2016)
ANSI_WARNINGS are basically concerned with Null values (ignoring the divide by 0 issue which is really a seperate one). I believe the better solution is never allowing Nulls in data columns in the first place.
Of course that requires some kind of flag values for "unknown value", "n/a" and so on, but to my mind this is a much better solution that the multi-value nature of Null anyway.
Null may be important from a relational model standpoint but has some serious downsides in implementation.
1. NULL IS the flag for "unknown value".
2. NULL is NOT multi-value. It is no value.
3. In the long run, the workarounds needed to maintain non-NULL values as flags for no value are much more cumbersome than simply taking the time to understand NULL.
Conceptually, null means "no value" BUT it doesn't tell you why there's no value. Was it never put in? Does it not apply to this particular record? Was the data lost so it can't ever be known (an asset was purchased years ago but the receipt was lost, for example).
Null doesn't supply *any* information, and sometimes having a nuanced "null" (flag value) is important. In our company it's usually extremely important in the majority of cases.
Besides that, null creates tri-value logic in a world that mainly deals with bi-value logic, and developers often neglect the third case. Better to have flag values in the domain than introduce tri-value logic.
Of course nulls are handy when you really will have an empty field most of the time (such as a comment field that is only rarely filled out).
Nulls are one of those debate topics that never stop. 🙂

"Null doesn't supply *any* information" -- this is patently false. NULL supplies the information that the value is unknown, and does so in a manner which does not require any additional information. Your "flag values" require documentation of the flag values, what they mean, and how to work around them.

If you know why there's no value and need to store that information, use a separate column rather than corrupting your original column with invalid values which might collide with valid values later on or which force a change to a non-optimal data type. But be prepared to make that additional column NULLable for those cases where you're missing information and don't know why... ; )

Tri-value logic is actually the reality; we simply deceive ourselves into thinking that we always know when we use bi-value logic.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 3

If the goal is simply to avoid the warnings for aggregate operations, that can be handled via ISNULL rather than SET ANSI_WARNINGS OFF:

SELECT SUM(tmp.val) AS [SomeSUM]

FROM (VALUES (1), (5), (NULL)) tmp(val);

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

SELECT SUM(ISNULL(tmp.val, 0)) AS [SomeSUM]

FROM (VALUES (1), (5), (NULL)) tmp(val);

-- no warning

Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

ebouras SSC Enthusiast Points: 190 More actions · Answer 4

Solomon Rutzky (8/23/2016)
If the goal is simply to avoid the warnings for aggregate operations, that can be handled via ISNULL rather than SET ANSI_WARNINGS OFF

Yes - I forgot to mention that in my post. I would venture a guess that the majority of cases where an unsavvy dev sets warnings off could be better solved by choosing this option.

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 5

Kevin Gill (8/23/2016)
You've confirmed my understanding of sp_configure which I then think means it would be worth a clarification in your article in the section headed "How to configure ANSI_WARNINGS for all sessions on the server?".
The advice given in there is to fire sp_configure with the "user options" string and the value of 8 in order to turn ANSI_WARNINGS on, and people may just blindly follow this advice not realising that they have in fact turned all the other "user options" off with the same command. There is already misunderstanding of this fact on the web at large - one forum post I found suggested that if someone had accidentally set "user options" to 2, they should just set it back to the default of zero to undo that, with no discussion of what else might have been impacted.

I agree with Kevin 100% that this article should be updated to clarify the usage of sp_configure with regards to the user options bitmasked value.

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

N_Muller SSCarpal Tunnel Points: 4037 More actions · Answer 6

If you have temp tables (#tables) with indexes in a stored procedure where ansi warnings are turned off for aggregation of null values, the procedure fails. Regardless of good or bad coding practices, if I purposely decide to turn off ansi warnings, SQL Server shouldn't error out on indexes. This has caused us tremendous pain and performance degradation by having to add isnull(variable,0) into sums.

ebouras SSC Enthusiast Points: 190 More actions · Answer 7

N_Muller (8/23/2016)
If you have temp tables (#tables) with indexes in a stored procedure where ansi warnings are turned off for aggregation of null values, the procedure fails. Regardless of good or bad coding practices, if I purposely decide to turn off ansi warnings, SQL Server shouldn't error out on indexes. This has caused us tremendous pain and performance degradation by having to add isnull(variable,0) into sums.

I wasn't aware of that... would it be a burden to ask for a short SQL example to illustrate the conditions for this? It's unclear by your description exactly what triggers the error.

chuck.forbes SSCarpal Tunnel Points: 4294 More actions · Answer 8

This would have been helpful to know for a preliminary data load we were doing the other day. We simply wanted a quick load of 250,000+ records to see what was in the incoming file. We were using SSIS and kept hitting the 'truncation' error, for everything exceeding 50 chars (the default string size set in SSIS), and modifying the column size. 50 chars per column would have been perfectly adequate for initial review. Dernit, this would have saved several hours of load, hit an error, resize, aaaand repeat.

On the bright side, if we have to start loading this data, we've already done the research on column sizings. Glass half-full, right? Right?

--=cf

rstone SSCertifiable Points: 6209 More actions · Answer 9

If you don't like the warning, it's often easy to update the query to explicitly replace the default behavior. To be honest, I have used this setting when I did not want to take the effort to "fix" the query. However, for code in procedures, I prefer to handle the logic explicitly rather than accept defaults - thus removing the warning without changing the setting. For an aggregate with a NULL, a WHERE clause to eliminate NULLs would have the same result without a warning. If the design is such that a NULL is really a value, then an ISNULL would work. (Forcing a NULL for a value is not ideal, but I could contrive a reason to do this.) I'm never fond of ignoring a divide by zero. A CASE test on a zero divisor might be called for. (If looking at disk latency, use a CASE to return NULL if there or no IOs; otherwise, return the divided result - same result without a warning.) If truncation is okay and the result can be too large to fit, then add a LEFT or something to insure it fits. If truncation is not okay, then be sure to raise the error - a warning is not enough. (A truncation without an error can be a PITA.)

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

rstone SSCertifiable Points: 6209 More actions · Answer 10

ebouras (8/23/2016)
N_Muller (8/23/2016)
If you have temp tables (#tables) with indexes in a stored procedure where ansi warnings are turned off for aggregation of null values, the procedure fails. Regardless of good or bad coding practices, if I purposely decide to turn off ansi warnings, SQL Server shouldn't error out on indexes. This has caused us tremendous pain and performance degradation by having to add isnull(variable,0) into sums.
I wasn't aware of that... would it be a burden to ask for a short SQL example to illustrate the conditions for this? It's unclear by your description exactly what triggers the error.

Perhaps there was a different warning about potentially exceeding the maximum key length of the index? There is character limit to the total key size; however, an index can be created on a variable length field - or combination of fields - that can potentially exceed the maximum. Future inserts or updates that exceed the limit fail. Those that do not exceed the limit do not fail. You might need a surrogate key if loading and processing large columns.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

roger.plowman SSChampion Points: 10265 More actions · Answer 11

sknox (8/23/2016)
roger.plowman (8/23/2016)
sknox (8/23/2016)
roger.plowman (8/23/2016)
ANSI_WARNINGS are basically concerned with Null values (ignoring the divide by 0 issue which is really a seperate one). I believe the better solution is never allowing Nulls in data columns in the first place.
Of course that requires some kind of flag values for "unknown value", "n/a" and so on, but to my mind this is a much better solution that the multi-value nature of Null anyway.
Null may be important from a relational model standpoint but has some serious downsides in implementation.
1. NULL IS the flag for "unknown value".
2. NULL is NOT multi-value. It is no value.
3. In the long run, the workarounds needed to maintain non-NULL values as flags for no value are much more cumbersome than simply taking the time to understand NULL.
Conceptually, null means "no value" BUT it doesn't tell you why there's no value. Was it never put in? Does it not apply to this particular record? Was the data lost so it can't ever be known (an asset was purchased years ago but the receipt was lost, for example).
Null doesn't supply *any* information, and sometimes having a nuanced "null" (flag value) is important. In our company it's usually extremely important in the majority of cases.
Besides that, null creates tri-value logic in a world that mainly deals with bi-value logic, and developers often neglect the third case. Better to have flag values in the domain than introduce tri-value logic.
Of course nulls are handy when you really will have an empty field most of the time (such as a comment field that is only rarely filled out).
Nulls are one of those debate topics that never stop. 🙂
"Null doesn't supply *any* information" -- this is patently false. NULL supplies the information that the value is unknown, and does so in a manner which does not require any additional information. Your "flag values" require documentation of the flag values, what they mean, and how to work around them.
If you know why there's no value and need to store that information, use a separate column rather than corrupting your original column with invalid values which might collide with valid values later on or which force a change to a non-optimal data type. But be prepared to make that additional column NULLable for those cases where you're missing information and don't know why... ; )
Tri-value logic is actually the reality; we simply deceive ourselves into thinking that we always know when we use bi-value logic.

Thus the debate. When you have hundreds of columns that require this kind of tracking a seperate column is not viable. Some domains *won't* allow flag values it is true. Most will, and in a way that doesn't cause collisions.

Trivalue or flag, pick your poison. Neither is perfect, but I find flag values far less cumbersome and much more informative.

As for null not supplying any information, that is actually a misconception. Null supplies the information the value is missing--it just doesn't tell you why.

rstone SSCertifiable Points: 6209 More actions · Answer 12

Please, not another null or non-null debate. SQL Server has a clear implementation of NULL. The flat-nullers should just get over it. Far more important are domain definitions, business analysis, and coders who know when not to use udfs. There's far more evil SQL not related to NULLs that I really don't see the point. It's like worrying about getting wet when you are trapped under water about to die. Sorry, that was probably a bit too strong - just noticed a scalar udf being used in a computed column to do a sum of related records. A very clever way to do something very bad.

RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉

N_Muller SSCarpal Tunnel Points: 4037 More actions · Answer 13

SQL Server doesn't allow to create filtered indexes or indexes on computed columns in temp tables with ansi warnings turned off. Here's a simple example:

set ansi_warnings off

create table #temp ( id int )

create index ix_#temp_id on #temp ( id ) where ( id = 0 )

It boggles my mind why SQL Server has this restriction, especially because I can wrap the create table and index around setting of ansi warnings on and off without errors.

set ansi_warnings on

create table #temp ( id int )

create index ix_#temp_id on #temp ( id ) where ( id = 0 )

set ansi_warnings off

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 14

N_Muller (8/23/2016)
SQL Server doesn't allow to create filtered indexes or indexes on computed columns in temp tables with ansi warnings turned off. Here's a simple example:

More specifically, SQL Server does not allow indexes in a few scenarios when ANSI_WARNINGS is OFF, though tempdb has nothing to do with it:

SET ANSI_WARNINGS OFF;

CREATE TABLE dbo.temp1 ( [id] INT );

CREATE INDEX ix_temp1_id ON dbo.temp1 ( [id] ) WHERE ( [id] = 0 );

and:

SET ANSI_WARNINGS OFF;

CREATE TABLE dbo.temp2 ([Col1] INT, [Col2] AS ([Col1] * 2));

CREATE INDEX [IX_temp2_Col2] ON dbo.temp2 ([Col2]);

Both get:

Msg 1934, Level 16, State 1, Line 4
CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

More info on MSDN at: CREATE INDEX

Take care, Solomon...

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR

Yet Another DBA SSCarpal Tunnel Points: 4299 More actions · Answer 15

Solomon Rutzky (8/23/2016)
N_Muller (8/23/2016)
SQL Server doesn't allow to create filtered indexes or indexes on computed columns in temp tables with ansi warnings turned off. Here's a simple example:
More specifically, SQL Server does not allow indexes in a few scenarios when ANSI_WARNINGS is OFF, though tempdb has nothing to do with it:
SET ANSI_WARNINGS OFF;
CREATE TABLE dbo.temp1 ( [id] INT );
CREATE INDEX ix_temp1_id ON dbo.temp1 ( [id] ) WHERE ( [id] = 0 );
and:
SET ANSI_WARNINGS OFF;
CREATE TABLE dbo.temp2 ([Col1] INT, [Col2] AS ([Col1] * 2));
CREATE INDEX [IX_temp2_Col2] ON dbo.temp2 ([Col2]);
Both get:
Msg 1934, Level 16, State 1, Line 4
CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_WARNINGS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
More info on MSDN at: CREATE INDEX
Take care, Solomon...

Finally the health warnings are starting that the OP should have stated in the first place!

( Thank you N_Muller & Solomon )