Background

Set options can have devastating effect on SQL Server Operations.  I am too shamed to talk about once it bit me terribly.

Other times, setting one or a couple of them helped us.

Setting XACT_ABORT on is very important when changing data within Stored Procedure.

Setting TRANSACTION ISOLATION LEVEL  to “read uncommitted” could possibly help address data contention issues.

Set NO_EXEC ON is helpful when writing DDL packages and we need to skip existing packages.

Code

Declare Helper Objects

Views

dbmgmt.vw_UserOptions

Guide

Based on Microsoft’s documentation, here are the user options.

use [master]
go

if schema_id('dbmgt') is null
begin

	exec('create schema [dbmgt] authorization [dbo]; ')
	
end
go

if object_id('[dbmgt].[vw_UserOptions]') is null
begin

	exec('create view [dbmgt].[vw_UserOptions] as select [shell] = 1/0 ')

end
go

alter  view [dbmgt].[vw_UserOptions]
as

select
	  DISABLE_DEF_CNST_CHK    = 1
	, IMPLICIT_TRANSACTIONS   = 2
	, CURSOR_CLOSE_ON_COMMIT  = 4
	, ANSI_WARNINGS		  = 8
	, ANSI_PADDING		  = 16
	, ANSI_NULLS		  = 32
	, ARITHABORT		  = 64
	, ARITHIGNORE		  = 128
	, QUOTED_IDENTIFIER	  = 256
	, NOCOUNT		  = 512
	, ANSI_NULL_DFLT_ON	  = 1024
	, ANSI_NULL_DFLT_OFF	  = 2048
	, CONCAT_NULL_YIELDS_NULL = 4096
	, NUMERIC_ROUNDABORT	  = 8192
	, XACT_ABORT		  = 16384

go

Query

List information on current user sessions

Explanation

Query sys.dm_exec_sessions to get session setting for all existing connections.

Code

declare @mycomputer sysname
declare @sessionID  int

set @mycomputer = HOST_NAME()
--set @sessionID = @@SPID

select 

		  tblES.session_id
	    , tblES.login_name
		, tblES.host_name
		, tblES.program_name
		, tblES.prev_error

		/*
			Transaction isolation level of the session.
			0 = Unspecified
			1 = ReadUncomitted
			2 = ReadCommitted
			3 = Repeatable
			4 = Serializable
			5 = Snapshot
		*/
		, [transactionIsolationLevelLiteral]
			= case tblES.transaction_isolation_level
					when 0 then 'Unspecified'
					when 1 then 'ReadUncomitted'
					when 2 then 'ReadCommitted'
					when 3 then 'Repeatable'
					when 4 then 'Serializable'
					when 5 then 'Snapshot'
					else cast(tblES.transaction_isolation_level as sysname)
			  end	

		, [sessionStatus]
			= tblES.[status]

		, [language]
			= tblES.[language]

		, [Date Format]
			= tblES.[date_format]		

		, [Ansi Defaults]
			= case tblES.ansi_defaults
					when 0 then 'No'
					else 'Yes'
			  end

		, [quoted Identifier]
			= case tblES.quoted_identifier
					when 0 then 'No'
					else 'Yes'
			  end

	

from   sys.dm_exec_connections tblSC

inner join sys.dm_exec_sessions tblES

			on tblSC.session_id = tblES.session_id

where tblES.[is_user_process] = 1

and   tblES.host_name
		 = isNull(@mycomputer, tblES.host_name)

and   tblES.session_id 
		= isNull(@sessionID, tblES.session_id )

Output

Explanation

1. Microsoft’s .Net application’s default Transaction Isolation level is Serializable
2. SQL Server Agent connections have quoted identifier set to off

Query @@OPTIONS

Explanation

In the sample below, we query @@OPTIONS to get the current session’s settings.

We then issue a bitwise and (&) to see if we achieve the specific options value.

Code

declare @option int

set @option = @@options

select 
		  [@@optionsAsDecimal] = @option

		, [quotedIdentifier] 
				= case @option & vwUO.QUOTED_IDENTIFIER
						when vwUO.QUOTED_IDENTIFIER then 'Yes'
						else 'No'
				  end	

		, [nocount] 
				= case @option & vwUO.[nocount]
						when vwUO.[nocount] then 'Yes'
						else 'No'
				  end	


		, [xact_abort] 
				= case @option & vwUO.XACT_ABORT
						when vwUO.XACT_ABORT then 'Yes'
						else 'No'
				  end	

		, [arith_abort] 
				= case @option & vwUO.ARITHABORT
						when vwUO.ARITHABORT then 'Yes'
						else 'No'
				  end	

from  [master].[dbmgt].[vw_UserOptions] vwUO

Output:

References

1. Set Statements
2. @@OPTIONS
   • @@OPTIONS
3. User Options Option
   • User Options Option