If you've worked with Microsoft SQL Server at all, you've run across SQL Query Analyzer. Of course, this tool is essential for running ad hoc queries and executing SQL statements. But have you ever taken the time to really investigate its capabilities? The SQL Server developers built a lot of functionality into Query Analyzer, not all of which is obvious to the casual user. In this article, I'll offer you ten bits of Query Analyzer that you might not have looked at already.
1. Getting Database Object Information
You probably know that SQL Server stores metadata about all of the objects in a database. The system tables contain a wealth of information about column names, data types, identity seeds, and so on. But did you know that you can get that information with a single keystroke via Query Analyzer? Highlight the object name in any SQL statement and press Alt+F1. Figure 1 shows the results for a SQL Server table. If you don't have anything highlighted, Alt+F1 will give you information about the database itself. For an equally neat trick, highlight a SQL keyword and press Shift+F1; you'll go straight to the Books Online page that describes that keyword.
2. Executing Part of a SQL Statement
Sometimes it's convenient to execute only part of a complex SQL statement that you're developing. For example, you might be working on a stored procedure that batches many statements together, or a query that contains a subquery. No problem! Just highlight the part that you want to execute, and press F5 (or press the Execute Query toolbar button if you're a mouse sort of person). Query Analyzer will only execute the highlighted text. You can parse the highlighted text without executing it by pressing Ctrl+F5.
3. Alter Objects Fast with the Object Browser
I can't possibly be the only one who's ever needed to fix an existing stored procedure. Fortunately, using Query Analyzer means never having to write the ALTER PROC statement by hand. First, display the Object Browser by pressing F8, if it's not already showing. Expand the tree to show the object that you're interested in (this tip works with any object, not just stored procedures). Right-click on the object and select Script Object to New Window As -> Alter. Query Analyzer will open a new query window, and build the necessary ALTER PROC statement for you.
If you've never looked at them, take a few minutes to explore the Object Browser shortcut menus. You can send object scripts to the clipboard or to a file, execute stored procs, or build CREATE or DROP SQL statements, among other options.
4. Drag and Drop from the Object Browser
The Object Browser is also a drag and drop source. Drag a table to a query window, and you get the table's name. Drag the Columns node under a table, and you get all of the columns from the table, separated by commas. Drag a single column and you get the column name. Judicious use of this technique can make fast work of building things like INSERT INTO statements, as well as avoid spelling errors.
5. Templates are Your Friend
Query Analyzer supports templates - boilerplate files containing SQL statements - to help you build tricky SQL more quickly. These files have the extension .tql, and they're stored in folders underneath the Templates\SQL Query Analyzer directory if you've done a full client tools install. Use Ctrl+Shift+Ins or Edit -> Insert Template to open a template into the current query window. Many templates contain parameters, which are delimited by angle brackets. Press Ctrl+Shift+M and you'll get the Replace Template Parameters dialog box, as shown in Figure 2.
Templates are just plain text files. If you find yourself needing to frequently insert some complex chunk of SQL, you can create and save your own template file to make life simpler in the future.
6. What's Up With This Query?
If you're faced with a query that has a performance issue, query analyzer is your first stop for gathering information. You have easy access to four different ways of looking at query performance:
- Ctrl+L will show you the estimated execution plan before you run the query.
- Ctrl+K will show you the actual execution plan after you run the query.
- Ctrl+Shift+T will open the trace pane, showing you the trace events on the server as you run the query.
- Ctrl+Shift+S will show you client-side statistics after you run the query.
There are items on the Query menu for each of these panes, just in case your brain is already too full to hold new shortcut keys.
7. Customize Connection Properties
When you fire up Query Analyzer and connect it to a database, it sets a few defaults - for instance, ANSI style null handling. If you'd like to tweak the connection properties for your own server environment, select Connection Properties from the Edit menu to open the dialog box shown in Figure 3, and click to your heart's content.
8. Get Back Wide Results
If you run a query that returns a column with lots of data, you'll discover that Query Analyzer truncates the results after the first 256 characters. This is especially annoying when you're working with FOR XML queries, which can return thousands of characters in an XML document format. Fortunately, this limitation is easy to modify. Select Options from the Tools menu, and navigate to the Results tab of the Options dialog box. Enter a new value for the Maximum Characters Per Column property and you're all set. While you're there, take the time to click around the rest of the Options dialog box; Query Analyzer lets you tweak quite a few things to match your own preferences.
9. Query Debugging
Query Analyzer contains a complete debugger. You can single step through stored procedures, inspect the value of local and global variables, supply values for parameters, inspect the callstack when multiple procedures are nested, and so on. The easiest way to get started is to right-click a stored procedure in the Object Browser window and select Debug . Figure 4 shows the debugging environment in action. This can be a real lifesaver when you're trying to figure out what's wrong with a complex stored procedure.
10. The Tools Menu the Way You Want It
The Query Analyzer Tools menu is extensible. Select Customize from the Tools menu to open the Customize dialog box, and switch to the Tools tab. Here you can enter new values to appear on the menu, and specify the executable file to call for each entry. You can also pass the server name, database name, or user name to the external utility.
And Yes, There's More
I hope this article has convinced you that Query Analyzer can be a powerful tool for creating, executing, and debugging SQL statements. There are plenty of features that I didn't cram into this top ten. For example, the Query Analyzer editor supports bookmarks, forcing case, adjusting tabs, and more. If this is a product that you use on a frequent basis, you owe it to yourself to keep digging and find out how to use it more efficiently. The time spent learning will be amply repaid.
About the Author
Mike Gunderloy is the author of over 20 books and numerous articles on development topics, and the lead developer for Larkware. Check out his latest book, Coder to Developer from Sybex. When he's not writing code, Mike putters in the garden on his farm in eastern Washington state.
0 comments:
Post a Comment