In this article, we will look into the new Code Snippet feature of 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
- Ready-made snippets of code that can be quickly insert into our code.
- It reduces the repeatable writing of code effort
References
Code Snippet: Step by Step Guideline from Creation to Installation of a Snippet
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