Using SQL Server Templates

00:00
  -
  -
  -
  -
00:15
  -
  -
  -
  -
00:30
  -
  -
  -
  -
00:45
  -
  -
  -
  -
01:00
  -
  -
  -
  -
01:15
  -
  -
  -
  -
01:30
  -
  -
  -
  -
01:45
  -
  -
  -
  -
02:00
  -
  -
  -
  -
02:15
  -
  -
  -
  -
02:30
  -
  -
  -
  -
02:45
  -
  -
  -
  -
03:00
  -
  -
  -
  -
03:15
  -
  -
  -
  -
03:30
  -
  -
  -
  -
03:45
  -
  -
  -
  -
04:00
  -
  -
  -
  -
04:15
  -
  -
  -
  -
04:30
  -
  -
  -
  -
04:45
  -
  -
  -
  -
05:00
  -
  -
  -
  -
05:15
  -
  -
  -
  -
05:30
  -
  -
  -
  -
05:45
  -
  -
  -
  -
06:00
  -
  -
  -
  -
06:15
  -
  -
  -
  -
06:30
  -
  -
  -
  -
06:45
  -
  -
  -
  -
07:00
  -
  -
  -
  -
07:09

Hello and welcome to another SQL Server Video, my name is Michael Campbell and in this video we’re going to take a look at using SQL Server Templates – which represent a really under-utilized, and frequently, unknown, aspect of interacting efficiently with SQL Server.

Specifically, in this video, we’ll look at what SQL Server Templates are, how to use them, how to use the wide assortment of templates that ship with SQL Server, and how create your own, custom, templates.

To get started we’ll take a look at what a template is – as that’s the best way to get a feel for how they work. And the first thing to note about opening one up is that I’m just opening up a plain-old .sql file – meaning that template functionality can be embedded, natively, into the files that you normally work with in SQL Server Management Studio and SQL Server Query Analyzer.

Now, in this case, the ‘template’ that I’ve opened up is one that I use a few times every week to create the 4-digit codes that allow me to link viewers to other videos, resources, and web sites from the SSV site. What’s important to note though, is that in this template I’m actually calling a sproc – meaning that templates and sprocs don’t serve the same purpose.

Instead, I find that templates are the perfect way to address queries or operations that you tackle on a semi-frequent basis – where having to rewrite the T-SQL to do what you want to do each time would be tedious, and where trying to remember business rules, the correct order of operations, or even some of the nuances of T-SQL would also be a pain.

As such, where templates come into play is by letting you save queries with a built-in way to easily support and facilitate parameterization directly within your code itself.

Now, as you can see in this template, the syntax for parameterization is fairly easy to recognize as it’s wrapped in angle-brackets.
And in each case that syntax takes the form of an opening angle-bracket, the name of the parameter or variable that you want to assign, the datatype that your input will be assigned to within code, along with an optional default value if you wish to provide one.

So, with that in mind, let’s go ahead and replace these existing parameters. To do that, we can either use the option from the Query Menu, or we can use the corresponding hot-keys for this action: CTRL+SHIFT+M – which is my preference – and that hot-key combo is the same in all versions of SQL Server.

And, as you can see, each parameter is listed by name, and we’re also shown information about the data-type of each ‘chunk’ of code that we’re replacing – and given a slot to put in our desired value or use a default if it’s been set.

So, in this case we’ll go ahead and create a new quick-link that can be used to access this video on templates, and as such I’ll use the quick code of tmpl.

And notice that I’ve literally surrounded my string or text data with ticks or quotes within the body of the template itself – meaning that I don’t have to surround my parameter values with ticks. In fact, if I did wrap my parameter values with ticks, those literal ticks would be output into my template, and I’d have double-ticks, and a problem.

I’ve also done myself a favor with this template and left some ‘documentation’ in place that I can look at in order to remind myself about the different types of quick-links (or business logic) that I can set up on the site.

I’m then just going to use the default value for the next two parameters, and then paste in the id for the resource that I want to link to.

And what makes templates so cool is that I’m able to create a new link in just seconds – whereas if I were doing this from scratch I’d need to remember all the different sproc parameters as well as the different link types, business rules, and so on.

Templates also provide some great flexibility for enabling the quick creation of code, or for using dynamic code in ways that you just can’t touch with sprocs – like here, for example, where I’m changing the USE statement to now work with a variable.

And, just like that, it’s possible to create and consume your own templates.
Another great thing about templates is that SQL Server Management Studio has a ton of them built right in – which you can access from the Template Explorer.

And, as you can see, within this template explorer, there are not only a wide variety of templates for T-SQL, but there are also templates for SQL Compact and Analysis Services as well – if you have those products installed.

Now, to use these templates, all you need to do is double-click on a template that you want to use, and its contents will be streamed into a new query window that you can work with as needed.

Obviously, not only can this help save time, but it serves as an awesome way to see full-blown examples of T-SQL syntax which can be used to handle common tasks.

And, if you’re using SQL Server 2000, you also have a large number of existing templates at your disposal – accessed from the templates tab within the object explorer – and these work in virtually the same way.

The only real way that SQL Server 2000 differs from SQL Server 2005 and 2008 in the use of templates is when it comes to adding your own, custom, templates into the existing templates already available.

In SQL Server 2005 and 2008, you can just right-click within the Template Explorer to add new folders and/or templates – though you’ll need to specify a location for your template files and I’m just dumping mine into the default directory – though you’ll likely want to take a bit more care in sorting yours.

With SQL Server 2000, if you want your templates and folders to show up within the template browser, you need to actually go out and add them to the underlying file system on your client machine – and restart Query Analyzer before they show up.

Of course, you’re also free to store template files anywhere that you want.
For example, I frequently keep a couple of them in normal file-system folders for certain projects, and also frequently send them to clients when I need to provide a script that handles basic tasks while requiring a bit of input before being executed.

There’s also no reason you couldn’t include them in source control, or use them in a variety of other ways as well.

If you do end up using templates in this manner though, you’ll likely want to make sure that if you get into the habit of using them a lot that you set them as read-only – otherwise, if you’re like me, and frequently press CTRL+S to save your work all the time, you’ll likely find that you’ll go to open them up one day and find that your parameters have all been overwritten.

Otherwise, good luck on improving your own productivity with the use of templates, thanks for joining, and we look forward to seeing you in subsequent videos.