How to create and manage T SQL code snippets
How to create and manage T-SQL code snippets
Figure 1: Right-Clicking in Query Window in SSMS (snippet options). If you select the “Insert Snippet…” action, you are presented with the available snippet categories from which you can use the snippet to use.
Figure 2: Inserting Expansion T-SQL Snippet in SSMS – Part 1. In this example I used the “Inline Table Function” snippet and here’s what I got in my query window:
Figure 3: Inserting Expansion T-SQL Snippet in SSMS – Part 2. If you select the “Surround With…” action, you are then presented with the below built-in snippets:
Figure 4: Available Built-In SurroundsWith Snippets in SSMS. 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.
Figure 5: Inserting an ‘IF’ T-SQL Snippet in SSMS Query Window.
Figure 6: The XML T-SQL Snippets Template for SSMS. 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:
Listing 1: Sample T-SQL Script to Include in Snippet. If we were about to create the snippet manually, we would have to write the below XML code:
Listing 2: Creating the Snippet Using XML (based on Listing 1 query). 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.
Figure 7: Snippets Generator – Creating a New T-SQL Snippet. 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.
Figure 8: Snippets Generator – Snippet Templates Library. Now let’s create the snippet for the query of Listing 1 using Snippets Generator.
Figure 9: Creating a Snippet for Listing 1. 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.
Figure 10: Importing the Snippet Created Using Snippets Generator – Part 1. We will import the snippet in the snippets category ‘My Code Snippets’:
Figure 11: Importing the Snippet Created Using Snippets Generator – Part 2. Now that the snippet was imported in SSMS, let’s use it in a Query Window:
Figure 12: Using the T-SQL Snippet.
Figure 13: Using the T-SQL Snippet and Running the Script.
Author Recent Posts Artemakis ArtemiouArtemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator, DBA Security Advisor and In-Memory OLTP Simulator. Moreover, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com. You can follow Artemakis on Twitter
View all posts by Artemakis Artemiou Latest posts by Artemakis Artemiou (see all) 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
SQLShack
SQL Server training EspañolHow to create and manage T-SQL code snippets
October 28, 2016 by Artemakis ArtemiouIntroduction
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…Figure 1: Right-Clicking in Query Window in SSMS (snippet options). If you select the “Insert Snippet…” action, you are presented with the available snippet categories from which you can use the snippet to use.
Figure 2: Inserting Expansion T-SQL Snippet in SSMS – Part 1. In this example I used the “Inline Table Function” snippet and here’s what I got in my query window:
Figure 3: Inserting Expansion T-SQL Snippet in SSMS – Part 2. If you select the “Surround With…” action, you are then presented with the below built-in snippets:
Figure 4: Available Built-In SurroundsWith Snippets in SSMS. 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.
Figure 5: Inserting an ‘IF’ T-SQL Snippet in SSMS Query Window.
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.Figure 6: The XML T-SQL Snippets Template for SSMS. 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:
Listing 1: Sample T-SQL Script to Include in Snippet. If we were about to create the snippet manually, we would have to write the below XML code:
Listing 2: Creating the Snippet Using XML (based on Listing 1 query). 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.Figure 7: Snippets Generator – Creating a New T-SQL Snippet. 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.
Figure 8: Snippets Generator – Snippet Templates Library. Now let’s create the snippet for the query of Listing 1 using Snippets Generator.
Figure 9: Creating a Snippet for Listing 1. 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.
Figure 10: Importing the Snippet Created Using Snippets Generator – Part 1. We will import the snippet in the snippets category ‘My Code Snippets’:
Figure 11: Importing the Snippet Created Using Snippets Generator – Part 2. Now that the snippet was imported in SSMS, let’s use it in a Query Window:
Figure 12: Using the T-SQL Snippet.
Figure 13: Using the T-SQL Snippet and Running the Script.
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.Author Recent Posts Artemakis ArtemiouArtemakis Artemiou is a Senior SQL Server and Software Architect, Author, and a former Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator, DBA Security Advisor and In-Memory OLTP Simulator. Moreover, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Artemakis's official website can be found at aartemiou.com. You can follow Artemakis on Twitter
View all posts by Artemakis Artemiou Latest posts by Artemakis Artemiou (see all) 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