The “SQL Guy” Post # 27: Increasing Developer Productivity with Snippets
This week we have something for you to share with the SQL Server developers, as well as use in your DBA T-SQL scripts to make life easier when you re-use common code. Saleem Hakani provides some guidance on new functionality in SQL Server 2012 to help make you more productive when creating T-SQL code.
SQL Server 2012 introduces a new feature called T-SQL Snippets. T-SQL Snippets allow you to quickly build T-SQL statements without having to remember the commands or its syntax. This feature can potentially help with reduced development time and in increased productivity for developers and DBA’s. Snippet templates in SQL Server 2012 are based on XML with predefined fields and values. When you use T-SQL snippet, these 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 in SQL Server 2012.
Snippets are categorized for ease of use so that the user can see and use various snippets based on the category.
SQL Server 2012 introduces three types of snippets:
DEFAULT SNIPPETS (aka: Expansion Snippets) |
SURROUND SNIPPETS |
CUSTOM SNPPETS |
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 OR INSERTING T-SQL SNIPPET:
There are multiple ways with which you can invoke T-SQL Snippets in SQL Server 2012. The easiest way to invoke T-SQL Snippet is to right click on context menu in the query editor and you will be presented with the Snippet options.
Another quick way of inserting a snippet when writing T-SQL code is to use “CTRL + K X” shortcut key combination by placing the cursor where you would like to insert the snippet.
CREATING A CUSTOM SNIPPET
Let’s now look at how to create a custom Snippet and add it to the Snippet menu. Creating and using a Snippet is a 3 steps 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\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033”
|
||
STEP1: CREATE A T-SQL SNIPPET FILE USING XML:
In the example below, 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.
|
||
STEP2: REGISTER THE SNPPET WTH SQL SERVER MANAGEMENT STUDIO
Once the above file is created, you can now use the Code Snippets Manager to register the snippet with SQL Server Management Studio. You can either add a new folder based on the snippet category or import individual snippets to the My Code Snippets folder.
To Add a Snippet Folder: 1. Launch SQL Server Management Studio 2. Select “Tools” from the menu items and Click on “Code Snippets Manager” (This will launch the Snippet Manager) 3. Click on the “Add” button 4. Browse to the folder containing CASE_END.Snippet file, and click on the Select Folder button.
Next step is to import the Snippet in to SQL Server Management Studio:
To Import a Snippet: 1. Launch SQL Server Management Studio 2. Select “Tools” from the menu items and click on “Code Snippets Manager” 3. Click on the “Import” button at the bottom 4. Browse to the folder containing CASE_END.snippet file and select CASE_End.snippet file by clicking on it and then click on “Open” button.
|
||
|
||
STEP3: INVOKE THE SNIPPET FROM QUERY EDITOR
You now have a snippet created called CASE_END that can be invoked by using the shortcut key from the query editor by pressing “CTRL + K X” and then by selecting the category folder in which you created the snippet.
Alternatively, you can right click on the context menu in query editor and select “Insert Snippet… ”
|
Using the above steps, you can create T-SQL code snippet and register it with SQL Server Management Studio. You can also create complex snippets of various regular tasks and make your SQL Server life easy.
