THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a data mangler in London working for Dunnhumby

SSMS in SQL Server Denali – “Insert snippet” and “Surround with”

SQL Server Denali CTP1 has hit the streets! I’ve been lucky enough to have been playing with it for a couple of weeks (thank you to those responsible – you know who you are) and that means I can now start talking publicly about some of the cool stuff I’ve learnt about this next version of SQL Server. In this blog post I’m quickly going to cover two new productivity enhancements in SQL Server Management Studio (SSMS):

  • Surround With
  • Insert Snippet

They’re very similar to each other so it makes sense to cover them both together.


Surround With

First “Surround With”. This feature enables you to edit some existing code to wrap it with the scaffolding for common coding constructs like BEGIN…END, IF () BEGIN…END and WHILE () BEGIN…END. A picture speaks a thousand words so rather than tell you I’ll just show you instead.

Take the following code snippet:


Its a perfectly valid stored procedure definition although I happen to be one of those guys who likes to have BEGIN and END statements marking out the stored procedure body – Surround With can help me with that.

First I select the code that I want to “surround”:


Right-click and select “Surround With” (note that there is a keyboard shortcut too):


That brings up the surround with submenu where I browse to the option that I want, in this case its “BEGIN…END”


Hit Enter and voila…I’ve surrounded my code with a BEGIN…END block:


That’s it!  A small new feature but a pretty useful one I hope you’ll agree. BEGIN…END is a fairly noddy use case but it gets more useful for the slightly more verbose IF() and WHILE() constructs.


Insert Snippet

This is probably the more useful of the two features I’m talking about here. “Insert Snippet” is similar to “Surround With” though I suspect you’ll be using it a lot more, in short what it does is create the skeleton DDL code for a new database object. All you need to do is right-click in a query window (or hit the keyboard shortcut CTRL+K, CTRKLX) and you’re presented with a list of code templates for various object types.




All you need to do is browse to the one you want, hit Enter, and you’re straight into the important business of development. Nice!



There is a Code Snippets Manager available for both Surround With and Insert Snippet that enables you to add your own snippets although not, as far I can see, edit the existing ones. I have an open Connect submission asking for that to be changed.


That’s it! Features such as these have been available in third party add-ins for a while but its nice to see them make their way into the core product. Enjoy!


Published Monday, November 8, 2010 11:40 PM by jamiet

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



Davide Mauri said:

Sure is CTP2?

November 9, 2010 5:41 PM

MartinIsti said:

I was wondering if one of my old wish would be fulfilled (or maybe it's already happenned just I don't know about it?) that SSMS can handle the queries like e.g. PL/SQL Developer does: if you separate e.g. SELECT commands with a semicolon then you can press a key (like F8) to execute only that statement where your cursor is flashing. I find it quite useful instead the laborous "highlight the part of the statement you want to execute" method.

Would somebody else find it useful or is it just my whim?

November 10, 2010 8:01 PM

jamiet said:


Would I like that? Absolutely! I left a comment up here saying so:


November 11, 2010 1:51 AM

SteveOLAP said:

Snippets promote reusable code in BIDS' VSTA scripting environment, too. I just built one this morning for the SafeMessageBox function described by Kirk H. in his SSIS 2008 book (p.277.) With regards to creating/modifying snippets, you may wish to try Check out the screencast video. It works as advertised for VSTA, though I've not tried it against SSMS Denali.

November 11, 2010 1:25 PM

MartinIsti said:

Thanks Jamie, it's good to know that I'm not alone with my wish! And I trust the MS guys that they'll take care of our small wish (I hope it's a relatively simple feature to implement).

November 11, 2010 4:01 PM

Leave a Comment


This Blog


Privacy Statement