SQL Query Analyzer

ToolKit

LANGUAGES: T-SQL

ASP.NETVERSIONS: All

By Ken McNamee

SQL Query Analyzer

The SQL Server Developer s Tool

Visual Studio.NET is a great all-around tool fordeveloping ASP.NET Web applications. You can develop in HTML for the browserinterface and any .NET language for the code-behind and business components.You can even develop in T-SQL for the SQL Server stored procedures. However,for the time being, my favorite tool for working with SQL Server is SQL QueryAnalyzer. Query Analyzer as it is usually referred to is simply the besttool to develop high-performance databases with fully optimized storedprocedures. As you will soon see, it is also a great learning tool that allowsyou to better understand the inner workings of SQL Server and the processesthat constitute query execution.

Many developers I know who are new to SQL Serverdevelopment usually pose the same question, Should I be using EnterpriseManager or Query Analyzer for SQL Server development? It s easy to understandtheir confusion, as both tools overlap significantly in their functionality,although the purpose of each tool is actually somewhat different. In general,Enterprise Manager s purpose is to allow for easy, graphical management of oneor more SQL Server instances. You can point and click to create, design, andmanage databases. It does a tremendous job at fulfilling this purpose. However,although you can develop stored procedures using Enterprise Manager, theinterface is not really optimized for this purpose. Query Analyzer, on theother hand, is really geared toward allowing developers to design and developoptimized SQL queries and stored procedures.

The Object Browser

Query Analyzer is mainly a code-driven tool, but as youcan see in Figure 1 with its Object Browser, you can view all the databaseobjects, such as tables, columns, stored procedures, stored procedureparameters, and even the tables on which a stored procedure is dependent. TheObject Browser is somewhat similar to viewing a SQL Server instance in VisualStudio.NET s Server Explorer window. However, one of the problems I have withthe Server Explorer is that it doesn t display the data type information forcolumns or stored procedure parameters in the same view. If you want to seethat information, you need to have the Properties window open separate from theServer Explorer, which can be inconvenient and restrictive because you can onlyview properties for one object at a time. So, I find that I always keep a QueryAnalyzer window open just so I can get that at-a-glance detailed view of thedatabase objects with which I m currently working.

Figure 1: Query Analyzer s ObjectBrowser lets you see and interact with all database objects, T-SQL functions,and code templates.

Although it does color-coding, one drawback to QueryAnalyzer is that it lacks IntelliSense. At the bottom of the Object Browser,however, is the Common Objects list, which gives API information on just aboutevery T-SQL function you will need, including input and output parameters anddata types. Just as useful is the Templates section of the Object Browser,which contains dozens of pre-defined code templates to do everything fromcreating stored procedures to managing users, roles, and other databaseobjects. Using the Object Browser I rarely find myself needing to turn to areference book for information.

The Execution Plan

Appropriately named, the most powerful reasons to use thistool lie in its query analyzation capabilities. Although you can use VisualStudio.NET or Enterprise Manager to create or alter a stored procedure, theydon t give you any idea about how the query is executed, whether it isoptimized, and which aspects of the query you need to focus on to improve itsperformance. Query Analyzer provides probably more information about a querythan you need to know, but that is rarely a bad thing.

Every time a query is executed, SQL Server goes throughseveral steps before the data is returned. First, the text of the query must beparsed to verify that there are no syntax errors or non-referenced objects. Thesecond, and most important, step is the determination of the execution plan,which is itself a series of steps for how SQL Server will be able toefficiently find and sort only the records for which the query is asking.Tables must be joined. The most efficient indexes must be chosen that have theleast amount of negative impact on the overall system.

There are two ways to view the execution plan. One is toclick the Display Estimated Execution Plan button, which shows you theexecution plan that will be used; however, the cost statistics are onlyestimates. To view the real cost of a query, first make sure that the ShowExecution Plan option is enabled in the toolbar prior to running the query.After execution you should see something similar to Figure 2, which is agraphical representation of the steps that SQL Server will take to return theresults of the query. In this sample Northwind database query I am trying toreturn all the customers with orders and the number of orders for each customer.The Customers table and Orders table are joined on the CustomerID column, forwhich there is a non-clustered index. SQL Server has determined that using thisindex is the most efficient way to execute the query.

Figure 2: The Execution Plan view inQuery Analyzer is the key to understanding the full cost of a query and thefirst step toward optimizing it.

If you have done any database design, then you know thatone of the keys to creating a high-performance database is where and how tocreate table indexes. The ability to view which indexes SQL Server has chosenin executing a query cannot be overstated. If you have a performance problemwith your database-driven Web application, then one of the first things youshould do is look at how SQL Server is using your indexes.

Conclusion

Although better tools are surely on the horizon withVisual Studio 2005 and SQL Server 2005, I believe that the best tool for SQLServer development today is SQL Query Analyzer, because it is really designedto make developers lives easier. Sure, it s a bit dated in its appearance, andlacks a few of the conveniences ASP.NET developers now take for granted, suchas IntelliSense. However, Query Analyzer makes up for those deficiencies withan uncluttered interface and powerful capabilities. After Visual Studio.NET, it sthe second application I open when I start work every morning. What more can Isay than that?

Ken McNamee is aSenior Software Developer with Vertigo Software, Inc., a leading provider ofsoftware development and consulting services on the Microsoft platform. Priorto this, he led a team of developers in re-architecting the Home ShoppingNetwork s e-commerce site, http://www.HSN.com,to 100% ASP.NET with C#. Readers can contact him at [email protected].