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.

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.

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

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.

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.

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.