Introduction
Transact-SQL (T-SQL) snippets were first introduced in SQL Server 2012 Management Studio. T-SQL snippets are templates containing one or more T-SQL statements which you can easily use them when you develop T-SQL scripts. The main concept behind code snippets is code reuse. With code reuse you develop faster, easier and with less syntax errors. If you frequently use specific T-SQL statements, then you should consider creating snippets with these statements as they will help you a lot.
There are two types of T-SQL snippets: (i) Expansion, and (ii) SurroundsWith.
Expansion snippets are full T-SQL statements which can be added in your T-SQL scripts. SurroundsWith snippets is code which can surround other T-SQL statements. For example, you can have a snippet that has the “BEGIN…END” block, “IF” block, “WHILE” block, “TRY…CATCH” block, etc.
Even though snippets were first introduced in SQL Server 2012 Management Studio, they existed in Visual Studio long before SQL Server 2012 so their benefits and usage are well known to the technical community.
Using T-SQL snippets in SSMS
Now let’s see a simple example on how we can use a built-in snippet in SQL Server Management Studio.
If you right click in SQL Server Management Studio’s (SSMS) Query Window, then among other, you will be presented with the following two options:
- Insert Snippet…
- Surround With…
If you select the “Insert Snippet…” action, you are presented with the available snippet categories from which you can use the snippet to use.
In this example I used the “Inline Table Function” snippet and here’s what I got in my query window:
If you select the “Surround With…” action, you are then presented with the below built-in snippets:
So if you have a statement in your query window (i.e. “SELECT * FROM tbl1”) and select it, and then you use the SurroundWith snippet “If”, your T-SQL statement will be automatically surrounded by an IF block.
Creating custom T-SQL snippets – The manual way
When I first checked out T-SQL snippets in SQL Server Management Studio 2012, I was excited as it was a long-awaited feature. However, right after the excitement, I wondered how it could be possible to create my own snippets. I found out that this was not very straightforward as in order to create a custom T-SQL snippet for SSMS you need to write XML code. Below you can see the XML template for creating a T-SQL snippet.
As you can see, even it’s not difficult, each time you want to create or modify a custom T-SQL snippet, you will need to write or modify XML code. For example, consider that we want to create the corresponding T-SQL snippet for the below T-SQL script:
If we were about to create the snippet manually, we would have to write the below XML code:
Even though I enjoy writing code, because the purpose of snippets is to make our life easier, I would prefer an easier way as well to create and manage my custom T-SQL Snippets. For this reason, within the context of my initiative SQLArtBits, I have developed a free tool called “Snippets Generator” which can be used for this exact purpose.
Creating custom T-SQL snippets with Snippets Generator
Snippets Generator is a free, lightweight program which makes it easy for anyone to create a T-SQL snippet for SSMS 2012 or later.
As you can see in the above screenshot, Snippets Generator provides a convenient GUI environment in which you just need to complete 5 simple fields:
- Title: The title of your custom T-SQL snippet.
- Description: Your custom T-SQL snippet’s description.
- Author: The snippet’s author name.
- Snippet Type: The snippet’s type, meaning whether is an “Expansion” or “SurroundsWith” snippet.
- Snippet T-SQL Code: As the name implies, in this textbox you write the T-SQL statement that you want to be included in the snippet.
In order to further assist you in the process of creating a new T-SQL snippet, Snippets Generator provides different code formatting options as well as the below additional functionality:
- Open existing SQL files in order to generate snippets based on the SQL files’ contents.
- The ability to set a “Default Author” name in order not to have to manually enter it each time.
- A “Snippet Templates” library. This library contains built-in snippet templates which you can use them to create your own T-SQL snippets.
- The ability to save your T-SQL snippet as template and thus add it to the Snippet Templates library for future use.
Below you can see a screenshot of the Snippet Templates library.
Now let’s create the snippet for the query of Listing 1 using Snippets Generator.
If you compare the two methods for creating the snippet, I’m sure you will agree with me that using Snippets Generator is a much easier and faster method for creating the snippet.
Now let’s import the snippet into SSMS in order to verify its correctness.
We will import the snippet in the snippets category ‘My Code Snippets’:
Now that the snippet was imported in SSMS, let’s use it in a Query Window:
Conclusion
Code reuse in SQL Server Management Studio with the use of T-SQL snippets is a handy way to be more productive with less effort. Snippets Generator is a free, lightweight program which makes it easy for anyone to create a T-SQL snippet. With Snippet Generator’s assistance, you can easily create snippets for your everyday T-SQL scripting needs, so that whenever you need to run certain queries, to just call them via the snippets functionality in SSMS. Therefore, with the use of snippets, you will no longer need to remember complex T-SQL queries, as you will just be able to use them via your custom T-SQL snippets.
View all posts by Artemakis Artemiou
- Certificate Management in SQL Server 2019 - May 31, 2019
- SQL Server consolidation – Hosting multiple databases on a single SQL Server instance - December 2, 2016
- How to create and manage T-SQL code snippets - October 28, 2016