The properties and behaviour of each session to a SQL Server instance can be handled individually via use of various SET options. As we multi-task during the day, we tend to forget the exact SET options that we choose for a particular user session. The focus of the day is how to retrieve this information programmatically, i.e. through T-SQL.

Retrieving SET options for a given session

To quickly review the SET options for a given session, we have two methods:

1. Properties Window
   • The Properties window is available if we are in a SSMS session – this method is not applicable when attempting to fetch the SET options via code
   • I described this method in my previous post, August 01, 2011 (http://beyondrelational.com/blogs/nakul/archive/2011/08/01/ssms-properties-window-know-your-query-options-ansi-nulls-quoted-identifier-arithabort-and-other-set-options.aspx)
2. Using DBCC commands
   • For those would be interested to get these SET options via code, the DBCC comes to our rescue
   • Let’s take a quick look at this method to get the user options

Demo

Using the Properties window to get the user options involves executing a query workload and retrieving the actual execution plan. DBCC, on the other hand allows us to fetch the user options without any workload and without retrieving the execution plan.

For this demonstration, let’s make a small change to the default user options, and run the DBCC statement in the query below.

--Change the connection option
SET ANSI_NULLS OFF

--Retrieve the user options for this connection
DBCC USEROPTIONS
GO

Now, let’s roll back the change (or launch a new session by creating a new query editor window), and run the following statement.

--Change the connection option
SET ANSI_NULLS ON

--Retrieve the user options for this connection
DBCC USEROPTIONS
GO

Comparing the outputs:

Comparing the output from the two sessions, we can see that the user options that are turned OFF do not appear on the list.

Reference

Books On line page for DBCC USEROPTIONS – http://msdn.microsoft.com/en-us/library/ms180065.aspx

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!