#0328 – SQL Server – Configuration Values for User Options


Earlier this week, I wrote a post on ANSI_NULL_DFLT_ON (Impact of ANSI_NULL_DFLT_ON on Temporary Tables). In this post, I used code similar to the following to ascertain whether the ANSI_NULL_DFLT_ON setting is set to ON as a user option (via the connection properties) or not.

IF (@@OPTIONS & 1024) > 0
    PRINT 'ANSI_NULL_DFLT_ON is SET';
ELSE
    PRINT 'ANSI_NULL_DFLT_ON is OFF';
GO

When I was originally introduced to the concept of the @@OPTIONS configuration function, the one question that came into my mind was:

How do I know what configuration value stands for which SET option?

I’m sure that many of you who read the posts from last week would also have had the same question.

The unique values for all user configuration options are documented in the following Books On Line/TechNet page: Configure the user options Server Configuration Option.

It is interesting to note that ultimately they correspond to the bit positions corresponding to a particular option (which is why we can do a bit-wise AND with the final value returned by @@OPTIONS to verify whether a particular option is enabled or not) – another example of how optimized the operation of SQL Server is by default.

Further reading:

  • The @@OPTIONS configuration function [Link]
  • Configure the user options Server Configuration option [Link]

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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