Sams Teach Yourself Transact-SQL in 21:Retrieving Data

Now Back to the Simplest Query

The more common way to use SELECT is to request data from a SQL Server table. In this next example, the server returns the titles for all books in the titles table:

     select   title
     from     titles

     title
     --------------------------------------------------------------------
     But Is It User Friendly?
     Computer Phobic AND  Non-Phobic  Individuals:  Behavior  Variations
     Cooking with Computers: Surreptitious  Balance Sheets
     Emotional Security:  A  New Algorithm
     Fifty  Years in Buckingham Palace Kitchens
     Is Anger the Enemy?
     Life Without Fear
     Net Etiquette
     Onions,  Leeks, and Garlic: Cooking Secrets of the Mediterranean
     Prolonged Data Deprivation: Four  Case Studies
     Secrets of Silicon Valley
     Silicon Valley Gastronomic Treats
     Straight Talk About Computers
     Sushi, Anyone?
     The Busy Executive’s  Database  Guide
     The Gourmet Microwave
     The Psychology of Computer Cooking
     You Can Combat Computer Stress!

     (18 row(s) affected)

The SELECT statement in this example is composed of two parts: the column list, which comes after the word SELECT, and the table list, which comes after the word FROM. The result set contains one row for every row contained in the titles table.

The ISQL/w Interface

You should have ISQL/w loaded and ready to go on your computer. (If not, refer to the section “Getting Connected” in Day 1.) The ISQL/w interface is an MDI interface, which means that it can contain many “child” windows inside the main “parent” window. Many Windows applications, such as Microsoft Word and Excel, are also MDI applications.

In ISQL/w, each window represents a different connection to the server. These connections can be to the same server or to different servers. Request a new window by selecting File | Connect.

Figure 2.1 shows the ISQL/w program. Across the top of the window is a tabbed interface that allows access to three different types of information. MS SQL 6.0 also has a Showplan tab. It turned out that nobody really liked or used that tab, though, so in 6.5 there are only three tabs.

Figure 2.1. The ISQL/w program from Microsoft. Query Pane pictured.

The Query window, shown here, is where you type your questions to the server. To execute the query, press Ctrl+E or click the green arrow in the upper-right corner. (Once I get going, I use the keyboard to do as much as possible.)

I prefer to have the interface switch to the Results tab once I execute the query so that I can see the results. This is a checkbox option under File | Configure | Switch to Results Tab on Execute. If this irritates you, turn it off. A picture of the ISQL/w program displaying the results of my query is in Figure 2.2.

Figure 2.2. ISQL/w with Results pane pictured.

Note: Some students tell me that, from time to time, they accidentally start typing a new query on the Results tab. When they attempt to execute that query, the last query they ran gets executed again, and the Results tab is cleared and filled with the last query’s results. This can be a little confusing the first time you do it.
Be sure to switch back to the Query tab before altering your query. You can do this by clicking the pane or by using Alt+Y.

Once you read the results, you’ll probably want to change the query somehow. To get back to the original query, click the Query tab. If, however, you want to keep the query and the results of the query around for a bit, you can add a new set of tabs to this window by selecting File | New from the menu or by pressing Ctrl+N. You can switch among your queries and their corresponding result sets by clicking the Query drop-down list box, as shown in Figure 2.3.

Figure 2.3. Executed queries appear in this drop-down list box.

The drawback to adding a new set of tabs is that the interface will also open a new connection to the server. Many installations limit the maximum number of concurrent connections to the server and base their licensing fees to Microsoft on this number. As a result, companies like to buy as few connection licenses as they can. If you open 50 different Query windows, somebody at your site is likely to pay you an angry visit. Every site is different, but if you keep your connections to five or less, everyone should be happy.

This is different than opening a new window because ISQL/w doesn’t ask you to log in again. Since it doesn’t ask you to log in again, those new connections are being opened transparently—a potentially dangerous behavior.

Anyway, back to the interface. The third tab, Statistics I/O, needs to be turned on before it shows anything. This tab is useful for understanding the performance impact your queries have on the server. It measures logical I/O, which counts the number of times SQL Server reads a 2048-byte page of information from data cache; physical I/O, which counts the number of times SQL Server copies a 2048-byte page from disk to data cache; and Scan counts, which counts the number of times that a particular table is read from start to end.

I talk more about performance issues later in the book. Once we get there, you can use the Statistics I/O tab to benchmark the effects of queries you run against the server.

Tip: One last tip about using ISQL/w: if you do not want to execute all the SQL on the Query tab, use the mouse or Shift plus an arrow key to highlight the SQL you want to run; then press Ctrl+E or click the green arrow.

Table of Contents

Используются технологии uCoz