Scope of SET ANSI_WARNINGS OFF

Question

if I have SET ANSI_WARNINGS OFF in one stored procedure to get rid of a warning, it only takes effects of that stored procedure and has no impact on other ones, which means in other stored procedures, the ANSI_WARNINGS is still on. What if I want to turn it off for all stored procedures? Why it is default on? How could I know that?

Do other settings(e.g., NOCOUNT) in sql server work the same way?

Thanks a lot.

It will be great if anybody can share articles about common characteristics of these settings with me.

Answer 1

It's possibly worth point out that not only does it NOT affect other stored procedures, it only affects statements following the SET operation

e.g. run this and you will see that the option is turned off then on and then off again.

CREATE TABLE test
(
    intvalue int NULL
) 
INSERT INTO test VALUES (1)
INSERT INTO test VALUES (NULL)
SET ANSI_WARNINGS OFF
SELECT COUNT(intvalue) FROM test
-- (1 row(s) affected)
SET ANSI_WARNINGS ON
SELECT COUNT(intvalue) FROM test
-- (1 row(s) affected)
-- Warning: Null value is eliminated by an aggregate or other SET operation.
SET ANSI_WARNINGS OFF
SELECT COUNT(intvalue) FROM test
-- (1 row(s) affected)
DROP TABLE test

Answer 2

From BOL:

SQL Server includes the ANSI_WARNINGS database option. This is equivalent to SET ANSI_WARNINGS. When SET ANSI_WARNINGS is ON, errors or warnings are raised in divide-by-zero, string too large for database column, and other similar errors. When SET ANSI_WARNINGS is OFF, these errors and warnings are not raised. The default value in the model database for SET ANSI_WARNINGS is OFF. If not specified, the setting of ANSI_WARNINGS applies. If SET ANSI_WARNINGS is OFF, SQL Server uses the value of the is_ansi_warnings_on column in the sys.databases catalog view.

You can read about it in BOL (F1 in Management Studio) or on MSDN http://msdn.microsoft.com/en-us/library/ms190368.aspx

NOCOUNT works the same.