ANSI Options Part 4 - SET ANSI_WARNINGS
This article continues my series on ANSI settings in SQL Server. The other parts are:
Introduction
SQL Server comforms to a number of ANSI standards to varying degrees. The level of compliance or even whether a particular object behaves as per an ANSI Standard is usually goverened by an option setting. In this series I plan to look at the various options that are available in SQL Server
What is this option?
ANSI Warnings are error messages that are displayed for various operations that occur. These error messages are mainly to inform the user if the underlying data might be affected by the presence of NULL values, divide by zero, and other conditions.
Are they useful?
Not sure. I tend to ignore them, but I work in an MS only environment. If you need to deal with cross platform data, then knowing if the result of some query might vary would be important.
There are a few conditions where this value might affect your results.
First, in making na aggregate query. If you have NULL values in your data and this option is set to ON, then a warning will appear that informs you there are NULL values. With SQL Server, you might not care, but you would want to know how NULLs affected the value, especially if this is an average (they are ignored). On another DBMS, however, they might be included in the average (as a 0), which would be important to know.
The next place this option affects is an important one and might cause you some real troublshooting headaches. Suppose you wrote a UDF that performs some math operations and includes division. Works fine for you and then you pass it off to a developer.
A month later he gets around to using the function and calls you to complain. Your function is returning NULL values.
Never happened to you. So you start investigating and you never get a NULL value. After a few hours of pulling your hair out, you finally trace his calls and duplicate the calls. On one where he reports a NULL, you receive:
Server: Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered.
Hmmmm, why is he getting NULL?
After a little more investigating you find that he has ANSI_WARNINGS set to OFF, while you have it ON. This is the other area where this option can be a headache.
NOTE: The SET ARITHABORT and SET ARITHIGNORE options must be set to OFF and ON to eliminate these messages.
This option changes the behavior of the transaction when a divide by zero or arithmatic overflow occurs. If set to ON, then the error message is generate AND the transaction is rolled back.
Note that: The transaction is ROLLED BACK.
If this is set to off, then NULL is returned and the transaction completes. An important distinction. If you are working with character columns and you overflow, the data is truncated to the length of the column and the statement succeeds. This can be incredibly annoying and cause issues with many applications. Better to handle the rollback.
What to do?
The default setting is ON for the SQL Server drivers. Leave it alone. Let me repeat that. LEAVE IT ALONE.
Unless you have a compelling reason to change it. And by compelling I mean you cannot work with it any other way. Spend some time and look for another way.
References:
- Books Online - Search "Set ANSI_WARNINGS"
Conclusions
I almost didn't write this article. Seemed like a simple option until I found out that you can get data truncated. Or errors are ignored and transactions succeed. Something I wasn't aware of and I doubt many people are. As with the other articles, I hope this sticks in your mind and comes in handy one day.
As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article.
Steve Jones
©dkRanch.net August 2002
