Special Edition Using Microsoft SQL Server 6.5 Second Edition

Previous chapterNext chapterContents


- 7 -
Retrieving Data with Transact-SQL

The SELECT statement is used to choose the data that is be displayed from database tables.
You can write queries that specify selected table rows.
You can manipulate the rows of a table, including changing their presentation order.
By using embedded queries, or sub-select statements, you can fine-tune the results returned to your application.

You usually don't want to access and display all the data stored in a database in each query or report. You may want some, but not all, of the rows and columns of data. Although you can access all the information, you probably don't need to display all rows and columns because it's too much information to examine at one time.

In previous chapters, you learned that the information stored in a relational database is always accessed as a table. If you reference a printed table of information, you usually don't read all the rows and columns. You probably look at only part of the table to obtain the information you need. The table exists in a printed form only because it's a traditional way of storing information.

If you can reconsider your requests for information from the database, you can start to eliminate the queries that produce unwanted or unneeded results. In these cases, you can produce output that presents exactly what is needed and nothing more.

Setting Up a Demonstration Database and Table

The data stored on your database, or the disk of your computer system, is analogous to a set of printed tables. You don't need to retrieve an entire table when you issue queries to display information from the database. You construct a query using a Transact-SQL statement, which returns only the relevant portion of the column or rows of your database tables.

Table 7.1 shows an example table structure and its data, which will be used for several examples in this chapter. For information on creating tables, see Chapter 5, "Creating Devices, Databases, and Transaction Logs" and Chapter 6, "Creating Database Tables and Using Datatypes."

You can find a sample script that creates a small database, this table, and inserts the appropriate values in the table on the CD accompanying this book. The file name for the script is chap6-1.sql.

Table 7.1 A Table Containing 12 Rows

Name Department Badge
Bob Smith SALES 1834
Fred Sanders SALES 1051
Stan Humphries Field Service 3211
Fred Stanhope Field Service 6732
Sue Sommers Logistics 4411
Lance Finepoint Library 5522
Mark McGuire Field Service 1997
Sally Springer Sales 9998
Ludmilla Valencia Software 7773
Barbara Lint Field Service 8883
Jeffrey Vickers Mailroom 8005
Jim Walker Unit Manager 7779

The table is limited to 12 rows to make it easier to work with the examples. The typical size of a table for a production database might have more columns of information and nearly always has more rows of information. The size of the table won't make any difference in showing the operation of Transact-SQL statements. The statements work identically, regardless of the size of the tables operated on. The examples in this chapter are easier to understand if a small number of rows and columns are present in the table used to show SQL operations.

Retrieving Data from a Table with SELECT

Your queries of a database are a selection process that narrows the information retrieved from the database to those rows that fit your criteria. As you've seen earlier in this chapter and in Chapter 2, which covered database design, your goal as you work with tables should always be to return only the information needed to fulfill the user's request. If you retrieve any more information, the user is required to wait for a longer period of time than is necessary. Any less information than is needed results in additional queries against the database. This modeling of sets of data is always a balancing act that requires continued refinement.

The SQL SELECT statement is used for the selection process. The various parts of a SELECT statement target the data in the database tables. The complete syntax of the SELECT statement is shown in Listing 7.1.

Listing 7.1 Syntax of the SELECT Statement

SELECT [ALL | DISTINCT] select_list
      [INTO [new_table_name]]
