sql-server - 如何查询 ARITHABORT、ANSI WARNINGS 和 ARITHIGNORE 的服务器默认设置？

Question

我需要查询 SQL Server 并确定 ARITHABORT、ANSI WARNINGS 和 ARITHIGNORE 的服务器默认值。最好的方法是什么？

Answer 1

ARITHABORT 的服务器默认设置是用户选项位掩码的一部分。要检索默认选项，请在 sys.configurations 表中查询“用户选项”设置值，并使用位逻辑来确定每个单独设置的值。 ARITHABORT 是第 7 位位置，因此使用 64 来确定它的值。 sys.configurations 表中的值列是 sql_variant 类型，因此有必要将该值转换为 INT 。当前连接的设置可以由@@OPTIONS 的值确定。数据库的默认设置可以通过 sp_dboption 存储过程确定:sp_dboption 'databaseNameHere', 'arithabort'。

SELECT [ARITHABORT] = CASE CAST(cfg.value AS INT) & 64 --bitwise operation on the 7th position
    WHEN 0 THEN 'OFF'
    ELSE 'ON' END
FROM sys.configurations cfg
WHERE name = 'user options'

-----------------------------------
-- All the user options settings --
-----------------------------------

DECLARE @UserOptionBitValue TABLE 
    (BitValue INT,
     Setting VARCHAR(100),
     SettingDescription VARCHAR(500))

---------------------------------------------------------------------------------
-- User Options definitions 
-- http://msdn.microsoft.com/en-us/library/ms176031.aspx
---------------------------------------------------------------------------------
INSERT @UserOptionBitValue VALUES (1,'DISABLE_DEF_CNST_CHK','Controls interim or deferred constraint checking.')
INSERT @UserOptionBitValue VALUES (2,'IMPLICIT_TRANSACTIONS','For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.')
INSERT @UserOptionBitValue VALUES (4,'CURSOR_CLOSE_ON_COMMIT','Controls behavior of cursors after a commit operation has been performed.')
INSERT @UserOptionBitValue VALUES (8,'ANSI_WARNINGS','Controls truncation and NULL in aggregate warnings.')
INSERT @UserOptionBitValue VALUES (16,'ANSI_PADDING','Controls padding of fixed-length variables.')
INSERT @UserOptionBitValue VALUES (32,'ANSI_NULLS','Controls NULL handling when using equality operators.')
INSERT @UserOptionBitValue VALUES (64,'ARITHABORT','Terminates a query when an overflow or divide-by-zero error occurs during query execution.')
INSERT @UserOptionBitValue VALUES (128,'ARITHIGNORE','Returns NULL when an overflow or divide-by-zero error occurs during a query.')
INSERT @UserOptionBitValue VALUES (256,'QUOTED_IDENTIFIER','Differentiates between single and double quotation marks when evaluating an expression.')
INSERT @UserOptionBitValue VALUES (512,'NOCOUNT','Turns off the message returned at the end of each statement that states how many rows were affected.')
INSERT @UserOptionBitValue VALUES (1024,'ANSI_NULL_DFLT_ON','Alters the session''s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.')
INSERT @UserOptionBitValue VALUES (2048,'ANSI_NULL_DFLT_OFF','Alters the session''s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.')
INSERT @UserOptionBitValue VALUES (4096,'CONCAT_NULL_YIELDS_NULL','Returns NULL when concatenating a NULL value with a string.')
INSERT @UserOptionBitValue VALUES (8192,'NUMERIC_ROUNDABORT','Generates an error when a loss of precision occurs in an expression.')
INSERT @UserOptionBitValue VALUES (16384,'XACT_ABORT','Rolls back a transaction if a Transact-SQL statement raises a run-time error.')

SELECT
    BitValue,
    Setting,

    [DefaultState]= CASE CAST(cfg.value AS INT) & BitValue
    WHEN 0 THEN 'OFF'
    ELSE 'ON' END,

    [CurrentState] = CASE @@OPTIONS & BitValue
    WHEN 0 THEN 'OFF'
    ELSE 'ON' END,

    SettingDescription
FROM
    sys.configurations cfg
    CROSS JOIN @UserOptionBitVAlue def
WHERE
    name = 'user options'