Snippets and Template Browser in SSMS

While I was working one my presentation for  12 hours of SQL Server 2012 I noticed a new feature of SQL 2012 called Snippets.  These code snippets are small templates that can be used as a starting point when building your queries. They can also be pretty handy for junior DBA’s that are not pretty sure about the syntax of certain statements.

Let me give an example.  I want to create a table Person with following structure:

Field Type
PersonID int
Last Name varchar(50)
First Name varchar(50)
Age smallint

Right click in your query windows and select “Insert Snippet” or just use the short keys CTRL+K, CTRL+X

image

The snippet window opens.  Just double click the type of snippet that you want to insert, in my case Table

image

image

As result you get the following code. As you can see, the snippet has some replacement points which are marked in yellow

image

Now, go through the template with “tab” and change the replacement points into the appropriate values that you want.  The replacement points remain active until you “close” the snippet. Just press “Enter” to close it.

My example,

CREATE TABLE dbo.Person
(
PersonID int NOT NULL,
Lastname Varchar(50),
Firstname varchar(50),
Age smallint
);

 

But… Didn’t we have this feature in older versions? I thought templates were actually providing the same. But in fact, there are some differences.  Let’s follow my example again, and create the same table by using the templates.

In the menu click on View – Template Browser

image

In the template browser, select Table and double click on Create Table

image

You’ll get the following result in your query window

— =========================================
— Create table template
— =========================================
USE <database,sysname,AdventureWorks>
GO

IF OBJECT_ID(”<schema_name, sysname, dbo>.<table_name, sysname, sample_table>”, ”U”) IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

You can use the Replace Template Parameters dialog box to specify values any time a parameter definition is used in code.  Or just change the code manually.

image

image

As you can see the sample code that is provide by default is slightly different.  In the template code, they added the IF clause to determine if the table already exists or not.  The template also provides more detailed information on the create table statement like identity, data types, constraints and primary keys,…

A snippet is really something you can use as a starting point when building your queries, I call it a quick insert of a SQL statement, while a template provides a more detailed pre-written SQL statement. Templates are highly useful queries to jumpstart some of the “not-so-familiar tasks”. They also come in handy when writing deployment scripts. Templates are also a place to store and organize your own parameterized queries. I personally, used templates a lot when I was working as a junior DBA.  Once you have used them a lot, you will know them by hart Knipogende emoticon