Faster SQL Development using T-SQL Snippets

T-SQL Snippets allow you to quickly build T-SQL statements without having to remember the commands or its syntax. This helps in reduced development time and in increased productivity for developers and DBAs. Snippet templates were introduced in SQL Server 2012 and are based on XML with predefined fields and values. When you use T-SQL snippet, fields are highlighted and the user can tab through each fields and change the values as required.

Imagine a series of commands that you always use when creating a Trigger or a Table or a Stored Procedure or even a SELECT statement. You can now reduce the amount of time and code you would write by implementing T-SQL Snippets.

Snippets are categorized for ease of use so that the user can see and use various snippets based on the below categories.

Default Snippet	Surround Snippet	Custom Snippet
These are code templates for various T-SQL commands that you can quickly insert into your T-SQL code when creating tables, stored procedures, triggers, etc.	These are code templates that allow you to implement code construct like Begin End, If, While, etc.	Custom Snippets allow you to create your own Snippet that can appear with the Snippet menu when invoked.

Invoking Snippet:

1. Right click on Context Menu in the query editor
2. Use CTRL + K X shortcut combination by placing the cursor where you would like to insert the snippet.

Creating a Custom Snippet is a 3 step process:

1. Create a snippet using XML
2. Register the snippet in SSMS
3. Invoke the snippet when using Query editor

Note: By default, all T-SQL Snippets are stored under the following folder and are saved as .snippet files:

C:\Program Files\Microsoft SQL Server\<version>\Tools\Binn\ManagementStudio\SQL\Snippets\1033

Let’s now look at creating a Snippet using XML:

In the below example, we will create a snippet that can be used to write SELECT statement for any table and would allow the use of CASE statement for an equality check on a column.

<[default]
<[default] <[default] span=""> <[default] span="">
<[default] span="">
<[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span="">
<[default] span="">        END,
<[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span="">
<[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span="">
<[default] span="">
<[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span="">
<[default] span="">]]>
<[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span=""><[default] span="">        CASE <[default]
           WHEN '' THEN ''<[default] <[default]
           WHEN '' THEN ''<[default] <[default]
           WHEN '' THEN ''<[default] <[default]
           WHEN '' THEN ''<[default] <[default]
       ELSE 'Value not found'
, <[default] <[default]
From <[default]
Go

Note: You can also right click on the context menu in query editor and select Insert Snippet