다음을 통해 공유

SQL Server 2012 new feature The Snippets

  • 아티클

Snippets

In SQL 2012 we have a new feature called snippets; So what are this snippets?

Lots of DBAs, when required to create a new T-SQL command, check out the syntax in BOL, they try to remember on how to do it, Or sometimes we need a repeating T-SQL Command like: Select, Proc, Func, triggers, tables etc. that we need to recreate with parametrical chances.

For that purpose, we have now in SQL 2012 the snippets. This feature can potentially help us with reducing development time and assist the productivity for developers and DBA’s.

Snippet are templates in SQL Server 2012 that are based on XML with predefined fields and values.

The snippets is not a server feature but an SSMS feature.

Snippets are categorized by groups in order to facilitate the use of them.

There are the three category of snippets in SQL 2012

DEFAULT SNIPPETS

SURROUND SNIPPETS

CUSTOM SNPPETS

Code templates for various T-SQL commands. We can quickly insert into our T-SQL code when creating tables, stored procedures, triggers, etc.

Code templates that allow users to implement code construct like Begin End, If, While, for, case, etc.

Custom Snippets enabling the user to create his own Snippet that can appear in the Snippet menu when called.

Snippets are usually stored in this path:

[C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SQL\Snippets\1033]

image

Creating our first Snippets

Step #1 - Create a Snippet using XML:

In step1 we are creating the XML file that holds all the information about the template and the code of the template that we will use. In this example we will create a "create table" T-SQL command, the XML will contain all the information on our snippets.

   1: <?xmlversion="1.0"encoding="utf-8" ?>

  2: <CodeSnippetsxmlns="https://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">

  3: <CodeSnippetFormat="1.0.0">

  4: <Header>

  5: <Title>TableTemplate</Title>

  6: <Description>insertthecolumnsnamesanddatatype.</Description>

  7: <Author>OrenBouni(MicrosoftCorporation-SQLPFE)</Author>

  8: <SnippetTypes>

  9: <SnippetType>Expansion</SnippetType>

 10: </SnippetTypes>

 11: </Header>

 12: <Snippet>

 13: <CodeLanguage="SQL">

 14: <![CDATA[

 15: Create Table Customers

 16: (

 17:   rgistrationDate  datetime default getdate(),

 18:   '<Col_Name>'  '<DataType>',

 19:   '<Col_Name>'  '<DataType>',

 20:   '<Col_Name>'  '<DataType>',

 21:   '<Col_Name>'  '<DataType>',

 22:   OfficeAddress  Varchar(100),

 23:   OfficeManager  Varchar(50)

 24: ) 

 25: Go

 26: ]]>

 27: </Code>

 28: </Snippet>

 29: </CodeSnippet>

 30: </CodeSnippets>

Step #2–register the snippets in the SSMS:

Once we have created the XML snippets file (as demonstrated above) we need to register the snippet in the SSMS, we can do one of the following:

Adding the snippets:

1. Create a new folder or use the existing folder and store the XML snippet in it, In my case I’ll use the new folder option and create a folder named Oren snippets

2. start SQL Server Management Studio

3. Select the “Tools” option from the menu items and Click on “Code Snippets Manager”

4. Click on the “Add” button

5. Browse to the folder containing the file, and click on the Select Folder button.

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 AddTable_Cust.snippet file

5. mark it, store it in the appropriate location I saved it in My Code Snippets and press OK.

After doing so, creating the snippet, adding it to the SSMS by importing it to the correct location, we can invoke the Snippet and use it in the SSMS.

To call a snippet we need in the SSMS new query windows to press the CTRL + K + X and then we will get the Snippet menu.

image

Now we will navigate to my code folder and choose the "create table" snippet,

 

image

Have fun and start using this nice and very helpful option.

Comments

  • Anonymous
    January 01, 2003
    snippets are very helpful! I love them! thanks for the post

  • Anonymous
    January 01, 2003
    You're Welcome

  • Anonymous
    April 14, 2016
    i have snippets built in xml with the added comment to help the user along. I need a way to remove user help comment blocks once the user is done filling all the blocks of sql code? i don't want to use tooltip function in xml since my comments are little bit descriptive to define company policy. i know when using $end$ it places the cursor at specific block of code when the user is done filling things out but i want to tie in the clean up block for all the extra comments.