First Look at SQL Server Denali (Part 2 – Snippets)

This is a quick post for a new feature I discovered while writing the first “Denali” post. SQL Server “Denali” CTP1 is available for download from the Microsoft Download Center here.

The first post used a very straightforward While loop to demonstrate the improved debugging ability. You now have the ability to add code Snippets and Surround With Snippets.

If I use the code from the previous post without the loop it looks like this:

If you select and right-click the UPDATE statement you are presented with a dialog box which includes Insert Snippet and Surround With. By selecting Surround With you are presented with a further menu which comprises Begin, If, and While. You can also type your own freeform text. For this example we need a while loop and so you click While and press Enter. The statement is now surrounded by a WHILE snippet as shown:

It is now a short step to complete the statement and finish the code as follows:

It could be argued that this doesn’t save a lot of time because it has only typed three words, however it does prevent mistakes and correctly formats your SQL.

The snippets feature is similar except you are typically using it for new queries, or new statements in a query. In the previous example you might want to check the results of the query and because you run this often you have decided to create a view (it is at this point that I wish I created a query which had a purpose rather than something which just looped around for the sake of it. Anyway, I digress…).

By creating a new query, right-clicking and selecting Insert Snippet, you are presented with these options:

Scrolling down reveals View and when you click this the following snippet is created:

From here, you can quickly finish the syntax to arrive at the final statement:

Again, nothing amazing, but there is a wide range of snippets and I wouldn’t be surprised if this was expanded for release. This is actually a feature that comes from Visual Studio and therefore we also have the Code Snippets Manager which I personally think makes these features much more useful. If you go to the Tools menu and select Code Snippets Manager, you are presented with the following dialog box which allows you to add your own snippets:

Adding a snippet is not as straightforward as clicking Add and typing some SQL however. Snippets are XML files with a .snippet extension. If you want to create your own I would recommend searching for .snippet files and using an existing one as a starting point. The Create View snippet has the following syntax:

A bit of editing (and bad highlighting) gets you here:

If you now save this in the Snippets folder in My Documents with a .snippet extension, you are ready to add it. Return to Code Snippet Manager and click Add. Select your snippets folder and it will appear as a folder in the snippet list. Now you can add a snippet in the standard way, but have your own snippet:

Now there are real benefits from snippets by creating your own standardized SQL. I’m not aware of a more user-friendly method to create the snippets, but if anyone else has found one, I’d love to hear about it in the comments.