What is the impact of setting SET ANSI_WARNINGS OFF?

  • Nakul Vachhrajani

    SSChampion

    Points: 10151

    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

  • Yet Another DBA

    SSCarpal Tunnel

    Points: 4299

    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

    SSChampion

    Points: 10151

    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

  • nick.mcdermaid

    Hall of Fame

    Points: 3779

    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

    SSChampion

    Points: 10151

    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

  • Kevin Gill

    SSCrazy

    Points: 2240

    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

    SSChampion

    Points: 10151

    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

  • Kevin Gill

    SSCrazy

    Points: 2240

    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

    SSC Guru

    Points: 61026

    Great article. Well done. Scary.

    -- Alan Burstein


    Helpful links:Best practices for getting help on SQLServerCentral -- Jeff ModenHow to Post Performance Problems -- Gail ShawNasty fast set-based string manipulation functions:For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)To split strings based on patterns try PatternSplitCMNeed to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8KI 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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    We turned them off when we migrated data into a new database. But never under normal operating conditions.

  • ebouras

    SSC Enthusiast

    Points: 190

    Did I miss in the article exactly why your developers needed the warnings set off? The implication is that they were trying to account for bad data. Would the workaround have actually caused problems in your specific case? Maybe they weren't running a COUNT but rather a SUM where the NULL being dropped out is not an issue.

    In the case where there are nulls for a column that is used for aggregates wouldn't the underlying issue be that the column should not have been designed to allow nulls in the first place? Was the ability to "fix" that design flaw in their power or were they just trying to work with what they were given?

    I am not an apologist for lazy developers but in my experience it is common to find "gray area" reasons as the cause for such workarounds - meaning the fault is not fully on the person doing the workaround.

    When I first saw this title I immediately thought of an SSIS package failing because of the warnings being interpreted as an error. This is where I've seen the need for turning warning off come up at my work. In our case there has been no ill effect because the queries that caused the warnings were evaluated for ill effects.

    A nice thing to add to this article would be a mention of this SSIS issue and how it can be mitigated so that we can at least stamp out that little enclave of "lazy" devs.

    FYI - this MS Connect issue states that the SSIS issue was resolved with SQL Server 2012: https://connect.microsoft.com/SQLServer/feedback/details/483175/failure-because-of-warning-about-null-aggregation

  • roger.plowman

    SSChampion

    Points: 10136

    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.

  • Nakul Vachhrajani

    SSChampion

    Points: 10151

    ebouras (8/23/2016)


    Did I miss in the article exactly why your developers needed the warnings set off? The implication is that they were trying to account for bad data. Would the workaround have actually caused problems in your specific case? Maybe they weren't running a COUNT but rather a SUM where the NULL being dropped out is not an issue.

    In the case where there are nulls for a column that is used for aggregates wouldn't the underlying issue be that the column should not have been designed to allow nulls in the first place? Was the ability to "fix" that design flaw in their power or were they just trying to work with what they were given?

    I am not an apologist for lazy developers but in my experience it is common to find "gray area" reasons as the cause for such workarounds - meaning the fault is not fully on the person doing the workaround.

    When I first saw this title I immediately thought of an SSIS package failing because of the warnings being interpreted as an error. This is where I've seen the need for turning warning off come up at my work. In our case there has been no ill effect because the queries that caused the warnings were evaluated for ill effects.

    A nice thing to add to this article would be a mention of this SSIS issue and how it can be mitigated so that we can at least stamp out that little enclave of "lazy" devs.

    FYI - this MS Connect issue states that the SSIS issue was resolved with SQL Server 2012: https://connect.microsoft.com/SQLServer/feedback/details/483175/failure-because-of-warning-about-null-aggregation%5B/quote%5D

    Basically, there were aggregations being done as part of nightly jobs which spanned COUNT, SUM, MIN and MAX operations. The problem was that the team monitoring the servers kept reporting the warnings as "errors" (being of the mindset that anything reported by the server unless it contains the word "success" is bad).

    The development team could not prevent NULLs because in the system, NULL were actually valid values (e.g. revenue not reported v/s a zero revenue). So, allowing NULLs in the system was not a flaw - it's by business design.

    My intention in preventing the team from switching ANSI_WARNINGS to OFF was because doing so opens the door to generation of potentially bad data.

    On the SSIS angle, yes it was an issue in SSIS for SQL 2008. However, the team switched from DTS to SSIS from 2012 and hence did not encounter issues there.

    Thank-you for your time and sharing your valuable feedback!

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

    Follow me on
    Twitter: @sqltwins

  • sknox

    SSChampion

    Points: 12216

    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.

  • ebouras

    SSC Enthusiast

    Points: 190

    Thanks for the explanation Rakul... and for the article. I didn't say so before but I found it well put together.

Viewing 15 posts - 1 through 15 (of 37 total)

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