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
- Microsoft’s .Net application’s default Transaction Isolation level is Serializable
- 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
- Set Statements
- @@OPTIONS
- User Options Option