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:
Right click in your query windows and select “Insert Snippet” or just use the short keys CTRL+K, CTRL+X
The snippet window opens. Just double click the type of snippet that you want to insert, in my case Table
As result you get the following code. As you can see, the snippet has some replacement points which are marked in yellow
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.
CREATE TABLE dbo.Person
PersonID int NOT NULL,
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
In the template browser, select Table and double click on Create Table
You’ll get the following result in your query window
— Create table template
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>
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>)
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.
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