Code snippet in Sql Server 2012 (Denali)

Introduction

Sql Server 2012 (code name : Denali) has come out with lot of developer friendly features.Today we will look into the new "Code Snippet" feature.Code snippets allow us to quickly insert code templates into the query window in order to perform some common TSql tasks. This feature is already present in Visual Studio and now has been integrated into SSMS too

How to invoke code snippet?

The code snippet can be invoked either by Edit->IntelliSense ->Insert Snippet or by the keyboard short cut Ctrl k, Ctrl X.

If we need to insert a basic Stored Procedure template, just double click on the Stored Procedure snippet as under

And the snippet code will be generated

How to create Creating Custom Snippet?

We can however, create our own custom snippet apart from the built in code snippets.

Here we will create a custom snippet to create Sequence object.Below are the steps to be followed for creating our custom code snippet.

Step 1: Create the snippet file and save it

As an initial step, we need to create a .snippet file which is basically a XML file that has Pre-defined schema. So our snippet file looks as under

<?xml version="1.0" encoding="utf-8"?>
<CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
  <CodeSnippet Format="1.0.0">
    <Header>
      <SnippetTypes>
        <SnippetType>Expansion</SnippetType>
      </SnippetTypes>
      <Title>Sequence</Title>
      <Author>Niladri Biswas</Author>
      <Description>Code snippet for Sequence Creation</Description>
      <HelpUrl>http://msdn.microsoft.com/en-us/library/ff878091(v=sql.110).aspx</HelpUrl>
      <Shortcut></Shortcut>
    </Header>
	
    <Snippet>
      <Code Language="sql">
                       <![CDATA[
		CREATE SEQUENCE Sample_Sequence  		
 		START WITH 1
 		INCREMENT BY 1
 		MINVALUE -2147483648
 		MAXVALUE 2147483647 		 
		]]>
    </Code>
    </Snippet>
	
  </CodeSnippet>
</CodeSnippets>

Save it into some folder and name it as CustomSequence.snippet(Note that the file extension is .snippet) in a folder say CustomSequence. In my case the entire path is C:\CustomSequence\ CustomSequence.snippet

Step 2: Register the custom code snippet in Code Snippet Manager

From the SSMS, Tools->Code Snippets Manager or invoke the short cut key as Ctrl K, Ctrl B. The code snippet manager window will open.

Click on the Add… button and choose the folder (here CustomSequence) where we have generated the CustomSequence.snippet file. In this case it will be C:\ CustomSequence

As can be seen that, the folder has been added. Click OK.

tep 3: Invoking the code snippet

We can invoke the code snippet either by Tools->Code Snippets Manager or by the short cut key as Ctrl K, Ctrl B

Double click on the Sequence and the code snippet will be generated as under in the Query Editor

CREATE SEQUENCE Sample_Sequence 
 		
 START WITH 1
 INCREMENT BY 1
 MINVALUE -2147483648
 MAXVALUE 2147483647 
 

Benefits of code snippet

1. Ready-made snippets of code that can be quickly insert into our code.
2. It reduces the repeatable writing of code effort

References

Conclusion

So in this article, we have learn what is the code snippet, the benefits of it and how easily we can create our own code snippet.Hope you enjoyed this article.Thanks for reading

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.