SQL Server – Know your query SET options – Properties window and DBCC USEROPTIONS


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
  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.

ANSI_NULLS OFF   ANSI_NULLS ON  
Set option Value Set option Value

textsize

2147483647

textsize

2147483647

language

us_english

language

us_english

dateformat

mdy

dateformat

mdy

datefirst

7

datefirst

7

lock_timeout

-1

lock_timeout

-1

quoted_identifier

SET

quoted_identifier

SET

arithabort

SET

arithabort

SET

ansi_null_dflt_on

SET

ansi_null_dflt_on

SET

ansi_warnings

SET

ansi_warnings

SET

ansi_padding

SET

ansi_padding

SET

  ansi_nulls SET

concat_null_yields_null

SET

concat_null_yields_null

SET

isolation level

read committed

isolation level

read committed

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!

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