THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search
Home Blogs Forums Downloads   Opml View as RSS news feed in XML

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: http://sqlblog.com/blogs/kalen_delaney/archive/2006/09/09/192.aspx )

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

So now you know...

~Kalen

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

Comments

 

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
 

External Links & References (SSQA.net) said:

Cross post from KalenDelaney-SQLBlog content, very interesting!

July 19, 2007 1:56 PM
 

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.

~Kalen

July 19, 2007 10:58 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs
©2006-2010 SQLblog.comTM
Brought to you by Adam Machanic & Peter DeBetta
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement