Retrieving Data with Query Analyzer
The SQL Server Query Analyzer is your primary interface for executing T-SQL queries and stored procedures. You will be using this utility throughout the rest of your 21 days, and it's a good idea to get comfortable with several of the available options in this tool. For those of you who might have used previous versions of Microsoft SQL Server, it should be noted that for historical reasons, the Query Analyzer is named ISQLW.exe. This was the program name of the older version of the interactive SQL processor (see Figure 1.1). Although this older version was useful, the changes that have been made to the new Query Analyzer in SQL Server 2000 make it a much easier tool to use.
Figure 1.1. The old ISQL SQL processor.
You can run SQL Query Analyzer either from inside the SQL Server Enterprise Manager or directly from the Start menu. You can also run SQL Query Analyzer from the command prompt by executing isqlw.exe itself. Some of the functions and features of Query Analyzer are
Create and execute queries and other SQL scripts
Quickly create commonly used database objects
Copy existing database objects
Execute stored procedures without knowing the parameters
Debug stored procedures
Debug query performance problems
Locate objects within databases or view and work with objects
Insert, update, or delete rows in a table
Create keyboard shortcuts for frequently used queries
Installing the Query Analyzer
Installing the Query Analyzer is actually done automatically during the full installation of the SQL Server product. However, if you want to use the Query Analyzer on a computer other than the one where SQL Server was installed, you need to run the SQL Server installation and select the Client Tools Only option (see Figure 1.2).
Figure 1.2. Installing the Query Analyzer on a client PC.
After you select the installation option, click Next to select the components you want to install on your computer (see Figure 1.3).
Figure 1.3. Selecting the components of SQL Server 2000 for installation.
Of the components available, the ones you want to select are the Enterprise Manager, Profiler, and the Query Analyzer. After you have made your selections, click Next to start copying the files (see Figure 1.4).
Figure 1.4. The SQL Server 2000 installation in progress.
After the installation is complete, you will find the Query Analyzer on the SQL Server menu list, as shown in Figure 1.5.
Figure 1.5. Finding the Query Analyzer start icon in the SQL Server menu list.
When the install is complete, you are ready to start using the Query Analyzer.
Logging In to and Changing the Database
Now that you have the Query Analyzer ready to go, let's start the program by selecting it from the SQL Server menu list. After you click OK, you are logged in to the appropriate instance of SQL Server (this is assuming that you entered in a correct login ID and password). You will then see the main work area of the Query Analyzer, as shown in Figure 1.6.
Figure 1.6. The SQL Server Query Analyzer main work area.
Note
Login IDs are created and maintained by the SQL Server 2000 administrator. If you are using SQL Server 2000 at work, you should contact this administrator for a login account.
If you want to learn more about the management of SQL Server 2000, which includes the administration of user ID's and passwords, refer to Sams Teach Yourself SQL Server 2000 in 21 Days.
If there is a problem with your login, you might see a dialog similar to Figure 1.7. Usually you will see this if you've mistyped your password. Simply try again. You should also verify that you have entered your login ID correctly. If you've changed the password, be sure to type in the newest one.
Figure 1.7. SQL Server notifying you of a login failure.
Figure 1.8 shows another error that you might see. If you do get this error, there are several things to look for to correct it. First, verify that the SQL Server service is running on the computer to which you are trying to log in. If it is, the problem might be with your client setup. Otherwise, make sure that you type in the server name correctly. To see a list of servers on the network, rather than type one in, click the three dots next to the SQL Server name box to display the list of active SQL Servers that are on your network.
Figure 1.8. Network connectivity error message.
Don't be alarmed if your server doesn't show up; you can still connect to it by typing in its computer name. In this case, I tried to connect to a server named Oracle, which doesn't exist on my network.
Figure 1.9 shows the empty Query Analyzer workspace. You should be able to see that there are really two connections open. Each connection that you open has a title bar that displays the following:
Figure 1.9. Seeing multiple connections in a single instance of the Query Analyzer.
Which computer you are logged in to
What database you are currently using
Your login
The title of any query you have opened
As you might have guessed by now, having this information in the title bar when you have many open connections can be extremely handy. So, back in Figure 1.9, you see both connections are open using the Login ID 'SA', which is the default login for SQL Server.
Caution
If you are using SQL Server 2000 at work, this login might not work because the administrator probably changed the password. Check with the administrator for the login to use.
Both are connected to my computer, LOWELL1, in the master database, and each one is connected to the same instance. There's no reason they couldn't be connected to different database instances.
The Query Toolbar
You can start running T-SQL queries by selecting the New Query button (leftmost button on the toolbar in Figure 1.10). Each new query window that you open is a separate connection to SQL Server; if you open too many, you might be wasting resources on your SQL Server.
Figure 1.10. The Query Analyzer toolbar.
If you select the second button (which looks like a standard Windows File Open button), it will open a standard dialog box to find saved SQL scripts (which, by default, have a .SQL extension). The next button, the Save Query/Result button, will either save the text in your query window or, if you have run a query and clicked in the Results window, save the results of a query you have run. The next button loads a "template" SQL query so that you can quickly develop variations of work you've done before. The next several buttons have the standard Windows functionality.
The Execute Mode button is next, and it's pretty powerful. When you click this button, you are presented with a drop-down list of options that determine how and where your results will appear when you run a query. The default option, Results in Grid, is easier to read because many names in SQL Server 2000 can be 128 characters long. The default display option will usually pad the text to the complete 128 characters with spaces if the names aren't fully used. The Results in Grid option typically leaves enough space only for the widest data column to be displayed. You can also switch to Results in Text, which can be nice when you want to look at all that long text. You can even automatically have the results of your queries routed directly to a file. These options are shown in Figure 1.11.
Figure 1.11. The Query mode selection drop-down list.
The next option on the menu shown in Figure 1.11 is Show Execution Plan. This option shows you the methods and indexes SQL Server will use to find the data you requested in your query. After you have logged in to the server, you then need to select the database with which to work. A drop-down box provides a list of the installed databases on the server to which you are connected. If you change the value here, the SQL script you run will use this database unless you specify a different database in the SQL script itself with the USE command. When you do specify a different database in your SQL, the changed database will be reflected in this list box.
Executing Your First Query
The easiest way to see how this all works is by using the Query Analyzer. Click the New Query button to open a new query window. Now type the following statement into the window as shown in Figure 1.12:
Figure 1.12. Entering a SQL statement into the query window.
Use Northwind Select * From Employees
This query is pretty easy after you understand what the commands mean. The first statement, USE Northwind, tells the server to switch to the Northwind database, which is a sample database that is installed along with SQL Server 2000. The second statement, Select * From Employees, requests that SQL Server return all the rows and columns of data from the table or view named Employees (and because you explicitly switched to the Northwind database, you know this table is in Northwind). So, to translate these statements into English, the following is what these statements mean:
Switch to the Northwind database Return all the data about the employees
After you type the two preceding commands, you can verify that they've been entered correctly. Notice that several other buttons on the toolbar are now available. Click the blue check mark on the toolbar to verify that everything is typed in correctly. This will check that the SQL you entered is syntactically correct. If everything is okay, you will see the following:
The command(s) completed successfully.
Understanding SELECT and FROM
The keywords SELECT and FROM are used in SQL programming more than any other words. They aren't as interesting as CREATE or as ruthless as DROP, but they are the backbone for any conversation you hope to have with the computer when asking for data from the database. Of course, data retrieval is the reason that you have stored data in the database in the first place. What follows is a brief discussion of the SELECT keyword. Almost every SQL statement you will code will start with SELECT as shown:
Select <column name(s)>
The basic SELECT statement is very simple. However, SELECT doesn't work alone, as you saw earlier when entering your first SQL statement. If you typed in this SQL statement
Select EmployeeID
you would get the following error message:
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'EmployeeID'.
This message tells you that SQL Server does not know where to find the column name EmployeeID. You can probably guess that something is missing. That something is the FROM clause:
From <table>
Together, the SELECT and FROM clauses begin to unlock the power that is contained in your database.
Note
I have used a couple of terms that you are probably wondering about. SQL has three main elements: keywords, clauses, and statements. SQL keywords refer to individual SQL elements, such as SELECT and FROM. A clause is a part of a SQL statement; for instance, SELECT column1, column3 is a clause. SQL clauses combine to form a complete SQL statement. For example, your first SQL statement was the combination of the SELECT and FROM clauses, which are themselves a combination of keywords.
Finally, the asterisk (*) that you used in your first SQL statement tells the database to return all the columns associated with the table that you listed in the FROM clause.
Using the Query Analyzer Editor
Another thing you might have noticed (see Figure 1.12) as you entered the earlier SQL statement is that some of the words you typed in have changed color. The Query Analyzer editor changes the color of the code entered based on the category the code word is found in. Table 1.1 lists the different colors that are used.
| Color | Category |
|---|---|
| Red | Character string |
| Dark Red | Stored procedure |
| Green | System table |
| Dark Green | Comment |
| Magenta | System function |
| Blue | Keyword |
| Gray | Operator |
Note
You can change these defaults by selecting the Fonts tab on the Query Analyzer Options dialog box.
Now, to actually run the query, click the Execute Query button. You can also select Query, Execute from the menu, press F5 on your keyboard, or press Alt+E. After you run the query, the results pane appears with all the information about the employees in the Northwind database, as shown in Figure 1.13.
Figure 1.13. Displaying your query results.
Notice that while your query was executing, the red Stop button became available. You can cancel a query while it's still running by clicking on the Stop button. However, your queries should run so fast that you will never get a chance to cancel them—at least until you work with a larger production database that contains many entries.
Now, let's go back to the text and grid options that we discussed in the previous section. Click the current mode button on the toolbar and switch to the Results in Text option. You can also press Ctrl+T to set this option. Now, rerun the query and examine your results pane. Notice that all the same tabs are listed, but your query results are formatted a bit differently.
Using Query Analyzer to Learn About Tables
Besides using Query Analyzer to run queries, you can also use it to view information about the tables in the database, such as the columns in the table or indexes that are defined. The Object Browser, when turned on, enables you to easily browse all database objects for each connection that you have open. It's much more powerful than it first appears. You can drag and drop any object into your query window. That's pretty useful, but try clicking on the object and dragging it into your query window as shown in Figure 1.14. In addition, right-clicking on an object enables you to choose from different SQL statement options, such as writing a create table script or a select query script.
Figure 1.14. Dragging and dropping objects from the Object Browser.
Another useful option is the object search. Click it to bring up the screen that is shown in Figure 1.15. If you can't remember where the table is that you had the salary column in, just use this feature and the Query Analyzer will search for it and the highlight it if found. This can be very useful when working on a large or complex system, or just a system you haven't seen in a while.
Figure 1.15. Using the Object Search dialog.
Well, you've gotten a good look at the Query Analyzer that you will be using for the remainder of this book. You will quickly see how important a tool the Query Analyzer really is.