Microsoft® SQL Server™ 2000 Programming Step by Step

Author Rebecca Riordan Pages 720 Disk 1 Companion CD(s) Level Intermediate Published 11/29/2000 ISBN 9780735611429 Price $49.99 To see this book's discounted price, select a reseller below.	More Information About the Book Table of Contents Sample Chapter Index Related Series Related Books About the Author Support: Book & CD Rate this book

Lesson 21: The Query Analyzer

• Understanding the Query Analyzer
• Starting the Query Analyzer
• Selecting a Database
• Using the Query Window
• Entering Transact-SQL Statements
• Using SQL Scripts
• Using the Object Browser
• Opening Objects
• Adding Objects to the Editor Pane
• Scripting Objects

Lesson 21 The Query Analyzer

• Start the Query Analyzer.
• Select a database with which to work.
• Execute Transact-SQL statements in the Query window.
• Create SQL Scripts.
• Open objects from the Object Browser.
• Add objects to the Editor Pane from the Object Browser.
• Script objects from the Object Browser.

In the previous lessons, we’ve used the Enterprise Manager to create and maintain database objects and to perform basic data operations. In this lesson, we’ll look at another one of Microsoft SQL Server’s graphical tools—the Query Analyzer.

Understanding the Query Analyzer

The Query Analyzer provides you with powerful tools for writing and debugging complex sets of Transact-SQL statements in various forms. (We’ll look at one of these, SQL scripts, in this lesson.) It also provides the means to analyze the performance of queries via execution plans and the Index Tuning Wizard, as we’ll see in Lesson 23.

Starting the Query Analyzer

If you start the Query Analyzer from the Start menu, or if you haven’t made a server or database connection in the Enterprise Manager, you must make the connection manually from within the Query Analyzer.

Start the Query Analyzer from the Enterprise Manager

1. Navigate to the Aromatherapy database in the Console Tree in the Enterprise Manager.

SQL Server displays a list of database objects in the Details Pane.



2. Select SQL Query Analyzer from the Tools menu.

SQL Server opens the Query Analyzer, automatically connecting to the server and the Aromatherapy database.



---

NOTE:
If you don’t see the Object Browser in the Query Analyzer, press F8 to display it.

---

3. Close the Query Analyzer.

Start the Query Analyzer from the Start menu

1. On the taskbar, click the Start button.
2. Point to Programs, and then select the Microsoft SQL Server folder.

The icons in the Microsoft SQL Server folder appear in a list.

3. Click the Query Analyzer program icon.



Query Analyzer icon

The Query Analyzer displays the Connect to SQL Server dialog box.



4. Make sure the correct server and authentication mode are selected, and click OK.

The Query Analyzer connects to the server, and then opens. The Query Analyzer will connect to whichever database has been selected as the default for your login.

---

TIP:
If your server isn’t set to start automatically, you can use the Start SQL Server If It Is Stopped check box to automatically start the server when you connect.

---

Selecting a Database

Figure 21-1. The Query Analyzer toolbar displays the currently selected database.

Selecting a database using the toolbar

1. Select Northwind in the toolbar combo box.

1. Select Change Database in the Query menu.

The Query Analyzer displays the Select Database dialog box.



---

TIP:
You can also use the shortcut key Ctrl-U to open the Select Database dialog box.

---

2. Select the Aromatherapy database by clicking anywhere in that row.



3. Click OK.

The Query Analyzer selects the Aromatherapy database.

Using the Query Window

New Query button

The Query window displays the name of the database server, the current database, the current login, and the query name in the title bar. The Query window is similar to the Enterprise Manager’s Query Designer but is much more powerful.

As an editor, the Query window is more flexible and powerful than the Query Designer’s SQL Pane. While the Query Designer can process only a limited set of SQL statements, the Query window can process any valid Transact-SQL statement, and you can enter multiple statements for processing in a single batch.

Although the Query window doesn’t support the Grid or Diagram views available in the Query Designer, it supports additional views of a query that are used to analyze performance, as we’ll see in Lesson 23.

The Query window also gives you the option of displaying each view of the query in a separate tab, rather than as panes. This is often the most convenient way of viewing the query.

Entering Transact-SQL Statements

Color	Meaning
Blue	Keyword
Dark Green	Comment
Dark Red	Stored Procedure
Gray	Operator
Green	System Table
Magenta	System Function
Red	Character String

Table 21-1. Color coding used in the Query Analyzer’s Query window.

