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.