Using Templates

1

March 18, 2013 by Kenneth Fisher

Templates are one of those really handy tools that most DBAs I know of have either never heard of or just don’t use much. I have to admit I fall into the second category myself. I will break out templates occasionally when I’m working with something I don’t do much. Cursors for example (please keep the yelling and screaming to a minimum).

To open the template pane either go to VIEW-Template Explorer or hit Ctrl-Alt-T.

Template1

The template pane has a list of the “templates” that come with SQL Server and any templates that you create for yourself.

Template2

Using a Template:

Let’s say that you want to create a partitioned table and haven’t done one in awhile. First go to the partition function option in the template pane and click the +.

Template3

Next we are going to select “Create Partitioned Table”. There are three ways we can do this. We can right click and select “Open”, double click on it, or drag it into a query window. Either way we get this.

Template4

Now we can fill in the template parameters. The template parameters are in the format of . Again there are 3 ways (that I know of) to fill them in. Probably the hardest, fill them in by hand. The next two are about the same. Select Query->”Specify Values for Template Parameters …”, or select Cntrl-Shift-M.

Template5

Note that each template parameter has a name, a data type, and a default value. In this case I’m going to leave schema_name and input_parameter_type as the defaults and fill in some simple values for the others.

Template6

Hit OK and the values will be filled into the template parameters.

Template7

Now in this case the partition schema needed a little work and I changed it from.

 -- Create partition scheme
CREATE PARTITION SCHEME sch_PartitionTest
AS 
	PARTITION fn_PartitionTest TO ([PRIMARY], TransactionHistoryGroup, WorkOrderGroup)
GO

To

 -- Create partition scheme
CREATE PARTITION SCHEME sch_PartitionTest
AS 
	PARTITION fn_PartitionTest TO ([PRIMARY], [PRIMARY], [PRIMARY])
GO

Because I don’t have those filegroups in my AdventureWorks database. If this were for real I would probably also need to change the default for the Partition Function as well. But once those minor changes are made I end up with a partitioned table called dbo.tbl_PartitionTest.

Now let’s say I want to make a few minor changes. In my case this particular template specifies the AdventureWorks database and I want that as another parameter. Not to mention the fact that mine is AdventureWorks2008, so I want a different default.

First I go back to the template exporer and right click on the template. I then select “Edit”.

Template8

Next I replace

USE AdventureWorks

with

USE <database_name, sysname, AdventureWorks2008>

Note in the template parameter the first part is the parameter name, the next is the datatype and last is the default value.

And lastly I save by clicking Cntrl-S or File-Save.

You can of course use this exact same method for creating brand new templates by right clicking on a template folder and selecting New-Template, name it, then edit it.

About these ads

One thought on “Using Templates

  1. […] to set the @SQL_String variable to varchar(MAX) and had it set to varchar(40) from the original cursor template. I noticed this because all of my output strings were chopped […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 293 other followers

%d bloggers like this: