SQLServerCentral Article

What is the impact of setting SET ANSI_WARNINGS OFF?

,

I was recently asked a very simple, but very important question: “What is the impact of setting SET ANSI_WARNING OFF in a stored procedure?”

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.

What is the SET option ANSI_WARNINGS?

The ANSI_WARNINGS controls the standard ISO behavior of the SQL Server database engine for various error conditions. When ANSI_WARNINGS is set to ON, the engine follows the standard ISO behavior for the following situations:

  • Encountering a NULL value during an aggregation operation
  • Encountering a Divide by Zero error
  • String truncation

When ANSI_WARNINGS is OFF, the engine follows a non-standard behavior, which reduces data quality and depending upon your business context, may generate bad data.

The examples below demonstrate the standard and non-standard ISO behavior when ANSI_WARNINGS is ON v/s OFF for each of the scenarios mentioned above.

Null value is eliminated by an aggregate or other SET operation

In our system, most aggregates are performed over revenue values, which would rarely be NULL. One would either have a 0, signifying no revenue, or some value (negative or positive), signifying a balance amount.

However, when aggregations (SUM, AVG, MAX, MIN, STDEV, STDEVP, VAR, VARP, or COUNT) are performed over columns that may contain NULLs (lap timings for example) a warning will be returned. This code sample demonstrates the issue.

In this example:

  • Confirm that ANSI_WARNINGS are set to ON by using the @@OPTIONS configuration function
  • Generate a test scenario wherein we have race data (LapNumber and LapTime) from some athletes. In cases where the athlete was not able to complete the lap, the LapTime would be NULL
  • We try to perform a simple aggregation to find the minimum lap time, i.e. "Fastest Lap Time"
USE tempdb;
GO
SET NOCOUNT ON;
SET ANSI_WARNINGS ON;
GO
--Confirm that the option SET ANSI_WARNINGS is ON
IF (@@OPTIONS & 8) > 0
BEGIN
    PRINT 'SET ANSI_WARNINGS is ON.';
END
GO
DECLARE @lapTiming TABLE (LapNumber INT           NOT NULL IDENTITY(1,1),
                          RunnerId  INT           NOT NULL,
                          LapTime   DECIMAL(19,4)     NULL
                         );
INSERT INTO @lapTiming (RunnerId, LapTime)
VALUES (1, '1.10'),
       (2, '1.12'),
       (3, NULL  ), --Assume this player is DNF
       (4, '1.05');
SELECT MIN(lt.LapTime) AS FastestLap
FROM @lapTiming AS lt;
/*******************
RESULTS
--------------------
SET ANSI_WARNINGS is ON.
Warning: Null value is eliminated by an aggregate or other SET operation.
*******************/GO

The results are shown below. While the aggregation worked, we also get a warning in the "Messages" tab of SSMS:

The warning simply notifies the user that the rows with NULL values were ignored when performing the aggregation, which should be expected because one cannot perform any mathematical operation on an unknown value (NULL indicates an unknown value). Therefore, this behavior is not an error, but a note that the system is actually working as expected.

However, when ANSI_WARNINGS is set to OFF, no warning is returned and SQL Server silently ignores the rows with NULL values. Here is the same code with the ANSI_WARNINGS set to OFF.

USE tempdb;
GO
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
GO
--Confirm that the option SET ANSI_WARNINGS is ON
IF (@@OPTIONS & 8) > 0
BEGIN
    PRINT 'SET ANSI_WARNINGS is ON.';
END
GO
DECLARE @lapTiming TABLE (LapNumber INT           NOT NULL IDENTITY(1,1),
                          RunnerId  INT           NOT NULL,
                          LapTime   DECIMAL(19,4)     NULL
                         );
INSERT INTO @lapTiming (RunnerId, LapTime)
VALUES (1, '1.10'),
       (2, '1.12'),
       (3, NULL  ), --Assume this player is DNF
       (4, '1.05');
SELECT MIN(lt.LapTime) AS FastestLap
FROM @lapTiming AS lt;
GO

The results and Message tabs are shown here.

This is especially confusing when performing a COUNT on a column with NULL values and the result does not match the number of rows available in the result set. This is because the rows with NULL values would have been eliminated from the aggregation without any notification of the elimination.

Conclusion #1: As can be seen from the example above, 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.

Msg 8134 Divide by zero error encountered

Typically, a division by zero error is a classic indication of bad data. One would expect that the offending statement in the batch is terminated and that the transaction encountering the divide by zero error is rolled back.

This is the ISO standard behavior enforced when both ARITHABORT and ANSI_WARNINGS are set to ON. ARITHABORT ON terminates the statement as soon as the divide by zero error is encountered and ANSI_WARNINGS ON takes care of reporting the error to the client application.

