T-SQL is vast. Think about it: it allows for the creation and control of everything from indexes, tables, data-cubes, full-text indexes, full/differential/log backups, statistic optimization, and on and on and on. Unless you've been using SQL Server since version 4.2 there's entirely too much syntax (along with nuances, variations, etc) to master.
That's where custom templates shine. Take the following example:
USE [master]
GO
EXEC sp_addumpdevice 'disk', '<device_name,sysname,mydevice>','<device_path,nvarchar(260),--DefaultPath>'
GO
RESTORE DATABASE
[<database_restore_name,sysname,--Default>]
FROM
[<device_name,sysname,mydevice>]
WITH
RECOVERY,
REPLACE,
MOVE '<logical_filename_01,nvarchar(128),--FileName01>' TO '<physical_filepath_01,nvarchar(260),--FilePath01>'
,MOVE '<logical_filename_02,nvarchar(128),--FileName02>' TO '<physical_filepath_02,nvarchar(260),--FilePath02>'
,MOVE '<logical_filename_03,nvarchar(128),--FileName03>' TO '<physical_filepath_03,nvarchar(260),--FilePath03>'
,MOVE '<logical_filename_04,nvarchar(128),--FileName04>' TO '<physical_filepath_04,nvarchar(260),--FilePath04>'
,MOVE '<logical_filename_05,nvarchar(128),--FileName05>' TO '<physical_filepath_05,nvarchar(260),--FilePath05>'
,MOVE '<logical_filename_06,nvarchar(128),--FileName06>' TO '<physical_filepath_06,nvarchar(260),--FilePath06>'
,MOVE '<logical_filename_07,nvarchar(128),--FileName07>' TO '<physical_filepath_07,nvarchar(260),--FilePath07>'
,MOVE '<logical_filename_08,nvarchar(128),--FileName08>' TO '<physical_filepath_08,nvarchar(260),--FilePath08>'
,MOVE '<logical_filename_09,nvarchar(128),--FileName09>' TO '<physical_filepath_09,nvarchar(260),--FilePath09>'
,MOVE '<logical_filename_10,nvarchar(128),--FileName10>' TO '<physical_filepath_10,nvarchar(260),--FilePath10>'
,MOVE '<logical_filename_11,nvarchar(128),--FileName11>' TO '<physical_filepath_11,nvarchar(260),--FilePath11>'
,MOVE '<logical_filename_12,nvarchar(128),--FileName12>' TO '<physical_filepath_12,nvarchar(260),--FilePath12>'
EXEC sp_dropdevice <device_name,sysname,mydevice> --,'delfile'
A frequent task that SQL Server users often face is the need to 'copy' a database by way of restoring a database to a different location. The script above is a quick way to accomplish that. Yeah, I could use Enterprise Mangler to do it and it wouldn't take TOO long, but in the end the goal, for me at least, is to fully understand what is going on under the covers so that I can master the process. Books Online is good at providing examples, although it's not uncommon to see a number of examples that aren't terribly practical when it comes to solving real-world issues… This is one area where custom templates become invaluable: they provide you the ability to make your own 'examples' (showing you correct syntax flow with the needed switches etc.) that you can 're-use' to solve problems down the road.
Here's what I love specifically about custom templates: take the above template for example: I had a hard-fast need to create a script like that (I needed the ability to move 'copies' of production databases off to a number of various development servers, and this provided a great template for a handful of scripts I needed to create to allow developers to simply 'load a copy of a production db' at will onto dev boxes). Spending the time to make a template made me focus a bit more on the syntax (bonus points right?). Then, like many things with SQL Server, I used that template heavily for a few days… to the point where I really no longer needed the template to be able to copy a database from one server to another; using the syntax above with BACKUP syntax I was able to move databases around wily-nilly in QA in about the same time I was able to do so with EM -- I don't know about you, but I felt pretty cool when I was able to do that. Unfortunately, that was about 2 months ago… and if I had to 'copy' a database today I'd be at a bit of a loss to remember just exactly what syntax I needed to use to effectuate the copy. That's also where custom templates come in. I can either go back to Books Online and 're-learn' the entire flow of BACKUP/RESTORE, or I can whip out my templates. Because I understood the principles fairly well 2 months ago either approach is going to be fairly quick, but the template is going to allow me to not only accomplish my task much quicker, it will also help me do it right (nothing sucks more than remembering that you FORGOT a step when you run a script and it doesn't work) while at the same time re-reinforcing my knowledge of the syntax.
Simply put, custom templates are a no-brainer. One of the great things I've also come to love about custom templates is the fact that in QA you can specify the directory you want QA to look for when it goes to bind your templates to the Object Browser (just hit Tools > Options > General and you'll see that it lets you define where your template directory lives). I've found that copying the default/installed directories off to my data drive (where I keep all my data/projects/code/toolbox snippets/etc.) is invaluable. Once there I can add my own directories, and then just chunk in my own templates as needed. Then I've always got a ready-set of references available when it comes time to complete a task that I can't 100% remember how to accomplish without a bit of a mnemonic aid.
As I said above, templates are a no-brainer. My hunch though is that they are one of the most underused tools by both developers and admins alike. Am I wrong?