Edit

@@OPTIONS (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

Returns information about the current SET options.

Transact-SQL syntax conventions

Syntax

@@OPTIONS

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

Return type

integer

Remarks

The options can come from use of the SET command or from the sp_configure user options value. Session values configured with the SET command override the sp_configure options. Many tools, such as Management Studio, automatically configure set options. Each user has an @@OPTIONS function that represents the configuration.

You can change the language and query-processing options for a specific user session by using the SET statement. @@OPTIONS can only detect the options that are set to ON or OFF.

The @@OPTIONS function returns a bitmap of the options, converted to a base 10 (decimal) integer. The bit settings are stored in the locations described in a table in the article Configure the user options Server Configuration Option.

To decode the @@OPTIONS value, convert the integer returned by @@OPTIONS to binary, and then look up the values on the table at Configure the user options Server Configuration Option. For example, if SELECT @@OPTIONS; returns the value 5496, use the Windows programmer calculator (calc.exe) to convert decimal 5496 to binary. The result is 1010101111000. The rightmost characters (binary 1, 2, and 4) are 0, indicating that the first three items in the table are off. Consulting the table, you see that those are DISABLE_DEF_CNST_CHK, IMPLICIT_TRANSACTIONS, and CURSOR_CLOSE_ON_COMMIT. The next item (ANSI_WARNINGS in the 1000 position) is on. Continue working left through the bit map, and down in the list of options. When the left-most options are 0, they are truncated by the type conversion. The bit map 1010101111000 is actually 001010101111000 to represent all 15 options.

Example C provides a query that automatically maps the @@OPTIONS bitmask to user options.

Examples

A. Demonstration of how changes affect behavior

The following example demonstrates the difference in concatenation behavior with two different setting of the CONCAT_NULL_YIELDS_NULL option.

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. Test a client NOCOUNT setting

The following example sets NOCOUNT``ON and then tests the value of @@OPTIONS. The NOCOUNT``ON option prevents the message about the number of rows affected from being sent back to the requesting client for every statement in a session. The value of @@OPTIONS is set to 512 (0x0200). This represents the NOCOUNT option. This example tests whether the NOCOUNT option is enabled on the client. For example, it can help track performance differences on a client.

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

C. Review @@OPTIONS bitmask with a PIVOT query

The following example uses table-valued constructors to generate a numbers-list reference and then compares the value of @@OPTIONS with a bitwise operator. An APPLY clause performs string concatenation to generate a character bitmask, and another generates aliases to review against the documented values from Configure the user options Server Configuration Option.

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. Review @@OPTIONS bitmask with GET_BIT

Applies to: SQL Server 2022 (16.x) and later versions.

The following example uses the GET_BIT function to get the value from each specific bit in @@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

See also