SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is the impact of setting SET ANSI_WARNINGS OFF?


What is the impact of setting SET ANSI_WARNINGS OFF?

Author
Message
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8597 Visits: 2163
Comments posted to this topic are about the item What is the impact of setting SET ANSI_WARNINGS OFF?

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Yet Another DBA
Yet Another DBA
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3687 Visits: 1246
Cool, so no down side in turning ansi_warnins off then? In fact by your suggestion it would even help the lazy developer who will not check for a divide by zero or domain rules. So no performance loss either.


I suggest you check your references and think about the functionality that would not be available. I think that this simplistic article should come with a health warning.
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8597 Visits: 2163
Yet Another DBA (8/23/2016)
Cool, so no down side in turning ansi_warnins off then? In fact by your suggestion it would even help the lazy developer who will not check for a divide by zero or domain rules. So no performance loss either.


I suggest you check your references and think about the functionality that would not be available. I think that this simplistic article should come with a health warning.


No. It is not cool to turn ANSI_WARNINGS OFF. While it eliminates warnings, it does have a negative effect on data quality and will cause issues when the data is migrated to or integrated with other systems.

While I have highlighted the fact that keeping ANSI_WARNINGS OFF will generate bad data in the individual conclusions, here's the summary from the end of the article which re-iterates the point about bad data generation.

Setting ANSI_WARNINGS OFF can eliminate warnings being logged if the aggregations are being done as part of a nightly job.

However, it may cause confusion when validating the results of the aggregation. When divide by zero or string length mismatch issues are encountered, the database engine may successfully accept, process, generate and commit data of a particular transaction with possibly poor data quality (i.e. data may be incorrect or incomplete).

Having poor quality data in a system may create problems when such data is migrated over to or integrated with another system (maybe another on-premise or a cloud system) and the target system enforces strict data quality standards. It may also cause dissatisfaction among users if an organization incorrectly addresses them or their shipments do not reach them due to silent truncation of their data.


Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
nick.mcdermaid
nick.mcdermaid
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3216 Visits: 813
Enforcing data integrity is an important function. Encouraging lazy developers is not. The only time I've ever needed to turn warnings off was to stop NULL aggregate warning bubbling up in SSIS packages and causing false error messages. This only ever happened intermittently and could only be fixed by turning off ANSI warnings.
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8597 Visits: 2163
nick.mcdermaid (8/23/2016)
Enforcing data integrity is an important function. Encouraging lazy developers is not...


Exactly. That's why I was concerned when the development team asked me whether it would be okay to set ANSI_WARNINGS to OFF.

I knew that doing so generates bad data and impacts system behaviour (no errors when you expect it to error out, etc) - which is the key point of this article.

"My immediate reaction was to understand why the team was trying to set ANSI_WARNINGS to OFF because setting ANSI_WARNINGS to OFF can have an impact on the data quality and system behaviour. This article demonstrates how setting ANSI_WARNINGS of OFF impacts system behaviour."

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Kevin Gill
Kevin Gill
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2071 Visits: 356
I'm confused by the use of sp_configure and I can't find a clear source anywhere to explain it. If the "user options" is made up of a bitmap of options and position 8 is ansi_warnings, you execute this:

EXEC sp_configure 'user options', 8 ;
GO
RECONFIGURE ;


Does that not zero all the other options within user_options and set the whole bitmap to ...000001000?

If not and it just "additionally" turns ANSI_WARNINGS on presumably with an implicit bitwise "AND" within the proc, how do you turn just ANSI_WARNINGS (or any other single option) off again?

Thanks

Kev

-------------------------------
Oh no!
Nakul Vachhrajani
Nakul Vachhrajani
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8597 Visits: 2163
Whatever value we supply is taken by SQL Server as the value to be set.

For example, the following turns ANSI_WARNINGS (8), QUOTED_IDENTIFIER (256) and XACT_ABORT (16384) to ON. The value 16648 is just a binary OR of the 3 values:


--SELECT (8|256|16384)
--Result: 16648

EXEC sys.sp_configure N'user options', N'16648'
GO
RECONFIGURE WITH OVERRIDE
GO



In order to set QUOTED_IDENTIFIER and XACT_ABORT back to OFF and only keep ANSI_WARNINGS as ON, one can use:


EXEC sys.sp_configure N'user options', N'8'
GO
RECONFIGURE WITH OVERRIDE
GO



If you have any further questions, please do let me know.

Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Be courteous. Drive responsibly.

Follow me on
Twitter: @sqltwins
Google Plus: +Nakul
Kevin Gill
Kevin Gill
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2071 Visits: 356
Hi Nakul, thanks for the reply.

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.

Thanks

Kevin

-------------------------------
Oh no!
Alan Burstein
Alan Burstein
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53662 Visits: 8955
Great article. Well done. Scary.

-- Alan Burstein


Helpful links:

Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw

Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K

I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001

Iwas Bornready
Iwas Bornready
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64468 Visits: 886
We turned them off when we migrated data into a new database. But never under normal operating conditions.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search