I’ve run into quite a few DBA’s who aren’t using the Templates feature we provide with SQL Server Management Studio. If you spend a little time becoming familiar with them, I think you’ll find they are a powerful tool.
Templates are really just a series of Transact-SQL statements that live on your hard drive – in the C:\Users\(your login name)\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql folder in Vista, and in C:\Program Files\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates\Sql in XP. You can actually just open them with a text editor and look at them. You can create new folders there and put your own scripts in those folders.
If you want to work with them in SQL Server Management Studio, just click the “View” menu and select “Template Explorer”. From here you can find the Templates we deliver, rename them, create your own and so on.
To use a Template, open the viewer, double-click the template you want and it will automatically display in a new query window. You’ll notice once one shows up that there are standard T-SQL statements as well as some parameters marked with the <> symbols on either side of the parameter name. You can overwrite those parameters with static values that make sense in your situation, but you don’t have to locate and type over each one – just press CTRL-SHIFT-M. You’ll get a little panel that pops up for you to type the value you want, and it will automatically write over the values.
I find three big benefits for using the Templates. First, it’s a great teaching aid. We wrote the scripts, so theoretically they show a “best practice” for developing that kind of code. You can read how we did it and adapt it to your situation.
Second, Templates enforce consistency. If you automate your statements this way, you’ll always do it the same way, every time. That increases speed, reduces errors, and makes for more maintainable code.
Finally, creating your own Templates creates a great scripting library. Just how many times do you want to look up how to do a CUBE WITH ROLLUP statement, anyway? Type it once, put some comments in there, and be done with it!