@@OPTIONS (Transact-SQL)

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體

傳回目前 SET 選項的相關資訊。

Transact-SQL 語法慣例

Syntax

@@OPTIONS

注意

若要檢視 SQL Server 2014 （12.x） 和舊版的 Transact-SQL 語法，請參閱 舊版檔。

傳回類型

integer

備註

選項可來自使用 SET 命令或 sp_configure user options 值。 以 SET 命令設定的工作階段值會覆寫 sp_configure 選項。 許多工具，例如 Management Studio，會自動設定選項。 每位使用者都有一個代表組態的 @@OPTIONS 函數。

您可以利用 SET 陳述式來變更特定使用者工作階段的語言和查詢處理選項。 @@OPTIONS 只能偵測設定為 ON 或 OFF 的選項。

@@OPTIONS 函數會傳回選項的點陣圖，轉換為以 10 為基礎 (十進位) 的整數。 位元設定會儲存在設定使用者選項伺服器組態選項文章中資料表內描述的位置。

若要解碼 @@OPTIONS 值，請將 @@OPTIONS 傳回的整數轉換為二進位，然後在設定使用者選項伺服器組態選項的資料表中查詢該值。 例如，若 SELECT @@OPTIONS; 傳回值 5496，請使用 Windows 工程型小算盤 (calc.exe) 將十進位 5496 轉換成二進位。 結果為 1010101111000。 最右邊的字元 (二進位 1、2 及 4) 為 0，表示資料表中的前三個項目為關閉 (off)。 查閱資料表，您會看到它們為 DISABLE_DEF_CNST_CHK、IMPLICIT_TRANSACTIONS 和 CURSOR_CLOSE_ON_COMMIT。 下一個項目 (ANSI_WARNINGS 位置中的 1000) 為 on。 在位元對應中繼續向左，然後在選項清單中向下。 當最左邊的選項為 0 時，就會被類型轉換截斷。 點陣圖 1010101111000 實際上是 001010101111000 來表示所有 15 個選項。

範例 C 提供自動將 @@OPTIONS 位元遮罩對應至使用者選項的查詢。

範例

A. 變更影響行為的示範

下列範例示範兩個不同 CONCAT_NULL_YIELDS_NULL 選項的設定，導致串連行為的差異。

SELECT @@OPTIONS AS OriginalOptionsValue;
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT 'abc' + NULL AS ResultWhen_OFF, @@OPTIONS AS OptionsValueWhen_OFF;
  
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT 'abc' + NULL AS ResultWhen_ON, @@OPTIONS AS OptionsValueWhen_ON;

B. 測試用戶端 NOCOUNT 設定

下列範例會設定 NOCOUNT``ON，然後測試 @@OPTIONS 的值。 NOCOUNT``ON 選項會防止針對工作階段中的每個陳述式，將受影響之資料列數的相關訊息傳回發出要求的用戶端。 @@OPTIONS 值設為 512 (0x0200)。 這代表 NOCOUNT 選項。 這個範例會測試是否啟用了用戶端的 NOCOUNT 選項。 例如，它可以協助您追蹤用戶端的效能差異。

SET NOCOUNT ON
IF @@OPTIONS & 512 > 0
RAISERROR ('Current user has SET NOCOUNT turned on.', 1, 1)

C. 使用 PIVOT 查詢檢閱 @@OPTIONS 位元遮罩

下列範例會使用資料表值建構函數來產生數值清單參考，然後使用位元運算子比較 @@OPTIONS 的值。 APPLY 子句會執行字串串連來產生字元位元遮罩，而另一個則會產生別名，以檢閱來自設定使用者選項伺服器組態選項中記載的值。

SELECT S.Bits,
    Flags.*
FROM (
    SELECT optRef,
        posRef,
        flagCheck
    FROM (
        SELECT ones.n + tens.n * 10
        FROM ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) ones(n),
            ( VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9) ) tens(n)
        ) f1(powRef)
    CROSS APPLY (
        SELECT POWER(2, powRef)
        WHERE powRef <= 16
        ) f2(binRef)
    CROSS JOIN (
        VALUES (@@OPTIONS)
        ) f3(optRef)
    CROSS APPLY (
        SELECT (optRef & binRef) / binRef
        ) f4(flagRef)
    CROSS APPLY (
        SELECT RIGHT(CONVERT(VARCHAR(2), CAST(powRef AS VARBINARY(1)), 2), 1) [posRef],
            CAST(flagRef AS INT) [flagCheck]
        ) pref
    ) TP
