Become More Efficient at Writing TSQL by Creating Code Snippets

A TSQL code snippet is a set of code that performs a task that can easily be placed in your TSQL code using SQL Server Management Studio (SSMS). Snippets are a great way to jump start creating a fragment of TSQL code for common types tasks, like creating a table, a stored procedure, a trigger, an index, and more. In this tip I will show you how to create and register a new snippet with SSMS.

To create a new snippet, you first have to create a file that contains your snippet code. Code snippets are defined using XML, and are saved with a file extension of “snippet”. SQL Server comes with some pre-canned snippet of XML code that is stored in the following root folder structure on my PC in the following location: C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SQL\Snippets\1033\. Depending on your installation of SSMS it might be in the same or a slightly different location. Underneath this folder there are series of other folders, where each folder represents a different type of SQL Server object. For example the “Create Table” snippet is stored in the C:\Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE\SQL\Snippets\1033\Table file folder.

To show you how to create a new snippet, I will be creating a new snippet named “Create Table In-Memory”. My new snippet can be used to quickly insert a CREATE TABLE statement into a TSQL script, that has the syntax necessary to create a table that resides in memory. Let’s get started in creating my new snippet.

The first step to creating this new template will be to create my snippet file, which will be called “Create Table In-Memory”. I can store my snippet anywhere, even in the root folder identified above. My snippet code looks like this:

<?xml version=”1.0″ encoding=”utf-8″ ?>

<CodeSnippets >

<_locDefinition >

    <_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 Table In Memory</Title>

<Shortcut></Shortcut>

<Description>Creates a table In Memory.</Description>

<Author>Gregory A. Larsen</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>Tablename</ID>

<ToolTip>Name of the table</ToolTip>

<Default>Sample_Table</Default>

</Literal>

<Literal>

<ID>column1</ID>

<ToolTip>Name of the column</ToolTip>

<Default>column_1</Default>

</Literal>

<Literal>

<ID>datatype1</ID>

<ToolTip>Data type of the column for Primary Key</ToolTip>

<Default>int NOT NULL PRIMARY KEY NONCLUSTERED</Default>

</Literal>

<Literal>

<ID>column2</ID>

<ToolTip>Name of the column</ToolTip>

<Default>column_2</Default>

</Literal>

<Literal>

<ID>datatype2</ID>

<ToolTip>Data type of the column</ToolTip>

<Default>int NULL</Default>

</Literal>

</Declarations>

<Code Language=”SQL”>

<![CDATA[CREATE TABLE $SchemaName$.$Tablename$

(

    $column1$ $datatype1$,

    $column2$ $datatype2$

)

WITH

   (MEMORY_OPTIMIZED = ON,

    DURABILITY = SCHEMA_AND_DATA);

$end$]]>

</Code>

</Snippet>

</CodeSnippet>

</CodeSnippets>

To add my new snippet, I use the “Code Snippets Manager” which can be started using the Tools option as shown below, or by using the Ctrl+K, Ctrl+B shortcut.

Writing code snippets starts with the code snippets manager window.

After launching the Code Snippet Manager tool, the following screen is displayed:

Code Snippets manager window.

To add my new “Create Table In-Memory” schema I will click on the “Import” button.  Upon doing that SSMS allows me to browse to the folder that contains my new snippet and there I select my “Create Table In-Memory” file.   After doing that the following “Insert Code Snippet” window will be displayed:

The final window needed for SQL snippets.

The final step to create my new “Create Table In-Memory” snippet is to click on the “Finish” button. It’s that easy.

Once I have finished importing my new snippet, I can start using the Code Snippet Manager to insert my new create table in-memory snippet into a query window.  Having a snippet for those fragments of code you write frequently can improve the speed and accuracy in of writing TSQL code.  As you are building your TSQL code you might want to consider creating snippets for those code fragments you write over and over again.

Gregory Larsen
Gregory Larsen
Gregory A. Larsen is a DBA at Washington State Department of Health (DOH). Greg is responsible for maintaining SQL Server and other database management software. Greg works with customers and developers to design and implement database changes, and solve database/application related problems. Greg builds homegrown solutions to simplify and streamline common database management tasks, such as capacity management.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles