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:
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’
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:
set nocount on
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.