SQL Server 2000 Query Analyzer: 10 tricks for simple querying

I compliment Microsoft on the improvements made to Query Analyzer in previous SQL Server version releases -- including the addition of the object browser and syntax color coding -- but I am surprised to learn that Query Analyzer will be no more come SQL Server 2005. Instead, Microsoft has built its querying features into SQL Server Management Studio. My impressions of SQL Server 2005 have been nothing but positive, but I must ask Microsoft: Would you please offer a lightweight querying tool?

Query Analyzer's performance gains can be divided into two broad categories:

1. Improvements in the raw performance of a SQL Server statement
2. Improvements in your job performance as a SQL Server DBA or developer

Here I will show you why and how SQL Server 2000 Query Analyzer is more than just a simple querying tool.

Tip 1: Graphical Query Plans Overview One of the best interfaces for understanding and improving the underlying SQL Server parser is the Graphical Query Plans only available in Query Analyzer. Based on a T-SQL statement, the parser will show the steps SQL Server takes to return your query. How do you enable it? Navigate to Query Menu | Show Execution Plan | Show Server Trace or Show Client Statistics. How do you read them? For the Show Execution Plan, read from right to left and hover your mouse over the icon on the lower pane for additional information. First focus on the processing that requires the most resources and determine how to improve the query performance. Additional resources Graphically Displaying the Execution Plan Using SQL Query Analyzer
2: Object Browser Overview The object browser has two tabs: Objects Tab and Templates Tab (outlined in Tip 3 below). The Objects Tab permits you to browse all of the database objects; drag and drop database object names by left clicking and pulling to the right pane; determine the definition of the objects; and access common system functions with the same drag-and-drop functionality. How do you enable it? Navigate to the Tools menu | Object Browser | Show/Hide. For your information Make sure you refresh the Object Browser by right clicking on the object that's higher in the hierarchy and selecting "Refresh," or press F5 to see the latest definitions and code. If something is missing or looks incorrect, refresh. Additional resources Using Object Browser
Tip 3: Object Scripting Overview An observation from working with many DBAs and developers is that they are familiar with Enterprise Manager's functionality to CREATE or DROP an object, but they were unfamiliar with the Query Analyzer's functionality, which can do the job just as well. Why leave Query Analyzer when this tool can do the job? How do you access it? Right click on the object and select the "Script Object to New Windows As" option. A context-sensitive menu will appear to generate a basic CREATE, ALTER, DROP, SELECT, INSERT, UPDATE, DELETE or EXECUTE. This is a quick-and-easy approach for interacting with the object you need without leaving Query Analyzer.
Tip 4: Templates Overview Who needs to go to SQL Server 2005 Books Online when Query Analyzer has coding templates? Do you know where they are located? They are on the Templates Tab in the Object Browser. Where are the templates stored? The interface is populated with the folders and *.tql files located by default at C:\Program Files\Microsoft SQL Server\80\Tools\Templates\SQL Query Analyzer\. The beauty of it is that you can add your own folders and files for common code you issue. For your information If you add new files and folders to the templates directory, make sure you close all of your sessions with Query Analyzer and re-open them to access the new folders and files.
Tip 5: Object Search Overview Have you ever lost an object in SQL Server? You know it is there, but not exactly where. Object Search is the solution to this problem. It is able to query across all databases from one interface. How do you access it? Navigate to the Tools menu | Object Search | New. Complete the options on the interface and press "Find Now" for the object.
Tip 6: Manage Indexes Overview Manage Indexes is another interface that few DBAs and developers seem to be aware of. Enterprise Manager has this capability, but why leave Query Analyzer to create, alter or drop existing indexes via a simple interface? How do you access this interface? Navigate to the Tools menu and the Manage Indexes option. For your information This is a simple, yet powerful interface. Heed this caution: When you are working in a production environment and drop, create or alter an index, the performance implications can be high. This type of work in a production environment should be planned and performed during a maintenance window
Tip 7: Manage Statistics Overview A similar interface and performance gain to Manage Indexes is Manage Statistics. This interface gives you the opportunity to create, update or drop statistics. How do you access this interface? Navigate to Tools menu | Manage Statistics option. For your information This is a simple, yet powerful interface. Heed this caution: When you are working in a production environment and drop, create or update statistics with a high sample rate, the performance implications can be high. This type of work in a production environment should be planned and performed during a maintenance window.
Tip 8: Bookmarks Overview One handy way to navigate or troubleshoot code is to set up bookmarks. The bookmarks become light blue boxes in the left margin of the query window, which can be easily seen. You also have the option to use keyboard shortcuts to navigate through many bookmarks. How do you access the bookmarks? Navigate to Edit | Bookmarks | Toggle Books, Next Bookmark, Previous Bookmark or Clear All Bookmarks.
Tip 9: Keyboard Short Cuts Overview Become a keyboard maestro with Query Analyzer using its many short cuts.	Execute a Query – CTRL + E Show or Hide the Results Pane – CTRL + R Comment Code – CTRL + SHIFT + C Uncomment Code – CTRL + SHIFT + R Results in Text – CTRL + T Results in Grid – CTRL + D
Tip 10: Query Analyzer Options Overview The Options interface has a number of tabs for many of the configurable items in the tool. I see this interface as similar to the Microsoft Office interface and appreciate the options to universally change items across the application. How do you access it? Navigate to Tools menu | select Option selection.

About the Author: Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also the SearchSQLServer.com Performance Tuning expert. Ask him a question here.

More information from SearchSQLServer.com