In SQL Server and Azure SQL, each session has a set of options, which affect the behavior of the session. For example, the NOCOUNT option determines whether you will receive a message at the end of each statement that states how many rows were affected.
These options are scoped to the session. Each session has its own set of values for these options, but all sessions derive the default values for these options from the "user options" instance configuration. But don't be surprised if you see a default value for one of the options, and then when you open a session, the value for that option is different. This is because most drivers and client tools automatically modify some of the set options when establishing the connection.
You can view the current values of the various options for your session using the @@OPTIONS function, which returns a bitmap of the options, converted to a base-10 (decimal) integer. Each bit represents a single option, which can be either on or off.
You can modify a specific option for the current session using the SET command with the appropriate option. For example, to set the value of XACT_ABORT, which affects the behavior of transactions in case of run-time errors, you can execute the following statement:
SET XACT_ABORT ON/OFF;
Since options affect the behavior of the session in all kinds of ways, it's important to know the value of each option for the current session when you debug or troubleshoot something. And since the @@OPTIONS function returns a bitmap, it is not straightforward. So to make your (and also my) life easier, I wrote a simple script that analyzes the @@OPTIONS value and displays a table with all the options and their current values. I even added a description to each option, because nobody remembers what ARITHIGNORE does. :)
You can access the script here.
Comments