Execute a SELECT query

1. Enter the following SELECT statement in the Query window:

SELECT OilID, OilName, LEFT(LatinName, 10)
FROM Oils

2. The Query window changes the color of the text as you type.



3. Click the Execute Query button on the Query Analyzer toolbar.



Execute Query button

The Query Analyzer adds a pane to the Query window containing two tabs: the Grids tab, which contains the query results, and the Messages tab.



4. Select the Messages tab.

The Query window displays the messages generated by the query.

1. Select Options from the Query Analyzer’s Tools menu.

The Query Analyzer displays the Options dialog box.



2. Select the Editor tab.



3. Select the check box labeled Tabbed Mode (Vs. Splitter Mode).



4. Click OK.

The Query Analyzer changes the display of the Query window.

Using SQL Scripts

Although scripts are most often used for creating database objects, they’re not limited to this use. Any valid Transact-SQL statement can be included in a script.

The SQL statements in a script are grouped into batches. A script can contain one or more batches, and each batch can contain one or more SQL statements. In a script containing more than one batch, the batches are separated by the GO command. If a script doesn’t contain the GO command, all the statements will be executed as a single batch.

Create a script

1. Change the SQL statement in the Query window to read:

SELECT OilID, OilName, LEFT(LatinName, 10)
FROM Oils
GO
SELECT PlantPartID, PlantPart
FROM PlantParts

---

TIP:
Only the last three lines have been added to the query used in the previous exercise.

---

2. Click the Execute Query button on the Query Analyzer toolbar.

The Query Analyzer displays the results in two panes of the Grids tab of the Query window.



3. Reselect the Editor tab of the Query window, and click the Save button on the Query Analyzer toolbar.



Save button

The Query Analyzer displays the Save Query dialog box.



---

IMPORTANT:
If you don’t select the Editor tab before clicking the Save button, the Query Analyzer will save the result of the query, rather than the query itself.

---

4. Navigate to the SQL 2000 Step by Step folder in the root directory, and name the script Lesson21.



5. Click Save.

The Query Analyzer saves the new script file.

Open a script

1. Click the New Query button on the Query Analyzer toolbar.



New Query button

The Query Analyzer opens a new, empty Query window.



2. Click the Open button on the Query Analyzer toolbar.



Open button

The Query Analyzer displays the Open Query File dialog box.



3. Navigate to the SQL 2000 Step by Step folder in the root directory, and select Lesson 21.



4. Click Open.

The Query Analyzer displays the script in the Query window.



5. Click the Execute Query button on the Query Analyzer toolbar to run the script.



Execute Query button

The Query Analyzer displays the results in the Grids tab.



6. Close the script window.

Using the Object Browser

The Objects tab of the Object Browser is arranged somewhat differently from the Console Tree in the Enterprise Manager. Only database objects are listed, so items such as Logins and Data Transformation Services packages aren’t visible. Also, the tables in a database are divided into two folders for convenience: User Tables and System Tables.

Additionally, rather than simply listing the tables in a database the way the Enterprise Manager Console Tree does, the Object Browser displays the columns, indexes, constraints, and triggers that you’ve defined for the table, as well as the dependencies, the views, and the tables that depend on the table. Figure 21-2 shows the Object Browser display for the Oils table.

Figure 21-2. The Object Browser displays information about the tables in your database that’s useful from a programming perspective.

The Common Objects folder of the Object Browser contains, along with the built-in data types, a set of common Transact-SQL functions grouped into categories. For each function, the Parameters folder contains a description of each parameter, including its name and data types. Figure 21–3 shows the LEFT function as it is displayed in the Object Browser window.

Figure 21-3. The Object Browser displays functions by categories in the Common Objects folder.

Using the Object Browser, you can open a table or view, similar to the way you display rows by opening the Query Designer in the Enterprise Manager. Having done so, you can simply view the rows, or you can insert new rows and edit existing ones.

You can also use the Object Browser when you’re creating Transact-SQL programs. You can add objects to the Query window using drag-and-drop, and you can create several different kinds of scripts automatically from the context menu.

Opening Objects

Open a table

1. Expand the User Tables folder of the Aromatherapy database in the Object Browser.

The Query Analyzer displays a list of tables in the database.



2. Right-click dbo.PlantParts, and select Open.

The Query Analyzer displays the rows in the table in an Open Table window.



3. When you’ve finished viewing the rows in the table, close the Open Table window.

Open a view

1. Expand the Views folder of the Aromatherapy database in the Object Brower.