[FROM {table_name | view_name}[(optimizer_hints)]
      [[, {table_name2 | view_name2}[(optimizer_hints)]
      [..., {table_name16 | view_name16}[(optimizer_hints)]]]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause]
[COMPUTE clause]
[FOR BROWSE]

A SELECT statement is like a filter superimposed on a database table. Using SQL keywords, the database is narrowed to target the columns and rows that are to be retrieved in a query. In the filter comparison, shown in Figure 7.1, the widest part of the filter selects all the rows and columns of a database for retrieval. The narrowest portion of the filter indicates selection of the smallest cross-section of data that can be retrieved from a table, a single row with only one column.

FIG. 7.1
SELECT
queries are used to target specific columns and rows of a database.

Most SQL queries retrieve rows and columns that are narrower than the entire table, represented by the base of the filter in the figure, but wider than the single row and column, as shown in the point of the triangle opposite the triangle's base. You'll typically need to retrieve more than a single row and column, but less than all the rows and columns of the database.

This is where more complicated SELECT statements are used. As you're going to see in this, and the next few chapters, the numbers and types of operations you can perform on your database tables can range from simple selects to complicated, server-resolved queries that provide precisely the information you need.

As you read through the different things you can do with SQL, keep in mind that you can, and should, try it out using ISQL or ISQL/W against your own tables. That's the quickest way to learn, the fastest way to get started.

Selecting a Query Tool

There are several different ways you can enter queries to be sent to SQL Server. Although it's beyond the scope and intent of this book to discuss development languages at length, it's likely that, during your testing and experimentation with SQL Server, you'll use one of at least three different tools:

Initiating the query, sending it to the server, and viewing the results of your query, are all covered in the next sections and these sections serve as the foundation for your use of these tools for the balance of the book.


You can also use Microsoft Query to send queries to, and receive results from, SQL Server. Another alternative is to use Access to work with SQL Server. For more information about this approach, see Chapter 26, "Upsizing Microsoft Office 97 Applications to SQL Server."

Using the Enterprise Manager Query Tool

SQL Enterprise Manager is an obvious choice with which to experiment for many different reasons. The primary benefit of this tool is the fact that you can use the other functions of the Enterprise Manager to help you work with table structures; other servers and other features of SQL Server that might not relate to, or be controllable by, your query.

Running queries in Enterprise Manager is much like working with ISQL for Windows. The dialog box you use is identical and offers the same functionality as ISQL/w. To use the Query tool from SQL Enterprise Manager, follow these steps:

1. Select the server from the list of available, registered servers.

2. Open the Query window by choosing Tools, Query Analyzer, or by clicking the appropriate toolbar button.

3. From the resulting dialog box, select the database with which you want to work. If you had highlighted a database prior to selecting the tool, that database will already be selected. See Figure 7.2 for an example.

FIG. 7.2
Be sure to select the database you want to work with prior to entering your query.

4. You enter your query in the text window portion of the Query tab; then press Ctrl+E, press the green play button, or select Query, Execute from the menu.

5. You'll be automatically taken to the Results tab, where you'll see the incoming results from your query. These results are displayed as they are received, so if your query requires processing time on the server for each row, you're likely to see as little information as a single line as each appropriate result is determined.


NOTE: With ISQL in its command-line version, you must enter a GO statement after each batch you want to process against the server. Although you can enter these in the Query Tool, it's not necessary. When you select Execute, as in the preceding step 4, all of the contents of the query window are sent to SQL Server for processing.

The exception to this is if you have highlighted one or more sections of the query window, only those highlighted portions are sent to SQL Server. This is a good way to test a specific statement out of a batch of statements, and a good tool for debugging your queries.


Using ISQL for Windows

If you're looking for the convenience of using the Query Tool from Enterprise Manager without having to work through the entire Enterprise Manager environment, then ISQL for Windows is perfect. When you start ISQL/W, you are prompted to sign in to SQL server, provide a user ID, password, and, if needed, a server to use (see Figure 7.3).

FIG. 7.3
Because ISQL/W doesn't store usernames and passwords as Enterprise Manager does when you register a server, you need to sign in each time you access the server for a new session.

After you sign in, you are presented with the query window with three tabs. These tabs are for the query, results, and statistics. They enable you to profile what it takes for SQL Server to fulfill your request.

Note that you must select the database you want to work with in the DB listbox. If you don't you receive a message, like that shown next, indicating that the object you're looking for does not exist.

Msg 208, Level 16, State 1
Invalid object name `feedback'.

Such a message probably means that you don't have the right database selected for your query, or that you don't have the right name for your table. Either way, make sure you're indicating an object that is currently in scope for your session.

As with Enterprise Manager, you execute your query after you've entered it by pressing Ctrl+E, pressing the green play button, or by selecting Query, Execute from the menu. When you do, the Results tab is automatically selected and you can see what happens as SQL Server processes your request.


NOTE: With ISQL in its command-line version, you must enter a GO statement after each batch you want to process against the server. Although you can enter these in the Query Tool, it's not necessary. When you select Execute, as in step 4 above, all of the contents of the query window are sent to SQL Server for processing.

The exception to this is if you have highlighted one or more sections of the query window, only the highlighted portions are sent to SQL Server. This is a good way to test a specific statement out of a batch of statements and a good tool for debugging your queries.


Using ISQL from the Command-Line

The last, most commonly used query tool for accessing SQL Server from the command-line is ISQL. This type of access is especially helpful in cases where you're running a series of scripts as you can create a batch file and run the entire series unattended. It's also helpful as a low-overhead approach to testing your queries.

The simplest way to start ISQL interactively is:

isql -Sservername -Uusername -Ppassword

Provide the server, user name, and password that should be used and you'll be presented with the equivalent to the DOS C> prompt, a 1> prompt, indicating that ISQL is ready and waiting on your input.

From here, you can enter the query you want to run, press Enter, enter a GO statement, and then press Enter. Remember, with the ISQL command-line version, you must enter the GO statement, or the query statements you enter will not be executed.

When you run the query, you'll see any messages about the processing as it occurs. This can be seen in the example shown next and is how you'll find out about any error messages.

1> select * from feedback
2> go
Msq 208, Level 16, State 1, Line 1
Invalid object name 'feedback'
1>

Remember, too, you'll need to use the USE statement to select the database you want to access. For example, the following would produce the results desired from the fragment shown above:

1> use feedback
2> select * from feedback
3> go


NOTE: This example assumes you have a database named feedback and a table within that database, also named feedback.


TIP: You can use the -i option to run ISQL from the command line and pass in a file that contains the SQL statements you want to run. For example,

isql -imysql.sql -Sprimary -Uusername -Ppassword

runs the mysql.sql file and the results are displayed to the monitor as they are generated.


In the next sections, you learn about all of the different portions of the SELECT statement, starting with the basics.

Specifying the Table with FROM (Required Element)

Different parts of the SELECT statement are used to specify the data to be returned from the database. The first part of the selection process occurs when fewer than all the database tables are referenced. You can retrieve data stored in the database separately by referencing some tables, but not others, in a SELECT statement.

A SELECT statement uses the FROM clause to target the tables from which rows and columns are included in a query. The syntax of the FROM clause is as follows:

[FROM {table_name | view_name}[(optimizer_hints)]
[[, {table_name2 | view_name2}[(optimizer_hints)]
[..., {table_name16 | view_name16}[(optimizer_hints)]]]

In the following complete SELECT statement, the FROM clause specifies that the returned data should include only data from the employee table:

SELECT *
FROM employee


NOTE: Note that SELECT * will return all columns without the need to specifically call them out in the query statement.


NOTE: In the examples shown in this chapter, the Transact-SQL keywords used to form clauses are written in uppercase. You can, however, use lowercase keywords.

Remember, however, if you installed Microsoft SQL Server with the default binary sort order, the names of your database objects, including the names of tables and columns, must match in case.


You can also specify multiple tables in the FROM clause, as in the following example:

FROM table_name_1,...,table_name_n

Each table is separated from the names of other tables with a comma. This is a separator used with lists of information in FROM and other Transact-SQL clauses. The list in a FROM clause often specifies multiple tables instead of a single table.

In the following example of a SELECT statement, the FROM clause references the data from two tables:

SELECT *
FROM employee,pay

The Employee and Pay tables are targeted, and all rows and columns are retrieved from these.


NOTE: As you'll see later in the section "Using a Wildcard in the SELECT Clause," using Select * returns all columns from the requested table or tables. This can cause queries that take quite some time to complete. You should avoid using Select * if possible because it does not restrict the returned data set at all.


NOTE: In a relational database, you must provide instructions within the SELECT statement to match the rows from two or more tables together. To learn how to match, or join rows from multiple tables, see Chapter 8, "Adding, Changing, and Deleting Information in Tables."

The SQL query language lets you choose tables from different databases. You can specify the name of the database in which the table is located by inserting the database name to the left of the table name. Next, place a period, the database owner name, and another period, followed by the table you need to work with, as shown in the following example:

database_name.owner.table_name

In the following example, the employee table in the database company and the owner dbo is specified:

SELECT *
FROM company.dbo.employee


NOTE: The DBO keyword specifies the database owner. You can refer to the dbo at any time. SQL will know that you're referring to the owner of the specific database.

In the previous example, the table was created by using the system administrator's account, so the owner is dbo. If you omit the name of the database and owner when you reference a table, SQL Server looks for the table or tables that you specified in the FROM clause in the current database. You must enter the name of the database in which a table was created, along with its owner, to include the rows and columns from tables in different databases.

Specifying Columns with SELECT (Required Element)

As you work with the SELECT statement, keep in mind that you can control the data elements returned in two different manners. First, you can divide the data vertically, limiting the columns that are returned in your results. This is done with the SELECT statement when you indicate the columns you want to have returned.

The other way you can divide your results set is horizontally, controlling which rows qualify for the results set. You use the WHERE clause, shown in coming sections, to divide your tables in this manner.

The columns of values returned from database tables are specified as part of the SELECT clause immediately following the SELECT keyword. One or more of the columns are entered as a list. Each column, like the tables in a FROM clause, is separated by a comma:

SELECT column_name_1,...column_name_n
FROM table_name_1,...table_name_n

In the following code example, the name and badge columns are selected for retrieval. The results are shown in Figure 7.4. This example uses the SQL Windows application utility ISQL/w to perform the retrieval of rows.


Select name, badge 
from employee

FIG. 7.4
SELECT statements, and the different options you can use within them, are the tool you use to limit results sets.


NOTE: The code listings are what should be entered into the query page of an ISQL/w session. Figures, such as Figure 7.4, show what the results should look like when the Execute button is pressed.

In previous chapters, you learned that one of the basic tenets of a relational database is that operations on database tables always return another table, as a results set. The rows and columns of database tables that are targeted for retrieval are always assembled into a temporary table. In most cases, this table is maintained only until the data is provided to the requesting client.

The new temporary table, shown in Figure 7.4, was constructed from the three-column Employee table. According to the SELECT statement, the temporary table targets all rows of the permanent table's three columns and eliminates the second column, Department. The temporary table is deleted after the rows are provided to the requesting client.

Figure 7.5 shows the query, involving the Employee table, performed with the command-line form of ISQL.

FIG. 7.5
Transact SQL produces the same results from a command-line prompt that it does the GUI environment.


TIP: Use the ED command to edit a long statement to be entered at the command line. This invokes the system editor with the previous command entered. After exiting the system editor, it places the edited statement as the next statement.

You can display table columns in a different order than you originally defined. To change the default order for the display of table columns, simply list the names of the columns in the order in which you want the columns displayed. In the following example, the order of display for the columns of the Employee table is reversed from the order in which they were defined (see Figure 7.6).


select badge, department, name
from employee

Changing the order of the displayed columns of a database table is consistent with the characteristics of a relational database. You may remember from Chapter 2 that the access of data from a relational database doesn't depend on the manner in which the data is physically stored. You simply specify the names of the columns in the order in which you want them returned in the SELECT clause of the SELECT statement.


TIP: You can display the same column of a table in multiple places if you need to improve the readability of the table, as in a train schedule.

FIG. 7.6
The SELECT clause determines the order columns are listed.

Using a Wildcard in the SELECT Clause

You can use an asterisk (*) in the SELECT clause to specify all columns for inclusion in the retrieval. The following code shows a query that uses an asterisk to reference all columns of the Employee table with the results shown in Figure 7.7. The name, department, and badge columns are displayed in the query results.

Select * 
from employee


NOTE: Although numerous examples throughout this book show SELECT statements with the asterisk (*) in the SELECT clause, you should always use caution in using the asterisk with production databases.

The asterisk is used in the examples because it's convenient to use to reference all of the columns. In many of the sample queries that use the asterisk, it has little effect on the amount of time it takes to perform the query.

You shouldn't use an asterisk with a production database because you probably need to access only some of the table columns in a query instead of all of them. Eliminating some table columns can dramatically reduce the time it takes to retrieve the rows when several rows are retrieved.

You can specify the column names even if all the columns should be retrieved so that the query is more descriptive. If the query is saved and later in need of revision, the columns and rows that the query retrieves will be easy to determine by reviewing the query.

One additional benefit of indicating the column names is that later, if you add columns to the table, the results from the query will remain constant and will not suddenly include new information.


FIG. 7.7
You can use the asterisk wild card character in the SELECT clause of a SELECT statement to return all elements of the table.

Specifying the Rows of a Table

In the previous examples, all table rows of a database are retrieved. Your goal will often be to retrieve only certain rows. For example, if you have tables that contain millions of rows, you'll probably never execute a query to retrieve all rows from the tables. Every query that you execute specifies a specific results set because it's impractical to retrieve or manipulate all rows in a single query.

The WHERE keyword is used to form a clause that you add to a SELECT statement to specify the rows of a table to be retrieved. A WHERE clause uses the following syntax:

SELECT column_name_n,...column_name_n
FROM table_name_1,...table_name_n
WHERE column_name comparison_operator value

A WHERE clause forms a row selection expression that specifies, as narrowly as possible, the rows that should be included in the query. A SELECT statement that includes a WHERE clause may return a single row or even no rows if none of the rows matches the criteria specified in the SELECT statement.

In the example from the following code, results shown in Figure 7.8, a WHERE clause specifies that only the rows with the sales department are retrieved. All rows that contain the sales department, without regard for the case, are displayed.

select * 
from employee 
where department = "sales" 

FIG. 7.8
The sort order that you defined during the installation of SQL Server determines case-sensitivity.


NOTE: The default sort order is defined as case-insensitive during installation. If you change this after installation, you'll need to reinstall SQL Server.

The following code results, as seen in Figure 7.9, show that the SELECT statement returns a single row because only one row contains the mailroom department:

select *
from employee
where department = "mailroom"

FIG. 7.9
The count message shows "row(s)" but only a single row is included in the results set.

The query result for a row that contains the personnel department retrieves no rows, as shown in the following code and in Figure 7.10. The count line, which displays the number of rows retrieved, is at the bottom of the output window.

A retrieval in which no rows match the criteria of the SELECT statement doesn't return an error message. Instead, the message (0 row(s) affected) is displayed.


NOTE: If you work with query products other than Transact-SQL, you may receive an error message when no rows are retrieved. A query that returns no rows is considered valid by Transact-SQL, SQL 92, as well as other SQL dialects.

select *
from employee
where department = "personnel"






TIP: You can refer to the @@ERROR system symbol, which is called a global variable, to learn whether the previous operation was successful. SQL Server returns a zero (0) to @@ERROR if the previous operation was successful. After you execute a SELECT query that retrieves zero rows, @@ERROR contains a zero (0), indicating that no error occurred.

select @@error

A SELECT statement can be used to display the contents of @@ERROR, so the results are returned as rows retrieved from a table. The dashes (-) are displayed underneath the location of where a column header would appear if a column, rather than @@ERROR, was specified. A count message is also displayed for the one value (0) retrieved from the global variable.


FIG. 7.10
You can enclose the value in the WHERE clause in single or double quotation marks.


For more information on working with @@ERROR, see Chapter 14, "Managing Stored Procedures and Using Flow-Control Statements."

Using Comparison Operators in a WHERE Clause The syntax for the WHERE clause allows the use of a comparison operator following the name of a table column and before a column value. In the earlier examples, only the comparison operator = (equal) was used. Additional comparison operators can be used to retrieve different rows. Table 7.2 lists the comparison operators that you can use in the WHERE clause.

Table 7.2 Comparison Operators

Symbol Meaning
= Equal
!= Not equal
<> Not equal
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
LIKE Equal to value fragment


NOTE: Table 7.2 lists the LIKE keyword as one of the comparison operators. Although LIKE isn't listed as one of the comparison operator symbols in the Microsoft documentation, LIKE is used exactly as a comparison operator. For more information on this operator, see the later section "Using the Comparison Operator LIKE."

The syntax for a WHERE clause that uses a comparison operator is as follows:

SELECT column_name_1,...column_name_n
FROM table_name_1,...table_name_n
WHERE column_name comparison_operator value


NOTE: You can optionally use spaces around the comparison operations. Your query will execute correctly with or without spaces around the comparison operators.

In addition to the = (equal) comparison operator that was used in the preceding section, you can use the <> (not equal) operator.

You can use the not-equal operator to retrieve all rows except those that contain the value to the right of the <> operator. The following code (see results in Figure 7.11), shows a SELECT statement that contains a WHERE clause for all rows from the Employee table except those that contain the sales department.

select *
from employee
where department <> "sales"

FIG. 7.11
You can use the comparison operators <> or != for "not equal. "

The < (less than) comparison operator can be used to retrieve rows that are less than the value specified for the column in the WHERE clause. In the following code, (see results in Figure 7.12), the rows that contain a badge number less than 5000 are retrieved from the employee table.

select *
from employee
where badge < 5000

FIG. 7.12
The results include rows with badge numbers less than 5000.

The > (greater than) comparison operator retrieves rows that contain a value greater than the value used in the WHERE clause. In the following code, rows with badge numbers greater than 8000 are retrieved from the employee table (see Figure 7.13).

select *
from employee
where badge > 8000

FIG. 7.13
The results include rows with badge numbers greater than 8000.

The <= (less than or equal to) comparison operator returns rows that have a value equal to or greater than the value in the WHERE statement. The following code returns rows that contain the value less than or equal to badge number 3211 (see Figure 7.14).

select *
from employee
where badge <= 3211

FIG. 7.14
The results include rows with badge numbers less than or equal to 3211.

The >= (greater than or equal to) comparison operator returns rows that are greater than or equal to the value in the WHERE clause. Comparison operators can be used with columns that contain alphabetic values, as well as numeric values. The following code uses >= in the WHERE clause to retrieve rows that are alphabetically greater than or equal to software for the department column (see Figure 7.15).

select *
from employee
where department >= "software"

FIG. 7.15
The results include rows that contain the software department.


NOTE: When you use comparison operations with columns that are defined as datatypes, such as CHAR or VARCHAR, SQL Server uses the binary representation of all characters including alphabetic characters. For example, an uppercase letter A is stored with a lower binary value than an uppercase B. A character value of B is considered greater than the value of an uppercase A using its binary representation.

For values that are more than a single character, each character is successively compared using the binary representation.


Using the Comparison Operator LIKE The last of the comparison operators is a keyword, instead of one or two special symbols. The LIKE keyword is followed by a value fragment instead of a complete column value. The example query in the following code retrieves all rows that contain a department name beginning with the alphabetic character S (see Figure 7.16). A wildcard character, such as the percent sign (%), can follow the letter S. This wildcard character is used to match any number of characters up to the size of the column, minus the number of characters that precede the percent sign.


TIP: You can also use the % before a value fragment in the WHERE clause of a SELECT statement, such as "%s." You can also use wildcards multiple times. The percentage option tells SQL Server to match anything in these positions.

You can use it to find a word within a complete phrase if needed. For example, "%find this%" could be found within the string "The cow asked where he could find this type of fork on the farm" and would be returned.



CAUTION: Use of the LIKE operator typically results in SQL Server not using the indexes associated with a given table. It tells SQL Server to compare the string you indicate and find any occurrence that matches the wildcard string you provide.

For this reason, it's not recommended that you use this type of search or comparison on large tables. At the very least, be sure to warn users that the wait time may be substantial as the system locates the rows that fit their search criteria.


select *
from employee
where department like "s%"

FIG. 7.16
The query retrieves only the rows that contain a department starting with the letter s.

An underscore (_) is another wildcard that you can use to specify a value fragment. Each underscore used in the specification of a value fragment can match any one character. The example shown in the following code uses four underscores following the S to match any rows containing a column value that begins with an s followed by any four characters (see Figure 7.17). Unlike the example shown in Figure 7.16, the query retrieves only the rows that contain Sales or SALES. It doesn't retrieve the rows that include Software.

select *
from employee
where department like "s____"

FIG. 7.17
You can use the underscore (_) wildcard along with the percent sign (%) in a WHERE clause.

You can use square brackets ([]) as wildcards in a WHERE clause that uses the LIKE comparison operator. The square brackets specify a range of values. In the following code, the brackets are used to specify a range of any upper- or lowercase characters as the first character of the department column (see Figure 7.18).

select *
from employee
where department like "[a-zA-Z]%"

FIG. 7.18
You can use any wildcard combination in the value fragment.

In Figure 7.19, % and [] are combined to specify that the rows for retrieval have any upper- or lowercase letter as their first character, as well as any additional characters up to the width of the column. The department column is wide enough to store 20 characters. Figure 7.20 shows that you can combine wildcards to specify a value fragment.

You can also use a caret (^) after the left bracket to specify a range of values to be excluded from the rows retrieved in a SELECT statement. For example, the SELECT statement, shown in the following code, retrieves all rows from the Employee table except those with first characters that fall within the range F through M (see Figure 7.19).

select *
from employee
where department like "[^F-M]%"

You can use wildcards only in a WHERE clause that uses the LIKE keyword. If you use the asterisk (*), underscore (_), brackets ([]), or caret (^) with any of the other comparison-operator symbols, they're treated as literal column values. For example, the following code contains the same query issued in Figure 7.20, but an equal sign (=) has been substituted for the LIKE query (see Figure 7.20). The identical query with an equal comparison operator instead of LIKE doesn't retrieve any rows.

select *
from employee
where department = "[^F-M]%"

FIG. 7.19
A SELECT statement that excludes a range of values from F through M.

FIG. 7.20
When you use wild cards with comparison operators other than LIKE, they're treated as literal column values.

Selecting Columns and Rows with the WHERE Clause You can retrieve a subset of a table's columns and rows in a SELECT statement by combining the use of specific, called-out columns and the use of a restricting WHERE clause. In the following code, only two columns, name and department, are selected, and only for the rows that contain the "Field Service" value in the department column (see Figure 7.21).

select name, department
from employee
where department = "Field Service"

FIG. 7.21
A SELECT statement can limit both the rows and columns retrieved.

You may remember from Chapter 1 that you can create more than 250 columns in a table and an unlimited number of rows. It's almost always impractical, except for small, simple tables, to retrieve all columns and rows of a table. If you correctly construct your query, you can also reference columns from multiple tables in a single query.

The SELECT clause is descriptive because it invokes a selection operation on a table's rows and columns. Keep in mind the analogy of the data-retrieval triangle introduced earlier in this chapter. The SELECT statement effectively superimposes a triangle over a table to retrieve some, but not all, columns and some, but not all, rows.

Keep in mind the following tips:

Using Boolean Operators and Other Keywords in a WHERE Clause You can use Boolean operators to retrieve table rows that are based on multiple conditions specified in the WHERE clause. Booleans are used the way conjunctions are used in the English language. Boolean operators are used to form multiple row-retrieval criteria. Use Boolean operators to closely control the rows that are retrieved. The syntax for the use of a Boolean is as follows:

SELECT column_name_1,...column_name_n
FROM table_name_1,...table_name_n
WHERE column_name comparison_operator value
Boolean_operator column_name comparison operator

You can use several operators with the Boolean option, each is described in the next sections.

Using the OR Operator The first of the Boolean operators is OR, which you can use to select multiple values for the same column. In the following code, OR is used to form a WHERE clause to retrieve rows containing two column values (see Figure 7.22). Continue to add ORs to the WHERE clause to select additional values for the same column.

select *
from employee
where department = "field service"
or department = "logistics"

FIG. 7.22
You can use any number of ORs with different comparison operators in each statement.

The following query retrieves the rows of the employee table that contain three column values (see Figure 7.23).

select *
from employee
where department = "field service"
or department = "logistics"
or department = "software"

FIG. 7.23
You can use additional ORs to select more than two values from the same column.

You can specify different columns in the WHERE clause of a SELECT statement that uses an OR. The query in the following code retrieves rows that are either members of the Field Service department with any badge number, or have a badge number that's less than 6000 but are members of any department (see Figure 7.24).

select *
from employee
where department = "field service"
or badge < 6000

FIG. 7.24
You can use multiple Boolean operators to specify criteria for the rows to be returned by a SELECT statement.

Using the AND Operator Use the AND Boolean operator if you want the rows returned by the query to meet both comparisons specified in the WHERE clause. In the following code, a query is used to retrieve a row that contains a specific Name and badge combination (see Figure 7.25). If two rows in the table contained Bob Smith, the Boolean AND is used to specify a criterion that requires the row to also contain the badge value 1834. Multiple rows would be returned only if more than one row contained the values Bob Smith and 1834.

select *
from employee
where name = "bob smith"
and badge = 1834

FIG. 7.25
You can also use AND and OR together in a WHERE clause.

Populating one column of the table with unique values allows individual rows to be retrieved. A unique row is returned if one of the specified columns is the column that contains unique values.


NOTE: Defining a column with a unique row or a combination of rows allows individual rows to be retrieved or manipulated. You may remember that SQL Server enables you to store rows that have duplicate values across all table columns. If you allow rows to be individually selected, you establish the capability to reference one table row at a time, if necessary.


See the section in Chapter 6, titled "User-Defined Datatypes."

Using the NOT Operator NOT is an additional Boolean operator that you can use as part of a WHERE clause. Use NOT to specify negation. You use NOT before the columns that will be used in the comparison. For example, if you wanted to select based on a value not equaling another value, you'd use the following statement:

select *
from employee
where not department = "field service" 

This query retrieves all rows of the Employee table that contain any department except Field Service (see Figure 7.26).


TIP: You can use NOT instead of the not-equal comparison operators != and <>. A WHERE clause that uses NOT for negation is visually easier to understand than one that uses != (not equal).

FIG. 7.26
You can use NOT for negation the same way the not-equal comparison operators (!= and <>) are used.

You can also use NOT in a WHERE clause in combination with AND and OR. In the following code, NOT is used to retrieve all rows of the employee table that are members of the Field Service department, except for Mark McGuire (see Figure 7.27).

select *
from employee
where department = "field service"
and not name = "mark mcguire"

FIG. 7.27
You can use OR, as well as AND with the NOT Boolean operator.

Using BETWEEN to Select a Range of Values Although you can use a number of ORs in a WHERE clause to specify the selection of multiple rows, another construction is available in Transact-SQL. You can use the BETWEEN keyword with AND to specify a range of column values to be retrieved. In the following code, a WHERE clause that includes BETWEEN is used to specify a range of badge values to be retrieved from the Employee table (see Figure 7.28). Use BETWEEN after the name of the column, followed by one end of the range of values, the AND keyword, and the other end of the range of values.

select *
from employee
where badge between 2000 and 7000

FIG. 7.28
The table doesn't have to contain rows that are identical to the column values used to specify the range of values referenced by BETWEEN.

The actual numbers that form the range of values for retrieval don't need to be stored in the table. For example, in Figure 7.28, badge numbers 2000 and 7000 don't have to be stored in the table. Those numbers simply specify a range. Also, a successful query, one that returns an error code of 0, can return zero rows within the range specified by the WHERE clause that contains a BETWEEN. No rows need to be stored in the table for the query to execute correctly.

Using IN to Specify a List of Values You can't always use a WHERE clause with BETWEEN to specify the rows that you want to retrieve from a table in place of a WHERE clause that contains multiple ORs. The rows that contain the column values specified within the range of values will include rows that you don't want. You can, however, use the IN keyword in a WHERE clause to specify multiple rows more easily than if you use multiple ORs with a WHERE clause. A statement that uses IN uses the following syntax:

SELECT column_name_1,...column_name_n
FROM table_name_1,...table_name_n
WHERE column_name IN (value_1, ...value_n)

In the following code, IN is followed by a list of values to specify the rows to be retrieved (see Figure 7.29).

select *
from employee
where badge in (3211,6732,4411,5522)

FIG. 7.29
A WHERE clause that contains IN is simpler to write than a WHERE clause that contains multiple ORs and is more specific than using the BETWEEN operator.

Using an ORDER BY Clause

You may remember that the rows of a relational database are unordered. As part of your SELECT statement, you can specify the order in which you want the rows retrieved and displayed. This is done by adding an ORDER BY clause to sort the table rows that are retrieved by a SELECT statement.


NOTE: The rows of a SQL Server database are usually retrieved in the order in which you insert the rows into the table. If you create a clustered index for a table, the order of the rows returned by a query is the order of the clustered index. You can't, however, rely on the stored order of rows for two reasons.

If you want to return the table rows in a specific order, you must add an ORDER BY clause to a SELECT statement.


The syntax of a SELECT statement that contains an ORDER BY clause is as follows:

SELECT column_name_1,...column_name_n
FROM table_name_1,...table_name_n
ORDER BY column_name_1,...column_name_n

The following code, shown in Figure 7.30, shows a SELECT statement in which the rows are ordered by department. The rows of the employee table are retrieved in ascending order by default.

select *
from employee
order by department 

If you include the name of a second column after the name of the first column, the second column orders the rows that are duplicates of the first column. In the following code, ORDER BY is used to order the rows of the employee table first by the department and then by the badge column (see Figure 7.31).

select *
from employee
order by department, badge desc 

FIG. 7.30
You can use multiple columns to determine the order of rows retrieved.

FIG. 7.31
By adding parameters to the ORDER BY, you can increase the fields that are used in the sort.

Notice that the DESC keyword is added after the second named column to order the badge numbers in descending badge order. You can also use the keyword ASC to specify explicitly that a column's order in the ORDER BY clause be ascending. It's unnecessary, however, because the order of a column is ascending by default.

When you use ORDER BY in a SELECT statement, you can specify the columns in the ORDER BY clause by their order number in the SELECT clause. In the following code, the department and badge columns from the Employee table are referenced in the ORDER BY clause by their order of occurrence from left to right in the SELECT clause (see Figure 7.32).

select department, badge
from employee
order by 1, 2  

FIG. 7.32
If you use a SELECT statement that references the columns using column numbers instead of names, you'll need to be sure and reference them in the order they are called out in the query.


CAUTION: The number of columns referenced by column number in the ORDER BY clause can't be greater than the number of columns in the SELECT clause of a SELECT statement. If you specify a number that's larger than the number of columns in the SELECT clause, you receive an error message saying that you're out of range of the number of items in the select list.

SQL Server doesn't use the order of the column defined in a table, such as employee, in the ORDER BY clause. This is because, in a relational database, the syntax of a query language that references database data should be independent of the manner in which the data is stored.

If Transact-SQL used the order of columns as they are defined in the table, the column number would be based as a physical characteristic of the stored data. It's more appropriate to reference the columns using the relative order of the columns in the SELECT clause.

Another problem exists in trying to reference table columns by the order in which the columns are defined in a table. You can specify columns from different tables in the SELECT clause. You can't, for example, reference two columns that are both defined as the third column in two tables, because the column numbers are identical.

You have complete control over specifying columns in the SELECT clause of a SELECT statement. You can reference the same column more than once in a SELECT clause, and the column values will be displayed multiple times. In the following code, the badge column is referenced twice in the SELECT clause (see Figure 7.33).

select badge, name, department, badge
from employee

FIG. 7.33
Multiple instances of the same column can be used to improve readability.

You may not immediately see a reason for displaying the values of a column more than once. If a listing is wide enough, it can be convenient to display a column, often the unique identifier for each row, as the first and last columns of a display, as Figure 7.34 shows. A train schedule is an example of a wide output in which the stations are often displayed in the first, center, and last columns to make the output display easier to read.

Using DISTINCT to Retrieve Unique Column Values

You can construct your database table so that you never allow duplicate rows to be stored, or to allow duplicate rows. Unless you define a constraint on your table, such as a unique key, you can store duplicate rows in the table. Although you can disallow duplicate rows in the table, you can allow duplicates for some columns. You may want to find the unique entries that exist in a table column. The DISTINCT keyword is used to return the unique values of a column.

The following code shows the different departments of the employee table (see Figure 7.34).

select distinct department
from employee  

FIG. 7.34
You can use DISTINCT with multiple columns to return the unique values of a column.

If you use DISTINCT with multiple columns, the rows retrieved are unique by the combination of the columns specified after the DISTINCT keyword. The combination of the values from the Department and badge columns must return all rows. Whenever you combine a column that contains non-duplicate values, such as badge, with a column that contains duplicate values, such as Department, the combination of the two is non-duplicate.

Using Arithmetic Operators

You can use arithmetic operators to form expressions in Transact-SQL. Expressions are evaluated within the statements in which they appear. You need arithmetic operators to manipulate the data retrieved from tables. You can use the arithmetic operators in the SELECT clause to add, subtract, multiply, and divide data from columns that store numeric data.

Table 7.3 shows the arithmetic operators you can use in Transact-SQL.

Table 7.3 Transact-SQL Arithmetic Operators

Symbol Operation
+ Addition
- Subtraction
* Multiplication
/ Division
% Modulo (remainder)

You can form expressions by using arithmetic operators on columns defined with the datatypes TINYINT, SMALLINT, INT, FLOAT, REAL, SMALLMONEY, and MONEY. You can't use the modulo operator (%) on columns defined with the MONEY, SMALLMONEY, FLOAT, or REAL datatypes.


TIP: The modulo operator (%) is used to return an integer remainder that results from the division of two integer values. As a result, it can't be used with datatypes that can contain non-integer values. You receive an error message if you try to use the operator on columns defined as other than integer datatypes, even if the column values are whole numbers.

You can use arithmetic operators to form expressions in the SELECT clause of a SELECT statement with both numeric constants and columns. In the following code, an expression is used to increment the badge numbers by five (see Figure 7.35).

select badge, badge + 5
from employee  

FIG. 7.35
You can use multiple arithmetic operators to operate on column names, constants, or a combination of column names and constants.

When you use an expression in a SELECT clause, the display for the evaluation of the expression doesn't have a column header. You can specify a column header for the expression by preceding the expression with a text string followed by an equal sign (=). For example, the following code shows the expression preceded by the specified column header in the SELECT clause of the SELECT statement (see Figure 7.36). You can enclose the text string in single or double quotation marks to retain embedded spaces.

FIG. 7.36
You can also specify an alternate column header for any column without using the column name in an expression.


NOTE: If you perform an arithmetic operation on a column that contains a NULL, the result is NULL. This means that, if you're developing an application and an operation is returning NULL as a result, you should check to make sure the values you're using in the equation are valid and not-NULL.

select badge, "badge + 5" = badge + 5
from employee



Computed columns are not stored in the database. They may exist in a temporary table that is created during the execution of the SELECT statement. Because the data is not in the database, there is no way to directly verify the results of a computed column. One easy way to compute a value incorrectly is to ignore operator precedence. Arithmetic operators are performed in predetermined order unless parentheses are used. Table 7.4 shows the order in which arithmetic operators are executed.

Table 7.4 Precedence Order of Arithmetic Operators

Operator Order of Precedence
* 1st
/ 1st
% 1st
+ 2nd
- 2nd

If you use multiple arithmetic operators with the same order of precedence, the expressions are evaluated from left to right. You can use parentheses to control the order of execution. Expressions in parentheses are evaluated before any other expressions. Use parentheses to evaluate expressions that contain addition and subtraction before the expressions that contain multiplication, division, and modulo operators.

The following code shows the use of parentheses in the SELECT clause. The constant five is added to each value of the badge column. After the constant is added to badge, the sum is multiplied by two (see Figure 7.37).

FIG. 7.37
You can use parentheses to make the evaluation order more descriptive, even if the parentheses are unnecessary.

select "badge + 5 * 2" = (badge + 5) * 2
from employee  

You can perform arithmetic operations on different numeric datatypes in the same expression, a procedure called mixed mode arithmetic. The datatype of the result is determined by the rank of the datatype code stored in a column of a system table.

You can use a SELECT statement to retrieve the datatype names and their code numbers from the systypes system table. Table 7.5 shows the codes for the numeric datatypes.

Table 7.5 Type Codes for the Numeric Datatypes

Datatype Code
TINYINT 48
SMALLINT 52
INT[EGER] 56
REAL 59
MONEY 60
FLOAT 62
SMALLMONEY 122

When you write expressions using different datatypes, the results are returned in the datatype of the highest ranked datatype. For example, the values of a column that is defined as either a TINYINT or a SMALLINT datatype is converted to INT if they're evaluated in an expression that contains an INT datatype.

One exception to the datatype code rule applies to expressions that include columns with the FLOAT and MONEY datatypes. The evaluation of an expression that contains FLOAT and Money is returned as the MONEY datatype, even though the code number for MONEY (60) is lower than FLOAT (62). You can retrieve the code numbers for all the Transact-SQL datatypes by using the query in following code.

select name, type
from systypes
order by type desc

This statement retrieves the names and codes for all Transact-SQL datatypes in order from the highest code numbers.

Using a GROUP BY Clause

The GROUP BY clause divides a table into groups of rows. The rows in each group have the same value for a specified column. Duplicate values for each different value are placed in the same group. Grouping enables you to perform the same functions on groups of rows.

You can group by any number of columns in a statement. Columns in the select list must be in the GROUP BY clause or have a function used on it. The syntax of a SELECT statement that contains a GROUP BY clause is as follows:

SELECT column 1,...column n
FROM tablename
GROUP BY columnname 1, columnname n

GROUP BY targets only unique column values after sorting by ascending column value (default). GROUP BY is unlike the ORDER BY clause, which sorts records in ascending order but doesn't remove duplicate column values.

The example query shown in the following code groups the rows by the department column (see Figure 7.38). The departments are first sorted to group them together. The duplicate departments aren't displayed because the purpose of the GROUP BY clause in a SELECT statement is to form groups of rows for subsequent action by other clauses.

select department, "headcount" = count(*)
from employee
group by department

FIG. 7.38
A SELECT statement containing a GROUP BY clause sorts rows by the columns included.


See Chapter 9 section titled "Using COUNT()," for more information on using the function shown in the previous example.

For example, you can select specific groups with a HAVING clause, which compares some property of the group with a constant value. If a group satisfies the logical expression in the HAVING clause, it's included in the query result. The syntax of a SELECT statement with a HAVING clause is as follows:

SELECT column 1,...column n
FROM tablename
GROUP BY columnname
HAVING expression

The HAVING clause is used to determine the groups to be displayed in the output of the SELECT statement. The following code shows the use of a HAVING clause (see Figure 7.39).

select department, "headcount" = count(*)
from employee
group by department
having count(*) = 1

FIG. 7.39
An example SELECT statement containing a HAVING clause that limits the returned rows.

Using a COMPUTE Clause in a SELECT Statement

You can use a COMPUTE clause in a SELECT statement with functions such as SUM(), AVG(), MIN(), MAX(), and COUNT(). The COMPUTE clause generates summary values that are displayed as additional rows. The COMPUTE clause works like a so-called control break, a mechanism used in applications called report writers. You can use the COMPUTE clause to produce summary values for groups, as well as to calculate values using more than one function for the same group.


NOTE: A report writer is an application that permits you to retrieve data from a database without using SQL statements. A report writer is designed with a graphical user interface that permits you to point and click buttons and menu commands to retrieve database data. You might find it useful to purchase a report writer to retrieve data from your database as well as using SQL statements.

You can also use report writers that are built in to some applications, like Microsoft Access, and allow these applications to generate the SQL statements and control breaks for you.


The general syntax of the COMPUTE clause is as follows:

COMPUTE row_aggregate(column name)
[,row_aggregate(column name,...]
[BY column name [,column name...]


See Chapter 9, "Using Functions," for more information on the aggregate function shown in the previous example.

Several restrictions apply to the use of a COMPUTE clause in a SELECT statement. The following list summarizes the COMPUTE clause restrictions:


NOTE: You can use a clause that contains the keyword COMPUTE without BY to display grand totals or counts. You can also use both a COMPUTE and a COMPUTE BY clause in a SELECT statement.

Using Subqueries

You can nest a complete SELECT statement within another SELECT statement. A SELECT statement that is nested within another SELECT statement is called a subquery. The nested or inner SELECT statement is evaluated and the result is available to the outer SELECT statement. To use a subquery, enclose a SELECT statement within parentheses to specify that it should be evaluated before the outer query.

The row, or rows returned by the SELECT statement in parentheses are used by the outer SELECT statement. The rows returned by the inner SELECT statement are used in the position of the value in the WHERE clause of the outer SELECT statement. For example, in the following code, all rows are retrieved for the Employee table, where the department is equal to the same department in which Bob Smith is a member (see Figure 7.40).

select *
from employee
where department = (
select department
from employee
where name = "bob smith") 

FIG. 7.40
You can nest a subquery within the subquery by using an additional set of parentheses around an enclosed SELECT statement.

Some restrictions apply to the use of subqueries. The SELECT list of a subquery must return one of two things:

...
if exists select * from mytable where myname="Brennan" 
...

This would result in the following error if a comparison operator such as = (equal to) were used in the WHERE clause of the outer query:

Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is illegal when the subquery
follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
Command has been aborted.


NOTE: Use a NOT IN to eliminate rows that match the results of a subquery.

You're restricted in the choice of datatypes within subqueries. You can't use either the IMAGE or TEXT datatypes in the SELECT clause of the subquery. Also, the datatype of the value(s) returned by the subquery must match the datatype used in the WHERE clause of the outer query.

Here are some key points to keep in mind:

Using ANY and ALL You can use ANY and ALL to modify the comparison operators that precede a subquery. In Transact-SQL, ANY and ALL don't have the same meaning that they do in the English language. For example, when > (greater than) is followed by ALL, it's interpreted as greater than all values, including the maximum value returned by a subquery. When > (greater than) is followed by ANY, it's interpreted as greater than at least one, which is the minimum.


TIP: If you have difficulty understanding the results of queries that contain one or more nested queries, you can execute each subquery separately. If you record the result of the execution of an inner query, you can use the values to help you interpret the results of the outer queries.


NOTE: The = ANY keyword is evaluated identically to IN. It would be clearer to use an IN instead of = ANY.

Specifying Optimizer Hints The optimizer hints clause is somewhat misleading. You use the optimizer clause of the SELECT statement to override the data-retrieval methods that are automatically chosen by the query optimizer. When a query is executed, a portion of SQL Server, called the query optimizer, determines how the retrieval of the data from the database is performed. For example, although an index may exist on a table that is referenced in a query, the query optimizer may determine that it would be faster to retrieve the rows from the table without using the index. The query optimizer may not use an index because the number of rows requested by the query are few in number, and it would be faster to directly access the data rows instead of both the index rows and then data rows.

If multiple indexes exist on a table, the query optimizer will choose to return the rows of the table using the index that would result in the fastest retrieval of information.

You may, however, want to override the way in which the retrieval of rows will be achieved. For example, if two indexes, one clustered and one non-clustered, exist on a table, the query optimizer may choose to use the non-clustered index for the retrieval of the table rows. You can use the optimizer hints clause in a SELECT statement to force a retrieval using the clustered index. You want the rows to be retrieved by the clustered index because they will automatically be returned in ascending sorted order by the column or columns on which the clustered index was created.

For example, you can use the following optimizer hints clause in a SELECT clause to specify that the rows of a table will be retrieved by the clustered index:

select * from employee (index=0)

This statement specifies the index name or ID to use for that table. Zero(0) forces the use of a clustered index if one exists. If you use a optimizer hint of one(1), a non-clustered index is used for retrieval of rows targeted in the SELECT statement.


NOTE: You can confirm the method that is used to retrieve your rows by using the query option SHOWPLAN. This option returns information about how SQL Server performed your query. For example, if the rows of the query were retrieved without using an index, the method table scan appears in the information returned by SHOWPLAN.

SHOWPLAN also clearly specifies if a clustered or non-clustered index is used for retrieval in a query. Click the Query Options button on the toolbar to bring up the Query Options dialog box, then select the Show Query Plan check box to return SHOWPLAN information.



TIP: You can also use the syntax index=index_column_name as an optimizer hint in a SELECT statement in place of the index number.

You can use a second set of optimizer hints to control the synchronization, or locking of the tables in your SELECT statement. You can use the locking optimizer hints to override the way in which SQL Server normally controls access to data from multiple clients.

Using NOLOCK You use the NOLOCK optimizer hint to permit you to read rows that SQL Server would normally not permit you to access. For example, if you use NOLOCK in a SELECT statement, you can read uncommitted rows.

Using HOLDLOCK You use HOLDLOCK to prevent other clients from changing rows that are part of your SELECT clause until your transaction is complete. Normally, other clients can modify the rows when they're displayed. One restriction of HOLDLOCK is that you can't use it in a SELECT clause that also contains a FOR BROWSE clause, discussed later.

Using UPDLOCK You use an UPDLOCK like HOLDLOCK to prevent other clients from changing rows that are part of your SELECT clause. UPDLOCK releases the rows of your table and the end of the command or next transaction, instead of at the end of the transaction only.

Using TABLOCK You use TABLOCK like HOLDLOCK to prevent other clients from changing rows. TABLOCK, unlike HOLDLOCK, acts on the entire table, instead of just on the rows of your table. You can use TABLOCK along with HOLDLOCK to prevent other clients from changing rows of your entire table until your transaction completes.

Using PAGLOCK You use PAGLOCK like HOLDLOCK to prevent other clients from changing rows. PAGLOCK prevents other clients from changing rows a table page at a time, instead of the entire table.

Using TABLOCKx You use TABLOCKx to prevent other clients from displaying, as well as changing, an entire table referenced in your SELECT clause until your command or transaction is complete. You might implement this option in cases where you're updating a table and it's imperative that the updated information is always displayed at the client. It's simply a way of locking the information until it's certain that you're able to provide complete, updating information to the client applications that may be accessing it.

Using FASTFIRSTROW You use FASTFIRSTROW to retrieve the rows of a table using a non-clustered index. Unlike the index=1 optimizer hint, the first row of the query is returned more quickly through use of optimized read techniques. The total time that it takes to perform the query may be longer than if the non-clustered index were used with the FASTFIRSTROW option. You use FASTFIRSTROW to get better response time by returning the initial results of your query faster.


NOTE: The response time for a database such as SQL Server is usually defined as time that it takes to display, on a client system monitor, the first row of a query. Throughput is the amount of time that it takes to complete an operation, whether or not part of the operation involves the display of information as feedback to a client.

Using the FOR BROWSE Option

You can use the FOR BROWSE clause in a SELECT statement to read a table within which another client is now adding, deleting, or updating rows. Normally, SQL Server won't permit you to read a table while pending updates, deletes, or inserts are uncommitted. There are restrictions on what other clauses your SELECT statement can contain when you use the FOR BROWSE clause.

To use the FOR BROWSE clause in a SELECT statement, the SELECT statement must contain a table with a timestamp column and a unique index. To use the FOR BROWSE clause in a SELECT statement, the SELECT statement can't contain a UNION clause. FOR BROWSE should be the last clause of a SELECT statement.

Unique indexes and timestamp columns are required attributes of tables to be used with the FOR BROWSE clause. If a table doesn't meet these requirements, a retrieval statement executes as though the FOR BROWSE clause weren't present in the SELECT statement. A SELECT statement that tries to read the rows from a table that's being modified waits for the default query timeout interval of five minutes.

If the modification is completed within that time, the rows are displayed by the waiting query. If the pending modification doesn't complete within the timeout interval, the query fails. The FOR BROWSE clause in a SELECT statement permits you to read rows of a table while they're being changed.


CAUTION: If you use the FOR BROWSE clause in a SELECT statement, remember that you're looking at table rows whose values may not be kept by the user who's modifying the table. You must be willing to take a chance that a change you see in a table using a SELECT statement containing the FOR BROWSE clause may not be kept.

From Here...

In this chapter, you've learned to write queries for the retrieval of data from a database so the results set contains only the required rows or columns. In addition, you've learned to manipulate the returned data, performing arithmetic operations and sorting the rows. Finally, you've learned to use optimization techniques to override the default actions of SQL Server to retrieve data faster or in different ways.

For more information about the topics mentioned in this chapter, see the following chapters:


Previous chapterNext chapterContents


Macmillan Computer Publishing USA

© Copyright, Macmillan Computer Publishing. All rights reserved.