The Query Analyzer Workplace

When you run SQL Query Analyzer, you will get a connection form, as you can see in Figure B.3, where you can select which server or server instance to connect to, and which authentication mode to use. If you selected SQL Server Authentication mode, you must provide a valid login and password to connect.

Initially, the Server drop-down list will not contain any entries. The server name will be added to the list only after successful connection to a server.

When connected, SQL Query Analyzer will show a workplace as shown in Figure B.4.

As in other Windows applications, you can see some common elements such as the menu bar, the toolbar, and the status bar.

SQL Query Analyzer is a Multi Document Interface (MDI) application where every connection to SQL Server uses its own child window, inside the parent window. You can select the connection window by using the application window menu as usual, and you can arrange the windows in the common ways: Cascade, Tile Horizontally, and Tile Vertically.

The Editor Pane

The Editor pane of a connection window is a text editor you can use to edit your queries and SQL statements. These statements can come from different sources as follows:

• Scripts written directly in the Editor pane

• Saved SQL script files you can retrieve in the Editor pane for further editing

• Scripts produced from the Object Browser and Object Search, as you will see later

• Predesigned templates you can reuse to speed up and facilitate the process of writing code

This is a code-oriented text editor with many features to help you write SQL language code. You can find information about these features in Books Online, but it is interesting to pay attention to the following points:

• The text is color coded to differentiate between keywords, system objects and system stored procedures, operators, comments, and so on.

• You can use shortcuts to speed the editing process. You can find the full shortcut list in the topic "SQL Query Analyzer Keyboard Shortcuts" in Books Online, but the more frequently used are in Table B.1.

• It is possible to select the font, tab length, and other options, as you can see in Figures B.13 and B.15 later in this appendix.

• You can select which part of the code to execute by selecting the block either with the mouse or with the keyboard combining the Shift key with the navigation keys.

The Object Browser

Writing queries requires precise knowledge of database objects definition and complete syntax of statements, functions, and stored procedures. Developers can use the SQL Query Analyzer Object Browser to get information about database objects. Moreover, Object Browser is a dynamic tool with embedded scripting capabilities that can greatly improve your coding productivity.

As you can see in Figure B.5, Object Browser has two main sections: the server structure, with information about databases and database object of the server that the user is connected to, and the common objects, with information about system functions and data types.

As you can see in Figure B.6, you can right-click any object to see a context menu from where you can create a script of that object either to the Clipboard, a new window, or a file. Depending on the object type, it is possible to script different statements. In the example shown in Figure B.6, a user table, the available statements are CREATE, DROP, SELECT, INSERT, UPDATE, and DELETE. For stored procedures, you can choose to script the CREATE, ALTER, DROP, and EXECUTE statements.

In Figure B.7, you can see how the Object Browser shows object dependencies, as well as definitions of columns and parameters. The Object Browser enables the user to drag and drop any object's name to the Editor Pane, reducing the likelihood of misspelling an object name.

An interesting feature in SQL Server 2000 is the possibility of defining extended properties for databases and database objects. From the Object Browser it is possible to edit them using the context menu, as you can see in Figure B.8.

The Object Search

Working with several databases, and hundreds or thousands of objects, makes it difficult to find specific objects in Object Browser. Using the Object Search you can efficiently search for any object on any database.

Figure B.9 shows the Object Search window. You can use wildcards in the Object name field, as seen in Figure B.9. Searching for ___help* means any three characters followed by the word help followed by any string. Other combinations, such as *sys* sys* or authors, are valid as well.

You can search for objects with specific extended properties values.

The Results Pane

The lower section of every connection's window, below the Editor pane, is the Results pane, in which SQL Query Analyzer shows the results of the queries and any output message from SQL Server.

SQL Query Analyzer can show results in Text or Grid, in a spreadsheetlike format. It is possible to send results directly to a file, in which case the Results pane shows only SQL Server messages and confirmation of the file write operation as shown in Listing B.1.

To send results to a file, select the menu Query—Results to File. With this setting, whenever you execute a query, SQL Query Analyzer will prompt you to provide a filename, and the results will be sent to the selected file. To test how SQL Query Analyzer sends results to a file, make sure the menu Query—Results to File is checked and execute the example of Listing B.1.

Code Listing B.1. The Results of a Query Can Be Sent to a File

						
USE Northwind
GO


SELECT *
FROM Products
(77 row(s) affected)
The following file has been saved successfully:
C:WINNTProfilesAdministratorMy Documentsfn.rpt 14242 bytes

In Figure B.10, you can see the same results in text, and in Figure B.11 in grid.