Systems Engineering and RDBMS

Using @@OPTIONS

Posted by decipherinfosys on January 15, 2009

Before we start looking into the usage of @@OPTIONS, it is important to point out that you should read this MSDN post to see which SET options affect results.  And here is an example of such a scenario happening in your environment.  And another post on the same topic.

So, what does @@OPTIONS do?  It returns the information about the current SET options.  Each user is assigned the default options as configured on the instance to which they are logging on to.  That is controlled by using the sp_configure command and using the parameter “user options”.   If a particular session wants to change the behavior and use a different setting, that session can use the SET command to do so.  By default, it has a value of 32767 in it.  Let’s see what this means.

The value is a bit position value.  This set of SQL (using bitwise AND operator) will show us which options are in effect:

select @@options
go

IF @@OPTIONS & 1     > 0 select ‘Deferred Constraint Checking’
IF @@OPTIONS & 2     > 0 select ‘IMPLICIT_TRANSACTIONS’
IF @@OPTIONS & 4     > 0 select ‘CURSOR_CLOSE_ON_COMMIT’
IF @@OPTIONS & 8     > 0 select ‘ANSI_WARNINGS’
IF @@OPTIONS & 16    > 0 select ‘ANSI_PADDING’
IF @@OPTIONS & 32    > 0 select ‘ANSI_NULLS’
IF @@OPTIONS & 64    > 0 select ‘ARITHABORT’
IF @@OPTIONS & 128   > 0 select ‘ARITHIGNORE’
IF @@OPTIONS & 256   > 0 select ‘QUOTED_IDENTIFIER’
IF @@OPTIONS & 512   > 0 select ‘NOCOUNT’
IF @@OPTIONS & 1024  > 0 select ‘ANSI_NULL_DFLT_ON’
IF @@OPTIONS & 2048  > 0 select ‘ANSI_NULL_DFLT_OFF’
IF @@OPTIONS & 4096  > 0 select ‘CONCAT_NULL_YIELDS_NULL’
IF @@OPTIONS & 8192  > 0 select ‘NUMERIC_ROUNDABORT’
IF @@OPTIONS & 16384 > 0 select ‘XACT_ABORT’
go

Now, if you look into the system table sys.syscacheobjects, you will see a column called setopts there.  This table contains information on how the cache is used and setopts contains the bit position value.  Let’s see how this value changes for a session:

select @@options
go
set nocount on
go
select @@options

The first output will give you 5496 and the second one will give 6008 assuming all default values are in place in your environment.  The “SET NOCOUNT ON” has a bit value of 512 so if we add 5496 + 512, that gives us 6008 which is what we get in the second run.  Using this @@OPTIONS function, you can know what settings users have in their session and determine whether you need to SET certain options on or off for the session.  If you want, you can even encapsulate all of the above logic in a UDF and then use it instead of firing off the code for all of those value set comparisons.

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 74 other followers

%d bloggers like this: