Where's my data? and other QA tricks

Introduction

Have you ever tried to query a table that had a wide column, like a description field for some product or customer and been frustrated by seeing only part of the result in query analyzer? Ever wonder why this is? Read on.

By default, the Query Analyzer tool only returns the first 255 characters of a query. Not a big deal in may cases, since you are looking for quick results. Hopefully this isn't your primary data entry tool :).

However, there will be times when you want the entire value. Perhaps you are trying to parse a 500 character field and need to see all 500 characters to get your code working. There is an option in QA to change the size of each field that you see (whether in text or grid). By selecting Tools | Options and then the "Results" tab, you can change the size of the result sets you see from the default of 256 to any number.

I often use this when performance tuning and I need to see more than 256 characters of the plan that is chosen.

Working quickly but not repetitively

Another little known trick has to do with making your day run quicker. Are there repetitive commands you use? Or things you find yourself typing over and over? I know I always have a list wherever I go. The list sometimes even changes as projects change, but there are definitely things that I need to include in a batch over and over.

For these items, there are two great things in QA that can help you work faster, and easier. The first is the tool shortcuts. These are customized commands that you can place in QA to work with a shortcut to run something you do often. These are executable commands, so there isn't a place for parameters or to alter the code. These are built in's that run with the touch of a button (or a few keys). Like sp_who. If you are in QA and want to run this command, type "CTRL+1".

Wha-la!

Without changing your code pane, anything entered in the space, or any other noticeable change, your results pane will return the output of this command. Useful if you are coding and someone asks for a quick "Who's blocking me?".

Query Analyzer includes spaces for 12 shortcuts, though 3 are prefixed and cannot be changed. These are sp_help (Alt+F1), sp_who (CTRL+1), sp_lock (CTRL+2). The rest of them can be customized to whatever commands you want to run using the "Tools | Customize" menu option. The interface is a little confusing, but click in a blank space.

Templates

I wrote an article a couple years ago about Templates in Query Analyzer for SQL Server Magazine and since then I've continued to use templates more and more. Similar to the enhancements in many development tools, templates are snippets of code that you can drag and drop into your code pane from the object browser. They are stored in c:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer as .TQL files. These are plain text files with the different extension. The extension does nothing other than have the file show up in the Object Browser under the "templates" tab.

To create a template, enter some SQL in the code pane and then do a "Save As" to save the file in the above mentioned location. Be sure to change the type to ".TQL". Then refresh your Query Analyzer object browser and your file will be there.

To use your template, drag and drop it into the code pane. Be sure to watch the vertical bar cursor in the code pane as you drag. This is where the code will appear.

Customized Templates

One extremely handy enhancement you can make to your templates is to include replaceable parameters. These are sections of the code that you will usually change, though not always, when you use a template. The easiest way to explain these is with an example.

One thing that I often use is a stored procedure template. This is a customized snippet of code to create a stored procedure. Obviously the name of the procedure would be a parameter, but the author is also for me, since I've usually worked in places where multiple people did development. So I created a template like this:

if object_id('<procedure_name,varchar(40),>') is not null
drop procedure <procedure_name,varchar(40),>
go
create procedure <procedure_name,varchar(40),>
as
/*
Description:
Author:<author_name,varchar(40),Steve Jones>
*/return
go

Notice that there are two parameters in this example. One is "procedure_name" and the other is "author_name". The code looks funny in Query Analyzer, but here's how it works.

I drag and drop this in a blank code pane. I then press "CTRL+Shift+M" and get a dialog like this:

I've got two parameters listed and anything I enter in the "value" column will be used to replace everything between the <> (brackets) for every instance of that parameter. Notice that I also have a default value for the "author_name" parameter that is already filled in. I can change this in the dialog or leave it alone. If I enter "MyProc" for the first one and leave the second parameter as "Steve Jones", I get this (after I press "replace all"):

if object_id('MyProc') is not null
drop procedure MyProc
go
create procedure MyProc
as
/*
Description:
Author:Steve Jones
*/return
go

Pretty near, huh? Instantly I have an outline of the stored procedure. I also use this for snippets of code (like big joins, that I often include. Dropping a template of:

 and p.productkey = oi.productkey
 and p.usprice = oi.usprice
 and oi.orderid = o.orderid
 and o.customerid = c.customerid

is a lot easier than typing the whole thing out over and over.

Conclusions

Not that much of this information is secret or difficult to find, but I know I like reading a bunch of information all in one place. Helps me to remember it and hopefully will help you as well. I'd also love to hear about any common one that I've forgotten and you see posted often.

I've got a few more tips I'll share with you next time that hopefully will save you a few strokes here and there. Maybe even give you time for a couple more hands of Solitaire .

Steve Jones

©dkRanch.net January 2003

Return to Steve Jones Home