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.

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

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 +.

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.

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.

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.

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

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”.

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.