THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? Nobody upgraded the SET OPTIONS screen in SQL Server Management Studio


In SQL Server Management Studio, the screen where you choose which SET options to enable for all connections appears to be left over from SQL Server 2000. I know there are a lot of different dialog boxes available through the Tools | Options menu, but I find it amazing that this one was completely overlooked when updating the old Enterprise Manager GUI.

Here's how to get to the dialog:

Tools | Options

Then expand Query Execution to SQL Server and then to Advanced...

The list of the set options to enable does not include anything new in SQL Server 2005.

1. It does not include SET SHOWPLAN_XML or SET STATISTICS XML (but includes SHOWPLAN_TEXT)

2. Transaction Isolation Level does not include SNAPSHOT

3. Deadlock Priority only allows NORMAL and LOW, not the numbers -10 to 10, or the value HIGH
   (see my earlier post: )

Maybe there are others that I haven't noticed yet.

So now you know...


Published Tuesday, July 17, 2007 4:16 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



James Luetkehoelter said:

Nice to point out Kalen - missing Snapshot in there is one that really infuriates me. I encourage those that aren't strong in TSQL (ahem, more than I'd like to admit to myself) to make the change in SSMS, script it out, then run it, then save the script in a folder with the current date in the name - instant change history! However, I can't do that here (or in some other places).

One unchanged piece in SSMS that drives me crazy is the backup dialog box. It still has that open text window at the bottom where you can add multiple disk devices. I can't count the number of times when I've been working with a client during a restore and realized that they had 5-10 files in there, not realizing that it was stripping to each one (since you could high-light a single file). ARGH!

July 18, 2007 5:34 AM

Brennan Gordon said:

I have been working with SQL 2000 and 2005 for quite a while now and have never really bothered about these settings. I tend to just leave things as default.

Am I horribly ignorant or just typical? Is there some amazing things that I am missing by not taking advantage of these client-side settings?

July 19, 2007 3:07 AM

Kalen Delaney said:

Hi Brennan

I think there are probably many people who never have a need to adjust any of these, and in most cases you will be better off if you leave these settings at their default. I think it's just curious that since they have the option of setting session options in a dialog box, they should have the options reflect what's possible in the version of the product that the tool is supposed to go with.


July 19, 2007 10:58 PM

king said:



January 16, 2018 11:36 PM

aaaa said:

April 1, 2018 5:23 PM

cara meningkatkan nafsu makan said:

August 3, 2018 11:52 PM

obat bronkitis anak secara alami said:

August 24, 2018 7:19 PM

obat benjolan said:

September 4, 2018 12:43 AM

obat wasir said:

September 4, 2018 8:57 PM

obat maag said:

September 18, 2018 7:41 PM

chenjinyan said:



October 9, 2018 6:48 PM

chenqiuying said:


October 10, 2018 6:46 PM

Leave a Comment


This Blog


Favorite Non-technical Sites or Blogs

Privacy Statement