The Query Analyzer displays a list of all views in the database.



2. Right-click dbo.OilCautionsExtended, and select Open.

The Query Analyzer displays the rows returned by the query in an Open Table window.



3. When you’ve finished viewing the rows in the table, close the Open Table window.

Adding Objects to the Editor Pane

Add a database object

1. Select the Query window, make sure the Editor tab is selected, and then click the Clear Window button on the Query Analyzer toolbar.



Clear Window button

The Query Analyzer empties the Editor Pane.

2. Type the following SQL statement into the Editor Pane (be sure to put a space after the final FROM):

SELECT * 
FROM

3. Expand the User Tables folder in the Object Browser.



4. Drag the dbo.Properties table from the Object Browser to the Editor Pane and drop it after the word FROM.

The Query Analyzer pastes the table name into the statement.



5. Click the Execute Query button to execute the query.



Execute Query button

The Query Analyzer displays the results in the Grids Pane.

1. Reselect the Editor tab of the Query window.
2. Delete the * from the SELECT statement.



3. Expand the dbo.Properties folder in the Object Browser.



4. Drag the Columns folder from the Object Browser to the Editor Pane and drop it after the word SELECT.

The Query Analyzer pastes all the column names into the statement.



5. Click the Execute Query button on the Query Analyzer toolbar to execute the query.



Execute Query button

The Query Analyzer displays the results in the Grids pane.

Scripting Objects

The available scripts and the objects to which they apply are shown in Table 21-2. We’ll be looking at most of these scripts in the next lesson about creating and maintaining database objects.

Script Command	Applicable Objects
Create	Table, Index, Constraint, Trigger, View, Stored Procedure
Alter	Trigger, View
Drop	Table, Index, Constraint, Trigger, View, Stored Procedure
Select	Table, View
Insert	Table, View
Update	Table, View
Delete	Table, View
Execute	Stored Procedure, Function

Table 21-2. Scripting Options.

Scripts can be written to a new Query window, a script file, or the clipboard (where they can be pasted into an existing Query window). Some scripts, such as the function execution scripts, use replaceable parameters. The Query Analyzer provides a dialog box to make it easy to replace these parameters with the appropriate values.

Script a SELECT statement

1. Expand the User Tables folder of the Aromatherapy database in the Object Browser.
2. Right-click the dbo.OilProperties table, point to Script Object To New Window As, and choose Select.

The Query Analyzer opens a new Query window with the SELECT statement.



---

TIP:
The Object Browser creates the SELECT statement on a single line. You can reformat it to improve readability as shown in the illustration.

---

3. Click the Execute Query button on the Query Analyzer toolbar to execute the query.



Execute Query button

The Query Analyzer displays the results in the Grids Pane.



4. When you’ve finished with the query, close the Query window.

Script a function

1. Expand the String Functions folder within the Common Objects folder.



2. Right-click the LEFT function, point to Script Object To New Window As, and choose Execute.

The Query Analyzer opens a new Query window with a SELECT statement, including the function.



3. Choose Replace Template Parameters from the Edit menu.

The Query Analyzer opens the Replace Template Parameters dialog box.



4. Enter 'Test Expression' as the value for the character_expression parameter, and 4 as the value of the integer_expression parameter.



5. Click Replace All.

The Query Analyzer replaces the parameters in the query.



6. Click the Execute Query button on the Query Analyzer toolbar to execute the query.



Execute Query button

The Query Analyzer displays the results in the Grids Pane.

To	Do this	Button
Start the Query Analyzer	Click the Start button on the task bar. Point to Programs and then to Microsoft SQL Server, and click Query Analyzer.
Select a database with which to work	Select the new database from the combo box on the Query Analyzer toolbar.
Execute a Transact-SQL statement in the Query window	Type the statement in the Editor Pane, and click the Execute Query button on the Query Analyzer toolbar.
Create an SQL script	Enter the Transact-SQL statements in the Query window, and click the Save button on the Query Analyzer toolbar.
Load an SQL script	Click the Open button on the Query Analyzer, and select the script in the Open Script dialog box.
Open a table or view in the Object Browser	Right-click the table or view in the Object Browser, and select Open.
Add an object to the Editor Pane from the Object Browser	Drag the object from the Object Browser, and drop it in the de- sired position in the Editor Pane.
Script an object from the Object Browser	Right-click the object in the Object Browser, point to Script Object to <destination> As, and select the type of script desired.