Keyboard shortcuts manager in SSMS Denali

Another new feature of the new SSMS Denali is introduction of the new Keyboard Shortcuts Manager known from the Visual Studio, as the new version of SSMS is based on the VS 2010 shell and is more bound to the VS shell than any previous version.

If you get used for shortcuts in some 3rd party SQL Server management application, now you can configure the SSMS to use your own shortcuts.

SSMS Denali Options Dialog - Keyboard Shortcuts

For example if you are missing a default shortcut Ctrl+Shift+M for “Specify Values for Template Parameters” from previous version of SSMS and which was not set-up after my installation of SSMS Denali, you can use the shortcuts manager and assign it.

What I have found missing compared to previous version of SSMS are the custom query shortcuts. Here is screenshot from SSMS 2008R2

SSMS 2008R2 Options Dialog - Keyboard Shortcuts

But When you take a look on the keyboard shortcuts in the SSMS Denali, you will find shortcuts for the Custom Queries, although I didn’t find a way how to define the custom queries. So hope, that Microsoft will add a GUI into the options dialog for defining such queries in upcoming releases of the Denali. On the first screenshot here you can see, that there are also separate commands and shortcuts for the sp_help, sp_who and sp_lock procedures.

SSMS Denali Options Dialog - Keyboard Shortcuts - Custom Queries

Code Snippets in SSMS Denali

Microsoft has introduced a new version of SQL Server Management Studio in the first CTP1 preview of the next SQL Server version code name Denali. The new SSMS is based on new VS 2010 shell and brings some new features.

One of the new features introduced in the CTP1 are new code snippets. Who was working with third party tools or was developing applications in Visual Studio, for sure knows this feature well.

The SSMS supports two kinds of snippets

  1. Insert snippets (Expansion)
  2. Surround With snippets

You can manage the snippets using the Code Snippets Manager available from Tools menu.

SSMS Denali - Code Snippets Manager

The code snippets are using the standard VS2005 snippet XML format, so you can use current available tools for creating and modifying the SQL Server snippets.

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<_locDefinition xmlns="urn:locstudio">
    <_locDefault _loc="locNone" />
    <_locTag _loc="locData">Title</_locTag>
    <_locTag _loc="locData">Description</_locTag>
    <_locTag _loc="locData">Author</_locTag>
    <_locTag _loc="locData">ToolTip</_locTag>
</_locDefinition>
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title>Create Inline Table Function</Title>
            <Shortcut></Shortcut>
            <Description>Creates an inline table function.</Description>
            <Author>Microsoft Corporation</Author>
            <SnippetTypes>
                <SnippetType>Expansion</SnippetType>
            </SnippetTypes>
        </Header>
        <Snippet>
            <Declarations>
                                <Literal>
                                    <ID>SchemaName</ID>
                                    <ToolTip>Name of the schema</ToolTip>
                                    <Default>dbo</Default>
                                </Literal>
                                <Literal>
                                    <ID>FunctionName</ID>
                                    <ToolTip>Name of the function</ToolTip>
                                    <Default>FunctionName</Default>
                                </Literal>
                                <Literal>
                                    <ID>Param1</ID>
                                    <ToolTip>Name of the input parameter</ToolTip>
                                    <Default>param1</Default>
                                </Literal>
                                <Literal>
                                    <ID>Datatype_Param1</ID>
                                    <ToolTip>Data type of the input parameter</ToolTip>
                                    <Default>int</Default>
                                </Literal>
                                <Literal>
                                    <ID>Param2</ID>
                                    <ToolTip>Name of the input parameter</ToolTip>
                                    <Default>param2</Default>
                                </Literal>
                                <Literal>
                                    <ID>Datatype_Param2</ID>
                                    <ToolTip>Data type of the input parameter</ToolTip>
                                    <Default>char(5)</Default>
                                </Literal>
            </Declarations>
            <Code Language="SQL">
<![CDATA[CREATE FUNCTION [$SchemaName$].[$FunctionName$](
    @$Param1$ $Datatype_Param1$,
    @$Param2$ $Datatype_Param2$)
RETURNS TABLE AS RETURN(
    SELECT @$Param1$ AS c1,
           @$Param2$ AS c2)]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

Insert (Expansion) snippets

The expansion snippets are similar to the Templates known from previous versions of SSMS and which are also available in new version of SSMS. You can insert the snippet using the menu Edit/ItelliSense/Insert Snippet…, using a default keyboard shortcut Ctrl+K, Ctrl+X, or using the context menu after mouse right click.

SSMS Denali IntelliSense - Isert Snippet

Once you invoke the command you can choose from the available snippets.

SSMS Denali IntelliSense - choosing snippet

The snippet behave differently from the Templates. After the snippet is inserted several replacement parts are highlighted and you can substitute the default values by your own. Then all all occurrences are automatically substituted in the snippet. You can switch among the replacements using the Tab key. Once you are ready with modifications you simply hit the Enter Key.

SSMS Denali - Create Function Snippet

Surround With snippets

In contrast to Insert (Expansion) snippets the Surround with snippets take all the selected text and surround it by the code contained in the snippet. Surround with snippets are again accessible using the menu or Ctrl+K, Ctrl+S default shortcut.

SSMS Denali - Inserting Surround With Snippet

SSMS Denali - Surround With Snippet

Missing snippet functionality

The current snippet implementation in SSMS has only a few possibilities and small power comparing the to snippets in Visual Studio. Hope that it is only by the current CTP status and Microsoft will implement following before the final release of the product.

Snippets shortcuts

As you can see from the image of the Code Snippet Manager above, there is Shortcut written I the details of the snippet. Unfortunately all the snippets have unassigned shortcuts and event you edit the source of the snippet and put some shortcuts, the current version of SSMS doesn’t support the snippet shortcuts. It could be nice to have eg. “itf” shortcut for Inline Table Function and simple press Tab for the snippet to be inserted as it works in Visual Studio

Functions in Snippets

I would welcome a functions element functionality in the SQL snippets. E.g.. Function for expanding table fields from a table name etc. Something like GenerateSwitchCases function for expanding enum members in C#. By adding such functionality it would be very easy to write a easy to use complex snippets e.g.. for CRUD procedures generation etc.. SSMS Denali is Based on VS 2010 and visual studio doesn’t allow writing custom functions for use inside of snippets, at least Microsoft could introduce at least the function for table expansions I mentioned here.