Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Determining SET Options for a Current Session in SQL Server


By:   |   Read Comments (3)   |   Related Tips: More > T-SQL

Attend this free live MSSQLTips webcast

Tips and Tricks to Growing Your SQL Server Estate from SMB to Enterprise Scale
Thursday, June 28, 2018 - click here to learn more


Problem
With each session that is made to SQL Server the user can configure the options that are SET for that session and therefore affect the outcome of how queries are executed and the underlying behavior of SQL Server.  Some of these options can be made via the GUI, while others need to be made by using the SET command.  When using the GUI it is easy to see which options are on and which options are off, but how can you get a better handle on all the options that are currently set for the current session?

Solution
SQL Server offers many built in metadata functions and one of these functions, @@OPTIONS which allows you to get the current values that are set for the current session.  When each connection is made the default values are established for each connection and remain set unless they are overridden by some other process.

The below list shows the options and the descriptions for each of these settings.

Value Configuration 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.

(Source: SQL Server 2005 Books Online)

In a previous tip, Reproducing Query Execution Plan Performance Problems the author talked about how SET options could impact the outcome of queries and performance issues, so this command could become useful to determine what the current options are for the session.

To get the settings for the current session you can run this simple SELECT statement:

SELECT @@OPTIONS

When run, this command returns an integer that represents the bit values as shown in the table above.

To help make further sense of these values you can run the following bitwise code that will show you what SET options are turned on.

DECLARE @options INT

SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

When the above code is run for my session, this is the output:

The first value 5496 displays the current @@OPTIONS value and the following lines signify which options are currently set for the session.

To illustrate how this value changes we are going to run SET NOCOUNT ON which should turn on bit value 512.  If we add 5496 and 512 the new value should be 6008.

SET NOCOUNT ON

DECLARE @options INT

SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

When the above code is run, this is the output with NOCOUNT now on:

Here is one more example where we have set NOCOUNT ON and QUOTED_IDENTIFIER OFF.

SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF

DECLARE @options INT

SELECT @options = @@OPTIONS

PRINT @options
IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK'
IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS'
IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT'
IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS'
IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING'
IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS'
IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT'
IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'
IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER'
IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT'
IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON'
IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF'
IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL'
IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT'
IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

When the above code is run, this is the output we get

 

Summary
This is a simple function that can give you a lot of insight into your current session settings.  By using this function, you can determine what settings users have set for their session and then determine if you need to turn certain options on or off to ensure your code acts as desired.

Next Steps



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 19, 2013 - 11:03:26 AM - Chris Henson Back To Top

Refactored in query format:

select 'DISABLE_DEF_CNST_CHK', case when (1 & @@options) = 1 then 1 else 0 end

union

select 'IMPLICIT_TRANSACTIONS', case when (2 & @@options) = 2 then 1 else 0 end

union

select 'CURSOR_CLOSE_ON_COMMIT', case when (4 & @@options) = 4 then 1 else 0 end

union

select 'ANSI_WARNINGS', case when (8 & @@options) = 8 then 1 else 0 end

union

select 'ANSI_PADDING', case when (16 & @@options) = 16 then 1 else 0 end

union

select 'ANSI_NULLS', case when (32 & @@options) = 32 then 1 else 0 end

union

select 'ARITHABORT', case when (64 & @@options) = 64 then 1 else 0 end

union

select 'ARITHIGNORE', case when (128 & @@options) = 128 then 1 else 0 end

union

select 'QUOTED_IDENTIFIER', case when (256 & @@options) = 256 then 1 else 0 end

union

select 'NOCOUNT', case when (512 & @@options) = 512 then 1 else 0 end

union

select 'ANSI_NULL_DFLT_ON', case when (1024 & @@options) = 1024 then 1 else 0 end

union

select 'ANSI_NULL_DFLT_OFF', case when (2048 & @@options) = 2048 then 1 else 0 end

union

select 'CONCAT_NULL_YIELDS_NULL', case when (4096 & @@options) = 4096 then 1 else 0 end

union

select 'NUMERIC_ROUNDABORT', case when (8192 & @@options) = 8192 then 1 else 0 end

union

select 'XACT_ABORT', case when (16384 & @@options) = 16384 then 1 else 0 end;


Monday, September 24, 2012 - 7:53:34 PM - Michael Freidgeim Back To Top

To see database settings run

select * From sys.databases where name = 'MyDB'
The view has columns
is_ANSI_NULLS_ON       , is_ANSI_PADDING_ON ,is_NUMERIC_ROUNDABORT_ON etc

 


Thursday, December 10, 2009 - 9:25:58 AM - aprato Back To Top

There's also a DBCC command that lists the enabled options for a connection

dbcc useroptions

If an option is set, it will be in the output.


Learn more about SQL Server tools