Using Snippets in SSMS

A little know and used feature by database developers is Snippets. Database developers understandably do not use this feature because we have long been accustomed to using prebuilt templates and code blocks from other sources. Often we have a readymade template which we customize as needed and that is it. Database administrators have mastered SQLCMD Mode to customize scripts for deployment since almost a decade now.

A Snippet while not as bullet proof as a template is a quick and easy way to add some basic code blocks into your Ad-hoc queries. Something I find very useful since I spend a significant amount of time in data exploration and performance tuning issues. The best example of a Snippet would be the Try Catch Block.

The First step to creating a snippet is to identify what code block you want to insert into you snippet. Here is a customized version of a snippet I use to set NO COUNT ON, Insert a try catch block and track the execution time and number of rows affected for Adhoc queries I run.

<?xml version="1.0" encoding="utf-8" ?>  
<CodeSnippets  xmlns="https://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">  
<_locDefinition xmlns="urn:locstudio">  
    <_locDefault _loc="locNone" />  
    <_locTag _loc="locData">Title</_locTag>  
    <_locTag _loc="locData">Description</_locTag>  
    <_locTag _loc="locData">Author</_locTag>  
    <_locTag _loc="locData">ToolTip</_locTag>  
   <_locTag _loc="locData">Default</_locTag>  
</_locDefinition>  
<CodeSnippet Format="1.0.0">  
<Header>  
<Title>TryCatch</Title>  
                        <Shortcut></Shortcut>  
<Description>Example Snippet for Try-Catch.</Description>  
<Author>Jayanth Kurup - Snippet</Author>  
<SnippetTypes>  
                                <SnippetType>SurroundsWith</SnippetType>  
</SnippetTypes>  
</Header>  
<Snippet>  
<Declarations>  
                                <Literal>  
                                <ID>CatchCode</ID>  
                                <ToolTip>Code to handle the caught error</ToolTip>  
                                <Default>CatchCode</Default>  
                                </Literal>  
</Declarations>  
<Code Language="SQL"><![CDATA[  
SET NOCOUNT ON
BEGIN TRY  
BEGIN TRAN T1
DECLARE @starttime datetime2 = getdate()

$selected$ $end$  

PRINT 'This query took ' +cast(datediff(s,@starttime, getdate()) as varchar(100)) + ' secs to complete and affected ' + cast(@@rowcount as varchar(100))+ 'rows . Started at'+cast(@starttime as varchar(100))
COMMIT TRAN T1
END TRY  
BEGIN CATCH  

    SELECT ERROR_MESSAGE() as Error
    ROLLBACK TRAN T1

END CATCH;  
]]>  
</Code>  
</Snippet>  
</CodeSnippet>  
</CodeSnippets>

You can edit the same template for pretty much any code block you want.

Once the Snippet is created you need to save the file with an extension .snippet

And import it into SSMS as shown below

Navigate to the folder my code Snippets and Import the file you created above.

Once imported you can simply highlight the code you want to surround with your snippet and Press Ctrl K + Ctrl S as show below

Identify the snippet you want to use from the list and press Enter.

Below is an example of my snippet

I found this simple piece of code saved a ton of my time on multiple occasions.