Home > SQL Server > Get the current value of SET options using @@OPTIONS – SQL Server

Get the current value of SET options using @@OPTIONS – SQL Server


@@Options function is used to get the current information about the SET options of the current session.

It returns a integer value. It contains 15 SET option values. Each option value is stored in each bit of the integer value.

SET Options with bit representation:

pic1Implementation

1. Get the current option value

pic7Binary equivalent for  5496 is 1010101111000.

2. This script will print the current value of the SET options for the current session.

options

Download Set Options SQL script File. [This code is contributed by Stefan Boumans]

These SET options are the user options server configuration option. You can find these SET options in the Default connection options of the SQL Server. Administrator can set this option as global defaults configuration for all users. So, this default settings will apply automatically when the user connect or create new session to the SQL Server.

pic3We can override this default settings using SET statement for individual session. But, We can also re-configure these user options using sp_configure ‘user options’.

Example

1. Overriding User option with SET statment

pic42. Turn ON multiple SET User options in one statement

pic5After executing this statement, server configuration will looks like.

pic6

Advertisements
  1. June 7, 2016 at 11:54 pm

    thanks , you did me a great favor.

  1. March 25, 2013 at 2:59 pm
  2. June 10, 2013 at 10:07 pm

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

%d bloggers like this: