Yesterday, working with a colleague the topic got steered towards the use of snippets in SQL Server 2012+. My reflex was to let my colleague know that my blog is wonderful source for all things SQL Server. Much to my chagrin, for all of the snippets I have, I have written a blog post about it.

Visual Studio has had code snippets for a decade now, but SQL Server only got them a version and a half ago. I might imagine that having templates was the excuse given for not pursuing them but I am glad we have them now. The tools menu will lead you to the Code Snippets Manager… from there you can find the path to built-in snippets. These provide good fodder for creating your own.

There are two types of snippets. Expansion snippets insert the code snippet where the cursor is. Surround snippets wrap code around highlighted SQL. Both have arguments that allow you to tab through variable portions and update multiple references in a single place. Let’s take a look at a simple expansion snippet.

<?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 Temporary Table</Title>
			<Shortcut></Shortcut>
			<Description>Creates a temporary table.</Description>
			<Author>Thomas W Marshall</Author>
			<SnippetTypes>
				<SnippetType>Expansion</SnippetType>
			</SnippetTypes>
		</Header>
		<Snippet>
			<Declarations>
                                <Literal>
                                	<ID>TableName</ID>
                                	<ToolTip>Name of the Table</ToolTip>
                                	<Default>TableName</Default>
                                </Literal>
                                <Literal>
                                	<ID>Column1</ID>
                                	<ToolTip>Name of the Columneter</ToolTip>
                                	<Default>Column1</Default>
                                </Literal>
                                <Literal>
                                	<ID>Datatype_Column1</ID>
                                	<ToolTip>Data type of the Columneter</ToolTip>
                                	<Default>int</Default>
                                </Literal>
                                <Literal>
                                	<ID>Column2</ID>
                                	<ToolTip>Name of the Column</ToolTip>
                                	<Default>Column2</Default>
                                </Literal>
                                <Literal>
                                	<ID>Datatype_Column2</ID>
                                	<ToolTip>Data type of the Column </ToolTip>
                                	<Default>char(5)</Default>
                                </Literal>
			</Declarations>
			<Code Language="SQL"><![CDATA[

IF EXISTS(SELECT name FROM tempdb.sys.objects WHERE object_id=OBJECT_ID('tempdb..#$TableName$') AND type = N'U') DROP TABLE #$TableName$;
CREATE TABLE #$TableName$
(
    $Column1$ $Datatype_Column1$,
    $Column2$ $Datatype_Column2$
);

]]>
			</Code>
		</Snippet>
	</CodeSnippet>
</CodeSnippets>

You can copy the above XML into a file with the extension .snippet and save it in the Code Snippets\SQL\My Code Snippets directory. You should then see it in the Code Snippets manager. Alternatively, you can import a .snippet file using the Code Snippets manager. Once the snippet has been installed whenever you strike the expansion chord CTRL+K,X in SQL Server Management Studio you will see it in the My Code Snippets folder of the fly-out menu.

In the above XML document we can see a field in the declarations for each of the variable fields. Once you have chosen to insert the snippet those declare fields are linked by there name appearing between $’s. This allows you to change the value in one and have all instances of that name update. You can also tab to the next name.