We can see this in the code sample below which performs a simple division operation on a set of values. One of the divisors is a zero and will result in a divide by zero error. When the error is encountered, the entire transaction will be rolled back because ANSI_WARNINGS is set to ON and the client application will be notified accordingly.

USE tempdb;
GO
SET NOCOUNT ON;
SET ANSI_WARNINGS ON; --Setting this to ON will automatically turn ARITHABORT to ON
GO
--Confirm that the option SET ANSI_WARNINGS is ON
IF (@@OPTIONS & 8) > 0
BEGIN
    PRINT 'SET ANSI_WARNINGS is ON.';
END
GO
IF OBJECT_ID('#mathOps','U') IS NOT NULL
BEGIN
    DROP TABLE #mathOps;
END
GO
CREATE TABLE #mathOps (RowId      INT           NOT NULL IDENTITY(1,1),
                       OpA        DECIMAL(19,4)     NULL,
                       OpB        DECIMAL(19,4)     NULL,
                       MathResult DECIMAL(19,4)     NULL
                      );
INSERT INTO #mathOps (OpA, OpB)
VALUES (1.0,  2.0),
       (1.0,  3.0),
       (1.0,  0.0),
       (1.0, 33.0);
GO
UPDATE mops
SET mops.MathResult = (mops.OpA/mops.OpB)
FROM #mathOps AS mops
GO
SELECT mops.RowId,
       mops.OpA,
       mops.OpB,
       mops.MathResult
FROM #mathOps AS mops;
GO

The screenshot below confirms that none of the records were updated (the statement was indeed terminated and that transaction rolled back) with the following error message being returned:

SET ANSI_WARNINGS is ON.
Msg 8134, Level 16, State 1, Line 36

Divide by zero error encountered.

The statement has been terminated.

However, when both ARITHABORT and ANSI_WARNINGS are OFF, the statement is not aborted. Instead of a rollback, what we see is that when a divide by zero is encountered, the output of the division is a NULL, i.e. unknown.

The setting of ARITHABORT to OFF prevents the statement from rolling back and the ANSI_WARNINGS causes a divide by zero to be evaluated as a NULL.

USE tempdb;
GO
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF; --This will NOT set ARITHABORT to OFF
SET ARITHABORT OFF;    --Hence, manually setting it to OFF
GO
--Confirm that the option SET ANSI_WARNINGS is ON
IF (@@OPTIONS & 8) > 0
BEGIN
    PRINT 'SET ANSI_WARNINGS is ON.';
END
GO
IF OBJECT_ID('#mathOps','U') IS NOT NULL
BEGIN
    DROP TABLE #mathOps;
END
GO
CREATE TABLE #mathOps (RowId      INT           NOT NULL IDENTITY(1,1),
                       OpA        DECIMAL(19,4)     NULL,
                       OpB        DECIMAL(19,4)     NULL,
                       MathResult DECIMAL(19,4)     NULL
                      );
INSERT INTO #mathOps (OpA, OpB)
VALUES (1.0,  2.0),
       (1.0,  3.0),
       (1.0,  0.0),
       (1.0, 33.0);
GO
UPDATE mops
SET mops.MathResult = (mops.OpA/mops.OpB)
FROM #mathOps AS mops
GO
SELECT mops.RowId,
       mops.OpA,
       mops.OpB,
       mops.MathResult
FROM #mathOps AS mops;
GO

The screenshots below show that the transaction completed successfully and the data condition resulting in a divide by zero error returned a NULL or an unknown. The client application only receives a warning message that it can discard (and most systems will).

The client application will therefore never realize that an erroneous data scenario just occurred and bad data will be persisted due to the success of the transaction.

Conclusion #2: Setting ANSI_WARNINGS OFF can cause the database engine to accept possibly bad data.

  • The ability to ensure data quality and accuracy becomes most important when trying to work with statistical information, e.g. census data that involves computations like population or age distribution in a given area. It is imperative that in such cases the bad data is rectified after a data review/physical cross-check with data collected from the source and then the computation is performed again
  • Having bad data in a system may also 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

Msg 8152 String or binary data would be truncated

One of the things we see very frequently when exchanging data between systems is difference in the sizes of various fields. When trying to insert a string into a field in a table which is smaller than the length of the input string, the standard behavior with ANSI_WARNINGS ON is to return a string truncation error.

