Query Analyzer Templates

Your manager—or a client—approaches you with an assignment and says, "I want you to make this scope change. And by the way, you need to get the job done today—with documentation—because the other developers are waiting for this project." Then, when you begin work, you discover that completing the task entails altering tables, adding new foreign keys, and changing INSERT, UPDATE, DELETE, and SELECT procedures.

The scenario is all too common. SQL Server developers toil for companies that now crunch development cycles from years to weeks. In addition, SQL Server developers produce the fundamental data layer of code for a project, so other team members must wait for them to finish the database work. As a result, developers must race to complete their work under shorter and shorter deadlines—and for longer and longer hours. But I don't rack up the overtime anymore. I've learned to apply shortcuts to my SQL scripts. How do I get more work done in less time? The templates feature in SQL Server 2000's Query Analyzer utility became a vital resource for shortening my development time.

Why Use Templates?

Everyone uses templates in one form or another. Every time you copy and paste a line of code or a sentence and change the result slightly, you use a template to reduce your workload. Programmers type the same structures in a program over and over, and almost all C and C++ tools include templates that build a skeleton for any application, module, or function. For example, when you add a function to a Visual Basic (VB) form or module, the IDE automatically creates a function template. These days, most development environments generate some type of skeleton routine when you add a function. What about SQL environments?

Native SQL Server tools include features that let you create a form of template by reverse-engineering a script of the code that you need to generate an object. However, when you use these native tools in new development, you must considerably edit the script you generate.

I used to keep a series of files on my desktop, as Figure 1 shows. When I linked these files with WordPad, I could double-click the file, then press Ctrl+A, Ctrl+C, and Alt+F consecutively. Finally, I could press X to close the file, go to SQL Server 7.0's Query Analyzer, and press Ctrl+V to paste all the text from the template file into Query Analyzer. I could then modify this basic template as necessary for my current needs. In this way, I created templates for tables, stored procedures, views, and so on. Although this process worked, it was cumbersome and it made a mess of my desktop.

A couple of years ago, I discovered a third-party tool that substantially shortened my development time. The tool let me move files from my desktop to a folder, then presented a Windows Explorer-like treeview of these files that I could drag onto a script. I immediately abandoned Query Analyzer except in rare instances when I needed a graphical analysis of my query. The third-party tool was only slightly better than keeping the files on my desktop, but it did help me organize files and let me keep a variety of code snippets handy as basic templates.

However, in my current job, I have to make do with a smaller budget and without third-party tools. On my new workstation, I installed SQL Server 2000, opened Query Analyzer, and clicked Object Browser in the menu bar. At the bottom of the Object Browser window, I discovered a tab for Templates. Clicking the Templates tab brings up a view that displays a series of folders containing templates, which Figure 2 shows. Each of these templates is a .tql file that represents a skeleton of code. You can drag these skeletons onto the design window as a basis for development or as an addition to a piece of code. In SQL Server 2000 Query Analyzer, the templates are even more useful than static snippets of code that you insert into your scripts. You can write the templates to accept easily replaceable parameters for a specific script. The parameter feature is a powerful find-and-replace tool—a vast improvement over the bare-bones templates I used to keep on my desktop.

Building Your Own Template

Let's build a template, then use it in a few sections of code to demonstrate its benefits. I use templates in two ways: to develop objects and to reduce the amount of typing that I have to do when I write a section of code.

At the company I work for, I customize the templates that I use for development according to the company's development standard. The templates provide handy development snippets of code that I use when I compile a series of objects on a server. Figure 3 shows my code template for generic stored procedures. This template consists of four sections: drop, create, and permissions sections and a success-or-failure notification section. Every stored procedure that I write includes these four sections because they speed development for everyone in the shop. For the DBAs, the stored-procedure template produces a script that runs and reports a success or failure; for the developers, the template produces consistent documentation. In addition, the "drop" section of the template eliminates errors that result when you try to create an object that already exists.

To create this template, first open Query Analyzer and enter the desired text in the design window. Second, after you enter the text, click File, Save from the menu. When the File dialog box appears, enter the template's name. Third, change SQL Query (*.sql) files in the Save as Type drop-down list to Template SQL (*.tql) files. Next, if you installed SQL Server on the C drive, open the C:program filesmicrosoft sql server80toolstemplatessql query analyzer folder. (If SQL Server isn't installed on the C drive, replace C with the name of the drive on which you installed SQL Server.) This folder already contains folders that correspond to the default templates that are installed with the tool. Then, after you save your template and right-click to refresh the treeview in the left pane, your template appears. (Figure 4 shows a template that I created by following the path I just described. In Figure 4, all the files below view_template are templates that I created.) Finally, press Ctrl+N to open a new query window, then drag your new template onto the window. The outline of a stored procedure appears.

You can perform even more tricks with this amazing Query Analyzer template tool. Figure 4 shows the template (minus proprietary information) that I use frequently. If you look closely, you can see code that follows the format parameter name, type, default value. This section of code, which takes advantage of the parameter search-and-replace feature in Query Analyzer, is a big time-saver. The code lets you include a series of parameters in your template, then replace them quickly and easily. Table 1 shows the example's three parameters.

You can change the parameter names for each project. Also, the type names you include are for programmer reference and don't affect the values you replace in the template. After you drop this template into the query window and press Shift+Ctrl+M, the Replace Template Parameters dialog box, which Figure 5 shows, appears. The dialog box lets you change the default values in the third column to suit the batch you're working on. Then, you can type the new name for the procedure, press the down arrow twice (I usually leave my name), and type the current date into the dialog box. Finally, click Replace All.

Before I began building my templates, I examined many of my existing procedures and looked for repeated patterns or sections that I changed in every procedure. Then, I set the repeating items as parameters in my templates. By using this technique, I shaved minutes off the development time for every stored procedure.

Speedy Joins

Templates are useful for more than creating new procedures. Not only did I save time in developing new stored procedures but I also quickly came upon another benefit of templates in my daily development: building faster queries. In my job, one of the databases is in third normal form (3NF), and four central tables recur in most queries that I run. Figure 6 shows the tables (with their main keys). I often join the tables as follows:

FROM product p, subcategory s, productattributes pa, producttype ptWHERE p.productid = s.productidAND s.categoryid = pt.categoryidAND pt.productid = p.productidAND p.productid = pa.productid

Typing these joins repeatedly was extremely annoying. Fortunately, I had Query Analyzer 2000 by this time, or I might have had to start renaming tables and altering the schema to keep my sanity. I created a template with the join example above and saved it in the root of the templates folder on my workstation. Because I keep the same aliases in all my queries for readability, I could drag this code snippet into any script I needed to join these tables together. This template became so useful that I even started to use it when I had to troubleshoot problems. When someone called with a data problem, I could quickly drag the template, add another table or two, add another qualification to the WHERE clause, and call the problem solved.

Free Up Your Personal Time

I love working with SQL Server and developing solutions. Spending time writing code, finding a solution to a problem, doing some testing and tweaking, and delivering a piece of code that works are aspects of my job that I relish. But although I enjoy my work, I enjoy my family more. My hat is off to the SQL Server development team for the improvements they made to Query Analyzer—especially for the templates feature. If you don't use templates in your work, I encourage you to do so. And if you're a SQL Server developer who isn't using SQL Server 2000 yet, I recommend that you examine SQL Server 2000 and its enhanced Query Analyzer. The time savings I realize when I use templates are worth the price of the software, and including templates in my work greatly increases my productivity.