How to View your Current SET Options in SQL Server

SQL Server provides various SET options that control the behavior of our session. These options can impact query execution and results. Also, some features in SQL Server rely on certain SET options to be configured in a certain way (for example indexed views).

Getting the current SET options configuration is not as straightforward as one might think. We need to do a bit of trickery.

Let’s look at how to view our current SET options.

The Raw Data

SQL Server provides us with the @@OPTIONS function that returns information about the current SET options:

SELECT @@OPTIONS;

Output:

Believe it or not, those four digits contain all my SET options and the values they’re currently set to.

The @@OPTIONS function returns an integer. This is a bitmap of the options, converted to a base 10 (decimal) integer. The bit settings are stored in the locations described in the table later in this article.

Anyway, in order to get any meaningful results from this, we need to do some more work.

Return All Options

Here’s a query that returns all SET options and indicates whether each one is ON or OFF:

DECLARE @options INT = @@OPTIONS;

WITH SetOptions AS (
    SELECT 1 AS BitPosition, 'DISABLE_DEF_CNST_CHK' AS OptionName, 'Controls interim constraint checking' AS Description
    UNION ALL SELECT 2, 'IMPLICIT_TRANSACTIONS', 'Controls whether transactions are implicit or explicit'
    UNION ALL SELECT 4, 'CURSOR_CLOSE_ON_COMMIT', 'Controls cursor behavior for transactions'
    UNION ALL SELECT 8, 'ANSI_WARNINGS', 'Controls truncation and NULL in aggregate warnings'
    UNION ALL SELECT 16, 'ANSI_PADDING', 'Controls padding of fixed-length variables'
    UNION ALL SELECT 32, 'ANSI_NULLS', 'Controls NULL handling comparison'
    UNION ALL SELECT 64, 'ARITHABORT', 'Terminates queries when overflow or divide-by-zero occurs'
    UNION ALL SELECT 128, 'ARITHIGNORE', 'Returns NULL when overflow or divide-by-zero occurs'
    UNION ALL SELECT 256, 'QUOTED_IDENTIFIER', 'Controls interpretation of double quotes'
    UNION ALL SELECT 512, 'NOCOUNT', 'Suppresses row count messages'
    UNION ALL SELECT 1024, 'ANSI_NULL_DFLT_ON', 'Controls default NULL behavior for new columns'
    UNION ALL SELECT 2048, 'ANSI_NULL_DFLT_OFF', 'Overrides ANSI_NULL_DFLT_ON'
    UNION ALL SELECT 4096, 'CONCAT_NULL_YIELDS_NULL', 'Controls whether concatenation with NULL yields NULL'
    UNION ALL SELECT 8192, 'NUMERIC_ROUNDABORT', 'Controls error reporting for loss of precision'
    UNION ALL SELECT 16384, 'XACT_ABORT', 'Rolls back transactions if a Transact-SQL statement raises an error'
)
SELECT 
    OptionName AS [SET Option],
    CASE WHEN (@options & BitPosition) = BitPosition THEN 'ON' ELSE 'OFF' END AS Status,
    Description
FROM SetOptions
ORDER BY BitPosition;

Output:

SET Option               Status  Description                                                        
-----------------------  ------  -------------------------------------------------------------------
DISABLE_DEF_CNST_CHK     OFF     Controls interim constraint checking                               
IMPLICIT_TRANSACTIONS    OFF     Controls whether transactions are implicit or explicit             
CURSOR_CLOSE_ON_COMMIT   OFF     Controls cursor behavior for transactions                          
ANSI_WARNINGS            ON      Controls truncation and NULL in aggregate warnings                 
ANSI_PADDING             ON      Controls padding of fixed-length variables                         
ANSI_NULLS               ON      Controls NULL handling comparison                                  
ARITHABORT               OFF     Terminates queries when overflow or divide-by-zero occurs          
ARITHIGNORE              OFF     Returns NULL when overflow or divide-by-zero occurs                
QUOTED_IDENTIFIER        ON      Controls interpretation of double quotes                           
NOCOUNT                  OFF     Suppresses row count messages                                      
ANSI_NULL_DFLT_ON        ON      Controls default NULL behavior for new columns                     
ANSI_NULL_DFLT_OFF       OFF     Overrides ANSI_NULL_DFLT_ON                                        
CONCAT_NULL_YIELDS_NULL  ON      Controls whether concatenation with NULL yields NULL               
NUMERIC_ROUNDABORT       OFF     Controls error reporting for loss of precision                     
XACT_ABORT               OFF     Rolls back transactions if a Transact-SQL statement raises an error

That’s how my system is configured, yours may be different.

This query does the following:

It declares a variable @options and assigns it the value of @@OPTIONS.
It uses a Common Table Expression (CTE) named SetOptions to define all the possible SET options, their corresponding bit positions, and descriptions.
The main SELECT statement then:
- Lists each option name
- Uses a bitwise AND operation to check if each option is set (ON) or not (OFF)
- Includes the description for each option
The results are ordered by the bit position for consistency.

Understanding `@@OPTIONS`

The @@OPTIONS function returns an integer value that represents the current SET options. Each bit in this integer corresponds to a specific SET option. To understand which options are enabled, we need to convert this integer to its binary representation.

For example, if @@OPTIONS returns 5496, the binary representation would be:

0001 0101 0110 1000

Each 1 in this binary string indicates that the corresponding option is ON, while each 0 indicates that the option is OFF.

To interpret this binary value, we need to know which bit corresponds to which option. Here’s a table that lists all options, their corresponding bit value, and descriptions:

Bit	Option	Description
1	DISABLE_DEF_CNST_CHK	Controls interim constraint checking
2	IMPLICIT_TRANSACTIONS	Controls whether transactions are implicit or explicit
4	CURSOR_CLOSE_ON_COMMIT	Controls cursor behavior for transactions
8	ANSI_WARNINGS	Controls truncation and NULL in aggregate warnings
16	ANSI_PADDING	Controls padding of fixed-length variables
32	ANSI_NULLS	Controls NULL handling comparison
64	ARITHABORT	Terminates queries when overflow or divide-by-zero occurs
128	ARITHIGNORE	Returns NULL when overflow or divide-by-zero occurs
256	QUOTED_IDENTIFIER	Controls interpretation of double quotes
512	NOCOUNT	Suppresses row count messages
1024	ANSI_NULL_DFLT_ON	Controls default NULL behavior for new columns
2048	ANSI_NULL_DFLT_OFF	Overrides ANSI_NULL_DFLT_ON
4096	CONCAT_NULL_YIELDS_NULL	Controls whether concatenation with NULL yields NULL
8192	NUMERIC_ROUNDABORT	Controls error reporting for loss of precision
16384	XACT_ABORT	Rolls back transactions if a Transact-SQL statement raises an error

To determine which options are enabled, we can perform a bitwise AND operation between the @@OPTIONS value and the bit value for each option. If the result is non-zero, that option is enabled.

For example, using the default value of 5496:

5496 & 2 = 0 (zero), so IMPLICIT_TRANSACTIONS is OFF
5496 & 4 = 0 (zero), so CURSOR_CLOSE_ON_COMMIT is OFF
5496 & 16 = 16 (non-zero), so ANSI_PADDING is ON
5496 & 32 = 32 (non-zero), so ANSI_NULLS is ON
5496 & 256 = 256 (non-zero), so QUOTED_IDENTIFIER is ON
5496 & 5192 = 5192 (non-zero), so NOCOUNT is ON

This method allows us to programmatically determine which SET options are currently enabled in our SQL Server session.

The Raw Data

Return All Options

Understanding @@OPTIONS

Understanding `@@OPTIONS`