Take It from the TOP

Proper indexing can help speed up sorting operations. In "Tuning of a Different Sort," December 2001, InstantDoc ID 22927, I stressed that sorting (as when you use ORDER BY) applies only to the result set because the data in a relational database table has no specific order. Another T-SQL construct you can use in a query's SELECT list to limit the number of rows returned is the TOP clause. TOP is similar to ORDER BY in that it affects only the result set. Let's look at how to optimize the use of the TOP clause and examine which indexes can help improve the performance of TOP queries. To understand how to get the maximum performance from queries that use TOP, you need to know exactly what those queries are trying to accomplish. Let's begin with some details about the semantics of the TOP operator, then let's look at some internal SQL Server mechanisms that improve the performance of TOP queries.

TOP of the Class
You know that the WHERE clause in a query lets you limit the number of rows that a SELECT statement returns. But to use a WHERE clause, you need to know some of the possible values a column might have so that you can include them in (or exclude them from) the WHERE clause's conditions. What if you want to see only a screenful of rows from a table, but you have no idea what range of values exists in the table? Before SQL Server 7.0, your only choice was to use the option SET ROWCOUNT n to limit SQL Server to sending only n rows to the client. Every SELECT statement using the connection in which ROWCOUNT was set would stop sending rows to the client after n rows had been sent. To remove the row limit, you set ROWCOUNT to 0. For example, the following query returns only five rows from the Orders table:

SET ROWCOUNT 5
SELECT * FROM orders
SET ROWCOUNT 0

In SQL Server 2000 and 7.0, you can instead use the TOP keyword in the SELECT list to get just five rows of the result set. The following statement is equivalent to the three-line batch above:

SELECT TOP 5 * FROM orders

As I show later, you can extend the TOP clause to control the data you want to see. The SQL Server query processor has an internal mechanism to help optimize the performance of TOP queries, so in many cases, the performance of TOP queries is better than the equivalent queries that use ROWCOUNT. Using SET ROWCOUNT instead of TOP has one specific advantage, however. The integer argument you use with SET ROWCOUNT can be a variable, but the value you use with TOP must be a constant. For example, the first batch in Listing 1 returns five rows from the Orders table, but the second batch generates an error.

Keep in mind that data values in your table have no effect on which rows are returned when you use either this simple form of TOP or the SET ROWCOUNT option. SQL Server typically returns rows in whatever order is most efficient. With both these options, SQL Server simply stops sending rows to the client when it reaches the limit, regardless of the data values in the rows returned. Therefore, if you want more control over which rows are returned, you need to use other options.

The most straightforward extension to TOP is to request a percentage of rows from the result set, rather than an absolute number. TOP with PERCENT instructs SQL Server to round up to the nearest integer number of rows. The Northwind database's Orders table has 830 rows, so a statement that requests the TOP 1 PERCENT of those rows returns 9 rows:

SELECT TOP 1 PERCENT * FROM orders

One of the most common syntax errors that programmers make when using TOP is to leave out the column list. TOP controls just the number of rows; SQL Server still needs a SELECT list to know which columns to return. You can use an asterisk (*) to specify all columns, or you can specify certain columns, as the query below shows:

SELECT TOP 1 PERCENT OrderID, CustomerID, EmployeeID, OrderDate 
FROM orders