sql server - 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