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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
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.