Microsoft SQL Server 2000 Programming Step by Step

Read an Excerpt

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

In this lesson you�ll learn how to:

• 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

Although it�s possible to create and execute queries and other Transact-SQL statements in the Enterprise Manager, its greatest strength is as a tool for database administration. The Query Analyzer, on the other hand, is primarily a programming tool.

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

You can start the Query Analyzer from within the Enterprise Manager or from the Windows Start menu. When you start the Query Analyzer from the Enterprise Manager, the Query Analyzer will transfer the connection information from the Enterprise Manager: if you are connected to a server, the Query Analyzer will connect to that server, and if you have a database selected, the Query Analyzer will select that database.

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.

(Image Unavailable)

2. Select SQL Query Analyzer from the Tools menu.

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

(Image Unavailable)

---

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 (Image Unavailable)

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

(Image Unavailable)

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.

---

(Image Unavailable)

Selecting a Database

The Query Analyzer will use the currently selected database to resolve references in queries and other Transact-SQL statements. Its toolbar, shown in Figure 21-1, contains a combo box showing the database that is currently selected. You can switch to a different database by selecting its name in the toolbar or from the Query menu.

Figure 21-1. The Query Analyzer toolbar displays the currently selected database. (Image unavailable)

Selecting a database using the toolbar

1. Select Northwind in the toolbar combo box.

(Image Unavailable)

Select a database from the Query menu

1. Select Change Database in the Query menu.

The Query Analyzer displays the Select Database dialog box.

(Image Unavailable)

---

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.

(Image Unavailable)

3. Click OK.

The Query Analyzer selects the Aromatherapy database.

Using the Query Window

The Query Analyzer initially displays two windows, the Object Browser and the Query window. When you first open the Query Analyzer, a single Query window is opened, but you can open a new window at any time by clicking the New Query button on the Query Analyzer toolbar.

New Query button (Image Unavailable)

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

The simplest way to use the Query window is to type in an SQL statement, just as you would in the SQL Pane of the Query Designer. Unlike the Query Designer, however, the Query Analyzer�s Query window provides some extra assistance by color coding the Transact-SQL statements you enter. Table 21-1 shows the colors the Query window uses.

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.

(Image Unavailable)

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

Execute Query button (Image Unavailable)

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.

(Image Unavailable)

4. Select the Messages tab.

The Query window displays the messages generated by the query.

(Image Unavailable)

Display query results in a separate tab

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

The Query Analyzer displays the Options dialog box.

(Image Unavailable)

2. Select the Editor tab.

(Image Unavailable)

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

(Image Unavailable)

4. Click OK.

The Query Analyzer changes the display of the Query window.

(Image Unavailable)

Using SQL Scripts

A script is a set of Transact-SQL statements stored in a file. Scripts are most often used to keep a permanent record of the commands used to create and populate database objects. Because the scripts are stored in text files, rather than in the database, they can be used to re-create the database on different servers. (SQL Server actually uses scripts to create the Pubs and Northwind sample databases.)

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.

(Image Unavailable)

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

Save button (Image unavailable)

The Query Analyzer displays the Save Query dialog box.

(Image Unavailable)

---

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.

(Image Unavailable)

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 (Image unavailable)

The Query Analyzer opens a new, empty Query window.

(Image Unavailable)

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

Open button (Image unavailable)

The Query Analyzer displays the Open Query File dialog box.

(Image Unavailable)

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

(Image Unavailable)

4. Click Open.

The Query Analyzer displays the script in the Query window.

(Image Unavailable)

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

Execute Query button (Image unavailable)

The Query Analyzer displays the results in the Grids tab.

(Image Unavailable)

6. Close the script window.

Using the Object Browser

The Object Browser is contained in the pane on the left side of the Query Analyzer window. Just as the Console Tree of the Enterprise Manager provides a hierarchical view of the objects in a SQL Server installation, the Objects tab of the Object Browser displays a hierarchical view of the databases in the server to which you�re connected, as well as other common objects used in creating Transact-SQL programs.

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. (Image unavailable)

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.

(Image unavailable)

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

When you right-click a table or view in the Object Browser and select Open, the Query Analyzer displays the rows in the table (or the results of the view�s SELECT statement) in the Open Table window.

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.

(Image Unavailable)

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

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

(Image Unavailable)

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.

(Image Unavailable)

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

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

(Image Unavailable)

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

Adding Objects to the Editor Pane

One of the simplest and most convenient capabilities of the Object Browser is its support for drag-and-drop. Whenever you need to specify an object listed in the Object Browser, simply drag it into position in the Editor Pane of the Query window, and its name will be pasted into the editor window.

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 (Image unavailable)

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.

(Image Unavailable)

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.

(Image Unavailable)

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

Execute Query button (Image unavailable)

The Query Analyzer displays the results in the Grids Pane.

(Image Unavailable)

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

(Image Unavailable)

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

(Image Unavailable)

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.

(Image Unavailable)

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

Execute Query button (Image unavailable)

The Query Analyzer displays the results in the Grids pane.

(Image Unavailable)

Scripting Objects

Scripting is a more sophisticated version of the drag-and-drop functionality of the Object Browser. Scripting creates a complete Transact-SQL statement and is available from the context menus of most objects. Not all script types are applicable to all object types, and some types, such as function parameters or columns, have no scripting available at all.

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.

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.

(Image Unavailable)

---

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 (Image unavailable)

The Query Analyzer displays the results in the Grids Pane.

(Image Unavailable)

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.

(Image Unavailable)

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.

(Image Unavailable)

3. Choose Replace Template Parameters from the Edit menu.

The Query Analyzer opens the Replace Template Parameters dialog box.

(Image Unavailable)

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

(Image Unavailable)

5. Click Replace All.

The Query Analyzer replaces the parameters in the query.

(Image Unavailable)

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

Execute Query button (Image unavailable)

The Query Analyzer displays the results in the Grids Pane.

(Image Unavailable)

Lesson 21 Quick Reference

Read More