PIVOT( MAX( flagCheck ) FOR posRef IN ( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [A], [B], [C], [D], [E], [F] )) P
CROSS APPLY (
    SELECT CONCAT ( '', [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [A], [B], [C], [D], [E], [F] ),
        CONCAT ( '', [F], [E], [D], [C], [B], [A], [9], [8], [7], [6], [5], [4], [3], [2], [1], [0] )
    ) S (stib, Bits)
CROSS APPLY (
    SELECT
          CAST(P.[0] AS BIT) /* 1     */ [DISABLE_DEF_CNST_CHK] -- Controls interim or deferred constraint checking.
        , CAST(P.[1] AS BIT) /* 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.
        , CAST(P.[2] AS BIT) /* 4     */ [CURSOR_CLOSE_ON_COMMIT] -- Controls behavior of cursors after a commit operation has been performed.
        , CAST(P.[3] AS BIT) /* 8     */ [ANSI_WARNINGS] -- Controls truncation and NULL in aggregate warnings.
        , CAST(P.[4] AS BIT) /* 16    */ [ANSI_PADDING] -- Controls padding of fixed-length variables.
        , CAST(P.[5] AS BIT) /* 32    */ [ANSI_NULLS] -- Controls NULL handling when using equality operators.
        , CAST(P.[6] AS BIT) /* 64    */ [ARITHABORT] -- Terminates a query when an overflow or divide-by-zero error occurs during query execution.
        , CAST(P.[7] AS BIT) /* 128   */ [ARITHIGNORE] -- Returns NULL when an overflow or divide-by-zero error occurs during a query.
        , CAST(P.[8] AS BIT) /* 256   */ [QUOTED_IDENTIFIER] -- Differentiates between single and double quotation marks when evaluating an expression.
        , CAST(P.[9] AS BIT) /* 512   */ [NOCOUNT] -- Turns off the message returned at the end of each statement that states how many rows were affected.
        , CAST(P.[A] AS BIT) /* 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.
        , CAST(P.[B] AS BIT) /* 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.
        , CAST(P.[C] AS BIT) /* 4096  */ [CONCAT_NULL_YIELDS_NULL] -- Returns NULL when concatenating a NULL value with a string.
        , CAST(P.[D] AS BIT) /* 8192  */ [NUMERIC_ROUNDABORT] -- Generates an error when a loss of precision occurs in an expression.
        , CAST(P.[E] AS BIT) /* 16384 */ [XACT_ABORT] -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.*/
    ) AS Flags;

D. 使用 GET_BIT 檢閱 @@OPTIONS 位元遮罩

適用於：SQL Server 2022 (16.x) 和更新版本。

下列範例會使用 GET_BIT 函數，從 @@OPTIONS 中的每個特定位取得值。

SELECT
      GET_BIT(@@OPTIONS, 0)  /* 1     */ AS [DISABLE_DEF_CNST_CHK] -- Controls interim or deferred constraint checking.
    , GET_BIT(@@OPTIONS, 1)  /* 2     */ AS [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.
    , GET_BIT(@@OPTIONS, 2)  /* 4     */ AS [CURSOR_CLOSE_ON_COMMIT] -- Controls behavior of cursors after a commit operation has been performed.
    , GET_BIT(@@OPTIONS, 3)  /* 8     */ AS [ANSI_WARNINGS] -- Controls truncation and NULL in aggregate warnings.
    , GET_BIT(@@OPTIONS, 4)  /* 16    */ AS [ANSI_PADDING] -- Controls padding of fixed-length variables.
    , GET_BIT(@@OPTIONS, 5)  /* 32    */ AS [ANSI_NULLS] -- Controls NULL handling when using equality operators.
    , GET_BIT(@@OPTIONS, 6)  /* 64    */ AS [ARITHABORT] -- Terminates a query when an overflow or divide-by-zero error occurs during query execution.
    , GET_BIT(@@OPTIONS, 7)  /* 128   */ AS [ARITHIGNORE] -- Returns NULL when an overflow or divide-by-zero error occurs during a query.
    , GET_BIT(@@OPTIONS, 8)  /* 256   */ AS [QUOTED_IDENTIFIER] -- Differentiates between single and double quotation marks when evaluating an expression.
    , GET_BIT(@@OPTIONS, 9)  /* 512   */ AS [NOCOUNT] -- Turns off the message returned at the end of each statement that states how many rows were affected.
    , GET_BIT(@@OPTIONS, 10) /* 1024  */ AS [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.
    , GET_BIT(@@OPTIONS, 11) /* 2048  */ AS [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.
    , GET_BIT(@@OPTIONS, 12) /* 4096  */ AS [CONCAT_NULL_YIELDS_NULL] -- Returns NULL when concatenating a NULL value with a string.
    , GET_BIT(@@OPTIONS, 13) /* 8192  */ AS [NUMERIC_ROUNDABORT] -- Generates an error when a loss of precision occurs in an expression.
    , GET_BIT(@@OPTIONS, 14) /* 16384 */ AS [XACT_ABORT] -- Rolls back a transaction if a Transact-SQL statement raises a run-time error.*/
GO

另請參閱