Brian Egler's SQL Server Strategies

Brian Egler

Previous Article Next Article

Template Explorer 2008: One of my favorite features

By Brian Egler on Wed, 09/17/08 - 11:43am.

One of my favorite features in SQL Server Management Studio is the Template Explorer. It has been enhanced in 2008 to support new templates to help you use the new release effectively. Templates were available in SQL Query Analyzer back in 2000 but there were a limited number of templates available. Of course you can develop your own, but now Microsoft has given us plenty to get our teeth into. Let's take a look at some of them...

To launch the Template Explorer from within SSMS, choose it from the View menu. This exposes a window, by default on the right, and shows various folders with meaningful names that contain templates. Each template can be opened by double clicking it. The thing to remember about these templates is that it is always a two-step process to get to some working code. Open the template (by double-clicking it) then choose "Specify Values for Template Parameters" from the Query menu. You will then need to enter some values for the template parameters. I usually take the default values if I can, just to get to some working code. Then I use the Edit Find/Replace features in the Query Editor to make global changes. The thing is, if you forget the second step, the template is really just pseudo-code and will throw up errors if you try to execute it. The template parameters in the template are coded surrounded by "<>" characters. For instance, in the Create Database template there is a parameter:

<Database_Name, sysname, Database_Name>.

This represents a single parameter called "Database_Name" that must be validated as a "sysname" data type and the default value will be "Database_Name". When you "Specify Values for Template Parameters" you get a chance to override the default values. This is a big time saver (and typo saver in my case!) that also provides an educational resource for good coding practices. If you highlight a statement and press F1, you will be taken to Books Online for that statement with further syntax information and most importantly, more examples to help you out. Remember, as I talked about in another article, even the experts use Books Online on a regular basis. New templates include those associated with new 2008 features such as Change Data Capture, Change Tracking and the Resource Governor among others. Watch out, if you have performed an in-place upgrade, the default data paths in some of the templates may not be valid paths. I learned that the hard way. You can update existing templates if you choose, by right clicking and choosing Edit, then saving any changes.

There are other templates for Analysis Services and SQL Server Compact Edition, available by clicking the appropriate icon at the top of the Template Explorer window when databases of that type are available.

You can even create your own templates by right-clicking the destination folder and selecting New/Template. Then you can name and code your own template using the parameter syntax embedded in your code. I usually use copy and paste to get what I need. It's not plagiarism, think of it as reuse!

Cheers

Brian