SQL Server v.Next (Denali) : The New SSMS

AaronBertrand — Tue, 23 Nov 2010 14:35:00 GMT

In SQL Server Denali, Management Studio has been entirely migrated from its earlier incarnation. Previously SSMS had slight traces here and there that it was a distant cousin to Visual Studio; in this release, it is a full-fledged immediate family member. There are some great things that come of this, but there are some negative aspects as well. I thought I would give a quick run-down of my observations so far.

The Good

Multi-Monitor Support

Finally, we seem to have reached an audience on the dev team that some of us really develop and administer with more than one monitor, and that this is not just important to developers. Based on the Visual Studio 2010 feature, you can now just grab any tab out of the tab strip and drag it off onto a different area of the screen; and putting it back is just as easy:

As someone who is used to working on multiple large monitors, I applaud this feature, and I hope it is useful for you, too. The only sad part is that it doesn't demo very well on a laptop, so it will be very hard to show audiences how powerful this can be.

Consolas

This probably doesn't affect many people, but I absolutely love the fact that the default font in the query editor has been changed to Consolas. I blogged about my preferences last year, including how to change 2008 / 2008 R2 SSMS to use Consolas instead of the default Courier New. Clearly that advice will now be obsolete.

Snippets

A very popular feature previously only found in Mladen Prajdi ć's SSMS Tools Pack, a free add-in for Management Studio, this now-native feature allows you to use snippets for reusable code, without having to discover or understand the templates folder. You can create your own snippets, but there is a good variety of built-in samples already (though the template ~~explorer~~ ~~browser~~ [which is it?] still exposes more). You can insert a snippet by the keystrokes Ctrl + K then Ctrl + X, the Edit > IntelliSense > Insert Snippet menu, or by right-clicking in a query window and choosing "Insert Snippet..."

Sadly, they don't use the template placeholders in the code - you know, the ones where you could hit Ctrl + Shift + M and tab through to replace values (though the values you need to replace are highlighted.) Here is an example in an earlier version of SSMS (however this functionality is now broken in Denali - more on this later):

