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:

<_locDefinition >

    <_locDefault _loc=”locNone” />

    <_locTag _loc=”locData”>Title

    <_locTag _loc=”locData”>Description

    <_locTag _loc=”locData”>Author

    <_locTag _loc=”locData”>ToolTip

SchemaName

Name of the schema

dbo

Tablename

Name of the table

Sample_Table

column1

Name of the column

column_1

datatype1

Data type of the column for Primary Key

int NOT NULL PRIMARY KEY NONCLUSTERED

column2

Name of the column

column_2

datatype2

Data type of the column

int NULL

(

    $column1$ $datatype1$,

    $column2$ $datatype2$

)

WITH

   (MEMORY_OPTIMIZED = ON,

    DURABILITY = SCHEMA_AND_DATA);

$end$]]>

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.

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

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 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.