我正在使用SQL Server 2017。
似乎SET ANSI_WARNINGS ON没有按照文档的建议设置ARITHABORT值，还是我误解了？
例如命令
SET ANSI_WARNINGS OFF
SET ARITHABORT OFF
SET ANSI_WARNINGS ON
文档
说：
将 ANSI_WARNINGS 设置为 ON 会在数据库兼容级别设置为 90 或更高时将 ARITHABORT 隐式设置为 ON。如果数据库兼容级别设置为 80 或更早版本，则必须将 ARITHABORT 选项显式设置为 ON。
这是什么意思？
这是否意味着(？
因此，即使ARITHABORT设置值为 OFF 并显示为 OFF 并且ANSI_WARNINGS设置为 ON 也是如此，对于数据库兼容性级别 90 或更高，ARITHABORT设置值实际上是 ON(即使显示和查询的值显示 OFF(。
或者我应该期望能够在SET ANSI_WARNINGS ON后看到ARITHABORT设置为 ON？
我正在使用SQL来查询这些行的设置
SELECT @@OPTIONS AS [user_options],
CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END AS [implicit_transactions],
CASE WHEN @@OPTIONS & 4 = 4 THEN 'ON' ELSE 'OFF' END AS [cursor_close_on_commit],
CASE WHEN @@OPTIONS & 8 = 8 THEN 'ON' ELSE 'OFF' END AS [ansi_warnings],
CASE WHEN @@OPTIONS & 16 = 16 THEN 'ON' ELSE 'OFF' END AS [ansi_padding],
CASE WHEN @@OPTIONS & 32 = 32 THEN 'ON' ELSE 'OFF' END AS [ansi_nulls],
CASE WHEN @@OPTIONS & 64 = 64 THEN 'ON' ELSE 'OFF' END AS [arithabort],
CASE WHEN @@OPTIONS & 256 = 256 THEN 'ON' ELSE 'OFF' END AS [quoted_identifier],
CASE WHEN @@OPTIONS & 1024 = 1024 THEN 'ON' ELSE 'OFF' END AS [ansi_null_dflt_on],
-- all above options combined
CASE WHEN @@OPTIONS & 1342 = 1342 THEN 'ON' ELSE 'OFF' END AS [ansi_defaults]
还有
select db_name(database_id), ansi_warnings, arithabort, *
from sys.dm_Exec_sessions
我考虑了这个测试脚本的输出，它确认当ANSI_WARNINGS设置为 ON 时，ARITHABORT 设置值没有影响。 即，即使设置为OFF，也表现得好像ARITHABORT是ON的..当ANSI_WARNINGS设置为ON..
PRINT '*** SET ANSI_WARNINGS ON';
SET ANSI_WARNINGS ON
-- SET ARITHABORT
-------------------------------------------------------------------------------
-- Create tables t1 and t2 and insert data values.
CREATE TABLE t1 (
a TINYINT,
b TINYINT
);
CREATE TABLE t2 (
a TINYINT
);
GO
INSERT INTO t1
VALUES (1, 0);
INSERT INTO t1
VALUES (255, 1);
GO
PRINT '*** SET ARITHABORT ON';
GO
-- SET ARITHABORT ON and testing.
SET ARITHABORT ON;
GO
PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO
PRINT '*** Resulting data - should be no data';
GO
SELECT *
FROM t2;
GO
-- Truncate table t2.
TRUNCATE TABLE t2;
GO
-- SET ARITHABORT OFF and testing.
PRINT '*** SET ARITHABORT OFF';
GO
SET ARITHABORT OFF;
GO
-- This works properly.
PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab
FROM t1;
GO
-- This works as if SET ARITHABORT was ON.
PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO
PRINT '*** Resulting data - should be 0 rows';
GO
SELECT *
FROM t2;
GO
-- Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
GO
PRINT '*** ############################################################################################################################';
PRINT '*** ############################################################################################################################';
PRINT '*** SET ANSI_WARNINGS OFF';
SET ANSI_WARNINGS OFF
-- SET ARITHABORT
-------------------------------------------------------------------------------
-- Create tables t1 and t2 and insert data values.
CREATE TABLE t1 (
a TINYINT,
b TINYINT
);
CREATE TABLE t2 (
a TINYINT
);
GO
INSERT INTO t1
VALUES (1, 0);
INSERT INTO t1
VALUES (255, 1);
GO
PRINT '*** SET ARITHABORT ON';
GO
-- SET ARITHABORT ON and testing.
SET ARITHABORT ON;
GO
PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO
PRINT '*** Resulting data - should be no data';
GO
SELECT *
FROM t2;
GO
-- Truncate table t2.
TRUNCATE TABLE t2;
GO
-- SET ARITHABORT OFF and testing.
PRINT '*** SET ARITHABORT OFF';
GO
SET ARITHABORT OFF;
GO
-- This works properly.
PRINT '*** Testing divide by zero during SELECT';
GO
SELECT a / b AS ab
FROM t1;
GO
-- This works as if SET ARITHABORT was ON.
PRINT '*** Testing divide by zero during INSERT';
GO
INSERT INTO t2
SELECT a / b AS ab
FROM t1;
GO
PRINT '*** Testing tinyint overflow';
GO
INSERT INTO t2
SELECT a + b AS ab
FROM t1;
GO
PRINT '*** Resulting data - should be 0 rows';
GO
SELECT *
FROM t2;
GO
-- Drop tables t1 and t2.
DROP TABLE t1;
DROP TABLE t2;
GO
PRINT '*** ############################################################################################################################';
我得到这个结果：
*** SET ANSI_WARNINGS ON
(1 row affected)
(1 row affected)
*** SET ARITHABORT ON
*** Testing divide by zero during SELECT
Msg 8134, Level 16, State 1, Line 29
Divide by zero error encountered.
*** Testing divide by zero during INSERT
Msg 8134, Level 16, State 1, Line 35
Divide by zero error encountered.
The statement has been terminated.
*** Testing tinyint overflow
Msg 8115, Level 16, State 2, Line 42
Arithmetic overflow error converting expression to data type tinyint.
The statement has been terminated.
*** Resulting data - should be no data
(0 rows affected)
*** SET ARITHABORT OFF
*** Testing divide by zero during SELECT
Msg 8134, Level 16, State 1, Line 66
Divide by zero error encountered.
*** Testing divide by zero during INSERT
Msg 8134, Level 16, State 1, Line 73
Divide by zero error encountered.
The statement has been terminated.
*** Testing tinyint overflow
Msg 8115, Level 16, State 2, Line 79
Arithmetic overflow error converting expression to data type tinyint.
The statement has been terminated.
*** Resulting data - should be 0 rows
(0 rows affected)
*** ############################################################################################################################
*** ############################################################################################################################
*** SET ANSI_WARNINGS OFF
(1 row affected)
(1 row affected)
*** SET ARITHABORT ON
*** Testing divide by zero during SELECT
Msg 8134, Level 16, State 1, Line 125
Divide by zero error encountered.
*** Testing divide by zero during INSERT
Msg 8134, Level 16, State 1, Line 131
Divide by zero error encountered.
*** Testing tinyint overflow
Msg 8115, Level 16, State 2, Line 138
Arithmetic overflow error converting expression to data type tinyint.
*** Resulting data - should be no data
(0 rows affected)
*** SET ARITHABORT OFF
*** Testing divide by zero during SELECT
Division by zero occurred.
(2 rows affected)
*** Testing divide by zero during INSERT
Division by zero occurred.
(2 rows affected)
*** Testing tinyint overflow
Arithmetic overflow occurred.
(2 rows affected)
*** Resulting data - should be 0 rows
(4 rows affected)
*** ############################################################################################################################