There is also a handy shortcut for "surround with" snippets, allowing you to quickly wrap a statement or set of statements with a BEGIN, IF or WHILE block (I'm curious why TRY and CATCH are absent):

However, the behavior is a little funky. For example, if you don't make your choice quickly enough (or inadvertently click within the big yellow box, next to "With:"), the drop-down disappears, leaving you wondering what to do next. Since there is a blinking cursor there, I tried to type "Begin" to get AutoComplete, but it didn't appear to do anything:

Now I was stuck with this weird artifact on my screen. To try to get it to go away, I hit Enter, and while that worked, I also got a pretty ugly error message:

So, the Snippets feature has the potential to be pretty powerful, but it is missing some fit & finish at this early stage.

Debugging Improvements

I'll be honest; I'm an old-school debugger. I do not come from a heavy object-oriented background, so PRINT, RAISERROR, and TRY/CATCH have been sufficient for me to date. So, I imagine some of these improvements will be more exciting for you than they could ever be for me. Still, I think folding into Visual Studio is going to provide some powerful options for debugging. In this release, they have made the following enhancements:

You can debug against SQL Server 2005 SP2 or later.
You can specify breakpoint conditions, hit count, filters and actions, and you can edit breakpoint locations.
You can watch T-SQL expressions and see Quick Info tooltips (see Viewing T-SQL Debugger Information).
IntelliSense now supports breakpoint validation.

Personally, I'll continue to rip the Debug icon off my toolbar, but hopefully this enhances the development experience for some of you.

Add-In Support

Bill Ramos (blog | twitter) has hinted to us several times that stronger add-in support is coming. Currently several vendors, as well as individuals like Mladen Prajdić (blog | twitter) and Jonathan Kehayias (blog | twitter), have had to pull teeth in order to get their code working within the SSMS environment. I hope that Bill's hype becomes a reality.

Extended Events

Extended Events finally gets some UI support:

But, at least in my limited exposure, I think this handles just the very basics -- once Jonathan's Extended Events Manager works as a Denali SSMS plug-in, it may still prove to be quite useful.

The Bad

Missing Keyboard Functionality

One of the things we've lost in the conversion to the Visual Studio shell is our ability to continue using several keyboard shortcuts. For example:

Ctrl + Shift + U still converts selected text to upper case, but Ctrl + Shift + L no longer handles lower case. That is now handled by a much less intuitive keystroke combination: Ctrl + U. Not a big deal, but re-learning these things can be painful.
Ctrl + Shift + M no longer raises the template parameters dialog. I've complained about this in Connect #623863. Please vote if you want this functionality back by default - though I'm sure there is a cumbersome way to customize this.
A very simple keyboard operation I've been used to performing in Management Studio is, in a query editor, holding the down arrow key to get to the end of the last line in a query window. In the Visual Studio shell, holding down gets you to the last line, but it doesn't move the cursor to the last character on that line. Not a huge deal, just mildly annoying until you get used to it.
We can no longer use keyboard layout schemes from previous versions of SQL Server. While it looks like the keyboard mapping will be more flexible (and maybe more schemes will be added later), it certainly feels less functional out of the box:

Crippled Toolbar Functionality

Another thing I typically do when I set up a new SSMS installation is move some elements of the toolbar around, and get rid of some buttons I never use (like the Debug icon). In 2005, 2008 and 2008 R2, you could right-click the toolbar, choose Customize, and then - with a seemingly modal dialog open - drag items to different areas of the toolbar, or off the toolbar altogether. One of the first things I discovered when I installed Denali was that the Visual Studio powered SSMS no longer supports this functionality. You can still do it, it's just a lot more cumbersome; see the workaround I posted in Connect #615154.

The Ugly

Horrible Color Coding Defaults

Don't get me wrong; color coding is a great thing. But man, did they pick the wrong fluorescent puke green (yes, that is an official Crayola color name) for system objects. If this doesn't give you a headache (especially the selected text), I don't know what will:

You can fix this by going to Tools | Options, Environment, Fonts and Colors, Text Editor, SQL System Table, then choose a new color for Item foreground:

The item says "SQL System Table" but this will affect all relevant built-ins, such as catalog views, DMVs and dynamic management functions.

Weird Hover Behavior

I am able to reproduce this one at will. I'll spare you all of the gory details unless you want to check out Connect #615766. Essentially, if you turn on Execution Plan and have a lengthy enough query (such as the query from Adam Machanic's sp_WhoIsActive), when you mouse over the query text above the plan diagram, SSMS becomes completely unresponsive as it tries to display the tooltip. It can lock up for 20 to 30 seconds, and then when it finally responds, the tooltip disappears without uselessly showing the query anyway. The drawing of the tooltip seems to be modal, so even if the UI wasn't responding, there isn't much you can do except wait. This behavior is simply not cool, and since I reproduced it *in person* for one of the program managers while at PASS earlier this month, I suspect it will be addressed before RTM. If you want more help reproducing this issue, please let me know.

Messy Scripting

Scripting from Management Studio has not improved very much over the years, and the team seems incapable of learning from past mistakes. As an example, Erland Sommarskog and I combined to find three pretty serious shortcomings in the script generated by the "New Service Broker Application..." wizard:

#622784 : [Denali SSMS] : Proliferation of the use of deprecated system tables (e.g. sysobjects)
#623047 : Incorrect SET options for New Service Broker Application
#623049 : master misspelled in new Service Broker tasks

Erland is absolutely right; they need to test their scripts in a case-sensitive collation (and maybe even in something like Turkish). In fact I think a lot of us would benefit from always developing in a case-sensitive collation.

Barely Handled F1 Exception

Depending on your cursor location in a query editor window (e.g. when it is not next to a keyword), trying to get to help by pressing F1 can lead to this error:

This issue was reported in Connect #623498.

The Bread Crumbs Don't Work

Because of the transition to Visual Studio, they've had to re-do a lot of the plumbing surrounding error handling and debugging. One thing that doesn't work in the current CTP is double-clicking on an error message which, in previous versions, jumps up to the actual statement that generated the error. This has been reported in Connect #623735. Also, when debugging, the line number reported in the error message is inexplicably off by 1.

There is this thing called the Error List, but I find the behavior quite confusing. The list seems to be updated when IntelliSense discovers new problems with your query as you're typing, but then doesn't update when you execute. It also seems to forget which window it is associated with, so you'll be in a query window and the error list will be talking about code that is in some other window in your tab stack. It seems, so far, to be useful only for debugging when you have a single tab open.

Search results matching tags 'Visual Studio' and 'SQL Server 11'

SQL Server v.Next (Denali) : The New SSMS