Learn more about SQL Server tools

   
   






















































Connect with MSSQLTips




MSSQLTips Facebook Page MSSQLTips LinkedIn Page MSSQLTips RSS Feed MSSQLTips Pinterest Page MSSQLTips Twitter Page MSSQLTips Google+ Page

Use SQL Server Code Snippets to encourage consistent conventions

MSSQLTips author Aaron Bertrand By:   |   Read Comments   |   Related Tips: More > SQL Server Management Studio
Problem

Many development teams find it challenging to maintain consistent naming and coding conventions. In places I've worked, these can be enforced by having a "gatekeeper" who can accept, reject or modify all submitted code changes based on personal preferences, company policy, or a combination. This is tedious for the gatekeeper, and frustrating for the developers - especially if the conventions are controversial and/or not well documented. In some places, teams use the Template feature in Management Studio, which lets you set up commonly-used bits of code for re-use. The main problem with the Template solution is that the template files are stored locally; so, when conventions change or new templates are introduced, they must be distributed to all developers.

Solution

In SQL Server 2012, Management Studio has a new feature called Code Snippets, which closely match a similar feature long offered in Visual Studio. This new feature in SSMS was described in a previous tip, Getting started with Code Snippets feature of SQL Server 2012. One item from that tip that I'd like to explore further is the use of custom snippets. While Management Studio ships with a wide array of default snippets, you can also create your own; more importantly, a custom snippets folder can point at a network location. This means that you can store all of your code snippets in a central, network location, and simply have all of your developers get their snippets from that location. This means they can all be using the same source, and as soon as changes are made, they will all see the changes without any need to pass files around.

To get started, let's create a share on a network-accessible device. This may work slightly different depending on your operating system and network configuration. In my case, I'm creating a folder "Snippets" on a central device (running Windows Server 2008 R2), then sharing that folder with specific users. You will need to determine how best to configure permissions in your environment, so I'll leave those details out (including whether you want some users to be able to write to that share, in which case they will need write permissions).

sql server snippets

Once that share is accessible over the network, check that you can access it from the machine(s) where you're running Management Studio. You can do this by simply trying to connect to the path in Windows Explorer:

create network share path

Now go to Tools > Code Snippets Manager, and click "Add...":

open code snippet management

sql code snippet manager

Then navigate to the share, highlight the folder, and click "Select Folder":

sql code snippets directory

Now you'll have an empty folder here called Snippets, and you'll need to repeat this one step on each machine where you want to use this centralized snippet store:

sql server code snippets manager

As of SQL Server 2012 RC0, there is no automated way to add new snippets to this folder - you will be doing it manually. And I'll be quite honest, creating the snippets is not for the faint of heart. But once they're created, you're going to love the way they work.

To create a new snippet, let's look at one of the existing ones to see how they are constructed. One of the simplest examples to look at is the "Create Schema" snippet. Navigate to the following folder (change "Program Files (x86)" to "Program Files" if you are on an x86 machine):

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033\Schema\

Right-click the file Create Schema.snippet, and choose Open With > Notepad. You'll see this (I'm going to highlight the elements I'll talk about - the rest won't change for your custom 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 Schema</Title>
        <Shortcut></Shortcut>
 <Description>Creates a database schema.</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>
  </Declarations>
  <Code Language="SQL"> <![CDATA[
CREATE SCHEMA $SchemaName$
]]>
  </Code>
 </Snippet>
 </CodeSnippet>
</CodeSnippets>

There are main areas that will impact the appearance and use of the snippet (I'll leave others such as Author as an exercise for the reader):

  1. Title - this is what shows up as the "name" of the snippet in both the Code Snippets Manager and the Insert Snippet context menu (marked as #1 in the diagrams below).
  2. Description - this is where you can write a lengthier explanation of the purpose of the snippet. It shows up in the Code Snippets Manager dialog when you've selected a specific snippet, and as a tooltip when you're about to insert a specific snippet (marked as #2 in the diagrams below).
  3. Literal - you will have one of these for each token you want to replace in your snippet. The tokens are highlighted once you've inserted the snippet. The three elements are:
    • ID - this is how you will identify the token in the code (in the code, you will surround this token with $dollar signs$ - note that it is case-sensitive).
    • ToolTip - this is what shows up if you hover on the token before you've replaced it.
    • Default - this is the value that should be used if you tab past the token without replacing it.
  4. Code - pretty self-explanatory, this is the actual code, as is, that will be inserted into your query window when you select a specific snippet. Here are the diagrams showing #1 and #2; I'll illustrate #3 and #4 in the example below.
    create new code snippet

    sql server snippet

As a quick example, let's make changes to the above four areas, and save a new snippet. For this we'll use a slightly more complicated scenario: creating an optimized cursor. Change the XML to the following:

<?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 Optimized Cursor</Title>
        <Shortcut></Shortcut>
 <Description>Creates a non-default, optimized cursor for forward-only, read-only operations.</Description>
 <Author>Microsoft Corporation</Author>
 <SnippetTypes>
  <SnippetType>Expansion</SnippetType>
 </SnippetTypes>
 </Header>
 <Snippet>
  <Declarations>
                  <Literal>
   <ID>CursorName</ID>
   <ToolTip>Name of the cursor</ToolTip>
   <Default>c</Default>
    </Literal>
    <Literal>
   <ID>Query</ID>
   <ToolTip>Query text including ORDER BY</ToolTip>
   <Default>SELECT QUERY</Default>
    </Literal>
    <Literal>
   <ID>Variables</ID>
   <ToolTip>List of variables from query</ToolTip>
   <Default>@foo</Default>
    </Literal>
  </Declarations>
  <Code Language="SQL"> <![CDATA[
DECLARE $CursorName$ CURSOR LOCAL FAST_FORWARD FORWARD_ONLY
FOR
 $Query$
 FOR READ ONLY;
OPEN $CursorName$;
FETCH $CursorName$ INTO $Variables$;
WHILE @@FETCH_STATUS = 0
BEGIN
 FETCH $CursorName$ INTO $Variables$;
END
CLOSE $CursorName$;
DEALLOCATE $CursorName$;
]]>
  </Code>
 </Snippet>
 </CodeSnippet>
</CodeSnippets>

As you can see, tokens like CursorName are identified as literals in the XML (<ID>), and then appear as $CursorName$ whenever they appear in the code.

Save this file in the central Snippets share you created earlier, as Create Optimized Cursor.snippet.

As an aside, I like to dig in and see how things work, but you don't have to. There are projects like the Snippets Generator for SQL Server 2012 to make this work easier. I haven't tried this specific one, but you can be sure that, until Microsoft provides an easier way to do this natively, you'll see more of these tools and utilities crop up.

Now you'll be able to right-click within a query window, choose "Insert Snippet..." and pick "Create Optimized Cursor" from the "Snippets" folder:

sql create snippet

When you double-click on the specific snippet, your query window should be populated like this:

You can see that the first token ("c") is highlighted, and all the subsequent matching tokens are outlined with a dotted red line. If you type over the c and click tab, all the other instances of c will change to whatever you typed, and your cursor will move to the next token ("SELECT QUERY"). This is literally two keystrokes later:

I don't want to belabor the details about how snippets work, as that was covered quite well in the aforementioned tip. I just wanted to demonstrate how useful the custom snippets folder can be in helping your team to use the same code structure and adhere to the same standards.

One complication with this approach may be that if the network share is only accessible via the local network or over a VPN, your remote users will not be able to access the templates unless they are connected. So it's not a perfect solution for all team topographies.


Next Steps


Last Update: 1/25/2012


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     





 
Sponsor Information