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

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.

    
    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:

  1. You can debug against SQL Server 2005 SP2 or later.
  2. You can specify breakpoint conditions, hit count, filters and actions, and you can edit breakpoint locations.
  3. You can watch T-SQL expressions and see Quick Info tooltips (see Viewing T-SQL Debugger Information).
  4. 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:

    1. 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.
    2. 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.
    3. 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.
    4. 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:

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.

 

Aaron Bertrand

I am a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. I am a long-time Microsoft MVP, write at Simple Talk, SQLPerformance, and MSSQLTips, and have had the honor of speaking at more conferences than I can remember. In non-tech life, I am a father of two, a huge hockey and football fan, and my pronouns are he/him. If I've helped you out, consider thanking me with a coffee. :-)

19 Responses

  1. Ashutosh says:

    Aaron:- A nice,short and straight forward article. Thanks a lot.

  2. Jayaram Krishnaswamy says:

    Here's another to your ugly list:
    The installation goes smoothly and you get great screens heralding your success. This is short lived. As soon as you start your SSMS you get a message that your your Microsoft Visual Studio 2010 SP1 is not complete.
    Well till yesterday it was well and kicking otherwise my Microsoft LightSwitch would yell. I go to Control Panel and I see the malignant sp1 there. So what's the problem?
    It is really frustration that persuaded me to call it malignant. It gave the same problem in installing Microsoft LightSwitch.
    Regarding what you say, it is a full VS family member but I say with a bad case of polygenic mutation.

  3. Matt Whitfield says:

    It's worth noting that actually, both my editor and Apex SQL Edit have had full on snippet support for ages. I would be glad to see the back of the old template style system – snippets are a lot more powerful and fit a lot more naturally into the flow of writing code – whether that's C# or SQL.
    For example, I have a snippet for creating primary keys. I just type in cpk, press tab, enter the table name, tab, enter the column name, press return. Job done…

  4. Ramdas says:

    Hi Aaaron,
    Thank you for sharing the information. You have oraginised the information in a neat manner. The bread crumbs feature is something i use a lot as a developer. Hope MS gets it in in the next release.

  5. AaronBertrand says:

    RichB: different priorities.  If your goal is to have better code completion, I strongly recommend checking out Red Gate's SQL Prompt.  Yes it is not free, but that gives them incentive to continue making it better, IMHO.  I think it overrides the built-in auto complete, so that is one way to disable it (I don't know of a direct way to turn it off permanently).

  6. Tom Winter says:

    The SQL Server community seems to do a great job using Connect to report bugs. I read the IE Blog and people there complain all the time about how horrible Connect is, but I never see anyone actually posting bugs with it.

  7. RichB says:

    Code completion – I guess mainly it is probably due to having a few thousand objects (mostly beginning with get – don't ask) in the databases, and a bit of latency between us and the server.
    The problem gets really acute with cross database queries, once you hit the second . and wait for the thing to load it's info up. Then when it does load up, it has a nasty habit of trying to force you to use something other than what you want – especially if you are aliasing things.  Mostly I just leave it turned off, but haven't quite figured out how to make it stay off cleanly!
    Can't quite figure out how come delphi years ago had suck quick code completion for a massive number of objects and classes, but SQL a decade later is struggling to give useful help.

  8. AaronBertrand says:

    Chris, no, the graphical plan seems to be the same, and in fact most of the demos I've done have used plans generated from Denali's SSMS.  I will certainly blog about any deltas I find.

  9. Chris Wood says:

    Aaron,
    As a user of your companies Plan Explorer, has anything changed with creating/saving/examining Execution Plans in Denali?
    Chris

  10. AaronBertrand says:

    RichB, what kind of issues are you having with code completion?  Have you tried out alternatives, like Mladen's SSMS Tools Pack (free) or Red Gate's SQL Prompt (not free)?

  11. AaronBertrand says:

    paschott,
    No, they haven't fixed the scratch file issue.  I think they're right a lot of people don't want to lose their work inadvertently.  The prompt doesn't really get in my way… I save the files I want to save, and when I close SSMS, I just say no to the prompt, once.  I'm not opening and closing new SSMS instances so often that it really is that troublesome.
    As for Connect, I'm sorry you feel that way.  I find Connect a very valuable place to provide feedback, more so than complaining about things here or on twitter.  At least on Connect they have a good chance to be seen by the developer responsible for the problem (though some of the dev teams there are better than others).  The bigger challenge is that an issue needs to have peer support behind it.  If you cite a problem but nobody else votes on it, it is not likely going to get high priority, because Microsoft can't justify fixing issues that affect one person.  (This can also be a problem if too many file duplicate issues because they didn't search first – they're just diluting the votes.)  So rather than crap on Connect (which I could, believe me), I'd rather encourage people to use it – because that's what will make it better.
    I'm not a big keyboard user; I don't mind clicking around.  I think I gave up long ago on good keyboard navigation in MS UIs.  I don't find myself typing a lot except when I'm actually writing queries or trying to trigger things like Ctrl + Shift + M (guess I won't need to worry about that one until at least the next CTP).
    RichB, I've found a few cases where performance is worse, even making the app unresponsive (e.g. hovering over a query plan that I talk about above), but in general it feels pretty snappy and smooth.  I think having the VS team to also worry about their users will help keep performance a better priority than when it was only SSMS users, as long as it doesn't land us with too much unnecessary baggage (dead weight).  I think it's far too early in the lifecycle to start really judging and analyzing performance.

  12. cinahcaM madA says:

    IMO the debugger will never be useful until it can output the contents of temp tables and table variables (or, ideally, let us query them in an ad hoc fashion during a debugging session). Even better would be the ability to step into a query plan (e.g. set a breakpoint on a certain iterator, see what rows are going through, etc). This would be incredibly helpful but it's a bit of a pipe dream.

  13. RichB says:

    Any improvement in general performance/footprint?  How about the code completion – any better?

  14. Peter Schott says:

    Did they fix the problem with needing to confirm that you really do want to close without saving your scratch work? It's an annoyance, but enough that people mentioned it and it was always "Closed – working as designed" instead of considering what the users actually do with the tool.  (I'm not a big Connect fan because of those types of responses – if someone took the time to actually use Connect, it means they're bugged enough to say something and further investigation is likely warranted.)
    Did they make any improvement to keyboard usability?  One of my big issues w/ VS in general is how much you absolutely need to use the mouse. Trying to use the keyboard to navigate is frustrating beyond words and needing to constantly click, type, click somewhere else, type was really frustrating.  Admittedly my main use for SSMS is to run ad-hoc queries, but trying to admin through the GUI was often painful.
    I will admit that I'm looking forward to the debugging improvements. Debugging TSQL is usually troublesome outside of PRINT and SELECT statements to see what's going on. If it's working well, this will be a good change.

  15. Scott Whigham says:

    Nice – a lot of it, hopefully, seems like typical CTP1 stuff that will get reported/fixed.

  16. AaronBertrand says:

    Pradeep: thanks.
    Sri: what do you mean?

  17. Sri Kumar says:

    Form over substance!

  18. Pradeep Adiga says:

    Aaron,
    You have put together the information very nicely!