Inspect server and session User Options in effect

SQL Server can be configured to provide certain behavior to client sessions, via the SET OPTIONS= command, or via the sys.sp_configure 'user options' system stored procedure. The SET OPTIONS= command only affects the current session, whereas the sys.sp_configure 'user options' system stored procedure configures the default values for all future user sessions.

The options that can be set consist of:

Value Name Description
1 DISABLE_DEF_CNST_CHK Controls interim or deferred constraint checking.
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.
4 CURSOR_CLOSE_ON_COMMIT Controls behavior of cursors after a commit operation has been performed.
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 when using equality operators.
64 ARITHABORT Terminates a query when an overflow or divide-by-zero error occurs during query execution.
128 ARITHIGNORE Returns NULL when an overflow or divide-by-zero error occurs during a query.
256 QUOTED_IDENTIFIER Differentiates between single and double quotation marks when evaluating an expression.
512 NOCOUNT Turns off the message returned at the end of each statement that states how many rows were affected.
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.
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.
4096 CONCAT_NULL_YIELDS_NULL Returns NULL when concatenating a NULL value with a string.
8192 NUMERIC_ROUNDABORT Generates an error when a loss of precision occurs in an expression.
16384 XACT_ABORT Rolls back a transaction if a Transact-SQL statement raises a run-time error.

The following code can be used to see a table listing the user options in effect for both the server, and the session. Session options take priority over server sessions.

IF COALESCE(OBJECT_ID('tempdb..#UserOptionValues'), 0) = 0
CREATE TABLE #UserOptionValues
(
    UserOptionVal INT NOT NULL
        PRIMARY KEY CLUSTERED
    , UserOptionName VARCHAR(23)
    , UserOptionDesc VARCHAR(200)
);

DECLARE @OptionVal INT;
DECLARE @SessionVal INT;

SELECT @OptionVal = CONVERT(INT, c.value)
FROM sys.configurations c
WHERE c.name = 'user options';

--SET @OptionVal = 6008; --specific value you're curious about
SET @SessionVal = @@OPTIONS; --current client settings

TRUNCATE TABLE #UserOptionValues;

INSERT INTO #UserOptionValues (UserOptionVal, UserOptionName, UserOptionDesc)
VALUES 
  (1,       'DISABLE_DEF_CNST_CHK',     'Controls interim or deferred constraint checking.')
, (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.')
, (4,       'CURSOR_CLOSE_ON_COMMIT',   'Controls behavior of cursors after a commit operation has been performed.')
, (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 when using equality operators.')
, (64,      'ARITHABORT',               'Terminates a query when an overflow or divide-by-zero error occurs during query execution.')
, (128,     'ARITHIGNORE',              'Returns NULL when an overflow or divide-by-zero error occurs during a query.')
, (256,     'QUOTED_IDENTIFIER',        'Differentiates between single and double quotation marks when evaluating an expression.')
, (512,     'NOCOUNT',                  'Turns off the message returned at the end of each statement that states how many rows were affected.')
, (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.')
, (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.')
, (4096,    'CONCAT_NULL_YIELDS_NULL',  'Returns NULL when concatenating a NULL value with a string.')
, (8192,    'NUMERIC_ROUNDABORT',       'Generates an error when a loss of precision occurs in an expression.')
, (16384,   'XACT_ABORT',               'Rolls back a transaction if a Transact-SQL statement raises a run-time error.')

SELECT Server = @OptionVal, Session = @SessionVal;

SELECT uo.UserOptionName
    , uo.UserOptionDesc
    , ServerDefault = CASE WHEN (uo.UserOptionVal & @OptionVal) = uo.UserOptionVal THEN 'X' ELSE '' END 
    , [On In Session] = CASE WHEN (uo.UserOptionVal & @SessionVal) = uo.UserOptionVal THEN 'X' ELSE '' END 
FROM #UserOptionValues uo
ORDER BY uo.UserOptionVal;

Output looks similar to this:

Also of note, SQL Server Management Studio defines the session options automatically, by default so the following are set “ON”:

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

See Microsoft’s Docs Site for details about the T-SQL @@OPTIONS statement.