In the example provided below, we are inserting more than 10 characters (the author's surname) into a field that only allows 10 characters. 

The insert fails and the statement is rolled back because we would want to either insert all of the data of the input string into the table, or none (I would not want my last name to be stored as incomplete in any database).

USE tempdb;
GO
SET NOCOUNT ON;
SET ANSI_WARNINGS ON;
GO
--Confirm that the option SET ANSI_WARNINGS is ON
IF (@@OPTIONS & 8) > 0
BEGIN
    PRINT 'SET ANSI_WARNINGS is ON.';
END
GO
--String Truncation Errors
DECLARE @personTable TABLE (FirstName CHAR(10),
                            LastName  CHAR(10)
                           );
--Conduct the test insert
INSERT INTO @personTable (FirstName, LastName)
VALUES ('Nakul', 'Vachhrajani');
SELECT pt.FirstName,
       pt.LastName
FROM @personTable AS pt;
GO

This following error message is returned:

SET ANSI_WARNINGS is ON.
Msg 8152, Level 16, State 14, Line 23

String or binary data would be truncated.

The statement has been terminated.

Now, when ANSI_WARNINGS are turned OFF, silent truncation of the input string happens when the input string is longer than the field into which it is being inserted or updated.

The example provided below is same as the one above, with the exception that ANSI_WARNINGS is explicitly set to OFF.

USE tempdb;
GO
SET NOCOUNT ON;
SET ANSI_WARNINGS OFF;
GO
--Confirm that the option SET ANSI_WARNINGS is ON
IF (@@OPTIONS & 8) > 0
BEGIN
    PRINT 'SET ANSI_WARNINGS is ON.';
END
GO
--String Truncation Errors
DECLARE @personTable TABLE (FirstName CHAR(10),
                            LastName  CHAR(10)
                           );
--Conduct the test insert
INSERT INTO @personTable (FirstName, LastName)
VALUES ('Nakul', 'Vachhrajani');
SELECT pt.FirstName,
       pt.LastName
FROM @personTable AS pt;
GO

Notice the incomplete LastName in the screenshot above. The input data was silently truncated without the client system ever knowing about the truncation. Such silent truncation of string data can lead to data quality issues – which can go undetected when ANSI_WARNINGS are kept OFF untill end users notice names, addresses or other notes being frustratingly truncated and call the product support helpline.

Conclusion #3: Setting ANSI_WARNINGS OFF can cause the database engine to silently generate bad data. Silent truncation of strings can go unnoticed unless cross-verification of stored data is done at each stage of a data transfer. Storing truncated strings ultimately results in poor data quality in cases where names, addresses, warning messages, incident reports or other notes and notices are being stored.

Impact on Table Design and Index Management

So far, we have seen that setting ANSI_WARNINGS OFF can generate potentially bad data. Bad data conditions are generally silent and are not immediately detected. The impact of ANSI_WARNINGS on table design and index management is a more visible one - in the sense that indexes on computed columns or indexed views need ANSI_WARNINGS to be ON.

If ANSI_WARNINGS are OFF, attempting to manipulate data in tables with indexed computed columns or indexed views results in an error. The example provided below creates a table with a non-clustered index on a computed column. This is done with ANSI_WARNINGS set to ON. When inserting data, ANSI_WARNINGS are set to OFF for the purposes of this test.

USE tempdb;
GO
--Confirm ANSI_WARNINGS is still ON
IF (@@OPTIONS & 8) = 8
BEGIN
    PRINT 'ANSI_WARNINGS is ON';
END
GO
--Now, create a table with some computed columns
--Safety Check
IF OBJECT_ID('dbo.MathematicalTables','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.MathematicalTables;
END
GO
CREATE TABLE dbo.MathematicalTables
        (intA INT NOT NULL,
         intB INT NOT NULL,
         intC AS (intA * intB),
         CONSTRAINT pk_MathematicalTables PRIMARY KEY CLUSTERED (intA, intB)
        );
GO
CREATE NONCLUSTERED INDEX nc_MathematicalTables ON dbo.MathematicalTables (intC);
GO
--Now, turn ANSI_WARNINGS OFF
SET ANSI_WARNINGS OFF;
GO
INSERT INTO dbo.MathematicalTables (intA, intB)
SELECT set1.intA,
       set2.intB
FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS set1(intA)
CROSS JOIN (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS set2(intB);
GO
--Revert Back the environment
SET ANSI_WARNINGS ON;
GO

When the code provided above is executed, we receive the following error:

Msg 1934, Level 16, State 1, Line 34

INSERT 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.

As the error suggests, our INSERT failed because ANSI_WARNINGS are OFF. Keeping ANSI_WARNINGS OFF prevents us from XML and spatial data manipulation and working with filtered index in addition to issues with computed columns demonstrated above.

If ANSI_WARNINGS are OFF at the time of index creation, the index creation itself will fail.This is demonstrated by the code provided below, which is same as the one above, but with an exception that ANSI_WARNINGS are turned OFF when the index is being created.

USE tempdb;
GO
--Confirm ANSI_WARNINGS is still ON
IF (@@OPTIONS & 8) = 8
BEGIN
    PRINT 'ANSI_WARNINGS is ON';
END
GO
--Now, create a table with some computed columns
--Safety Check
IF OBJECT_ID('dbo.MathematicalTables','U') IS NOT NULL
BEGIN
    DROP TABLE dbo.MathematicalTables;
END
GO
--Now, turn ANSI_WARNINGS OFF
SET ANSI_WARNINGS OFF;
GO
CREATE TABLE dbo.MathematicalTables
        (intA INT NOT NULL,
         intB INT NOT NULL,
         intC AS (intA * intB),
         CONSTRAINT pk_MathematicalTables PRIMARY KEY CLUSTERED (intA, intB)
        );
GO
CREATE NONCLUSTERED INDEX nc_MathematicalTables ON dbo.MathematicalTables (intC);
GO
--Revert Back the environment
SET ANSI_WARNINGS ON;
GO

The following error is reported if ANSI_WARNINGS are OFF when an index is being created.

Msg 1934, Level 16, State 1, Line 31

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.

Conclusion #4: Index creation on computed columns, filtered indexes, data manipulation of XML and Spatial data requires that ANSI_WARNINGS be set to ON in addition to other SET options (ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS).

Testing for ANSI_WARNINGS

The following code demonstrates how to check whether ANSI_WARNINGS is ON or OFF. The @@OPTIONS function returns a bitmap of the various SET options that can be turned ON or OFF, converted to a base 10 (decimal) integer. All we need to do is use binary AND operation to check whether the bit at the appropriate binary position is 1 (i.e. option is ON) or 0 (i.e. option is OFF).

--Confirm that the option SET ANSI_WARNINGS is ON
IF (@@OPTIONS & 8) > 0
BEGIN
    PRINT 'SET ANSI_WARNINGS is ON.';
END
GO

How to configure ANSI_WARNINGS for all sessions on the server?

We simply use the reverse logic used to test for a set option being ON. The system stored procedure sp_configure can be used to set the appropriate option by specifying the decimal representation of the appropriate bit position.

In case of ANSI_WARNINGS, the bitmap for decimal value 8 represents that the option is ON.

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

IMPORTANT NOTE:

  • The value supplied as part of the "user options" setting to the system stored procedure sp_configure overwrites the existing configuration. In this case, passing a value of 8 means that it will set all other user options to OFF and only keep ANSI_WARNINGS as ON
  • In order to set multiple user options as ON, a binary OR needs to be done on the configuration values of various SET options and the resultant value needs to be supplied. 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 corresponding to the SET options:
--SELECT (8|256|16384)
--Result: 16648
EXEC sys.sp_configure N'user options', N'16648'
GO
RECONFIGURE
GO

Summary

The SET option ANSI_WARNINGS controls the ISO behavior of the SQL Server database engine. 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.

In addition to data truncation, turning ANSI_WARNINGS OFF also has a serious impact on table design and index maintenance such that SQL Server prevents insertion of data in the affected tables.

Further Reading

Disclaimer

A few disclaimers about this information and code:

  • The scripts in this article are provided "as-is", i.e. without warranties of any kind and are intended for demonstration purposes only.
  • Request you to use these scripts for understanding and study purposes in your development environments only - they are not meant for use in production. The author is not responsible for any damage caused by misuse of these scripts.
  • Scripts are tested on SQL Server 2012 and SQL Server 2014
  • The opinions expressed herein (social media and other community portals) are his own personal opinions and do not represent his employer’s view in anyway

About the author

Nakul Vachhrajani is a Microsoft Certified Professional (MCTS: SQL 2008, MCP: SQL 2012), TOGAF 9 certified Technical Architect and systems development professional with Capgemini (formerly, IGATE) having a total IT experience of more than 12 years. He has more than a decade of dedicated experience on Data Quality, Data Management and Database Administration. He has a comprehensive grasp on data migrations to Salesforce.com, and product development, implementation and sustenance (including handling of production support escalations) on products using MS SQL Server and C, C++, Visual C++/C#.

Nakul is the author of 12 technical articles on database technologies (published worldwide in edited journals & communities) and 3 whitepapers (one of which has been published nationwide). Nakul is an active blogger (more than 370 blogs on https://nakulvachhrajani.com, also syndicated on ToadWorld - a software community by Dell), and can also be found on forums at SQLServerCentral and Experts-Exchange. He has been a guest columnist for SQLAuthority.com and SQLServerCentral.com. He has also presented a webcast in Microsoft Virtual Tech Days.

In addition to his passion about SQL Server, Nakul also contributes to the academia out of personal interest. He visits various colleges and universities as an external faculty to judge project activities being carried out by the students.

When not working occupied with work, Nakul likes to spend time with his family. His hobbies include travelling, listening to music and reading till late into the night.

Specialties: Microsoft SQL Server - Administration, Development, Data Quality & Data Management, migration of on-premise SQL Server data to Salesforce.com

Product Development, Sustenance & escalation management in an agile environment

Blog | LinkedIn | Twitter | Google+

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.6 (30)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.6 (30)

You rated this post out of 5. Change rating

Related content