When I ran this script, it took 10 logical reads without the index and 4 logical reads with the index—an improvement of 60 percent. Six fewer page reads might not sound like much, but Order Details is a small table. When your tables have thousands of pages or more, you'll appreciate a savings of 60 percent. SQL Server uses the nonclustered index to find the highest quantity, then follows the bookmark to retrieve the data row. The query plan in Figure 2 shows the index scan.
One thing that might surprise you is that SQL Server also takes advantage of the nonclustered index if you use SET ROWCOUNT instead of TOP. If you've built the index as Listing 2 shows, the following batch also results in only four logical reads:
SET ROWCOUNT 1
SELECT * FROM [order details]
ORDER BY quantity DESC
SET ROWCOUNT 0
So, does using TOP instead of ROWCOUNT yield better performance if you don't want to use special features such as PERCENT or WITH TIES? When the column in the ORDER BY clause has a clustered or nonclustered index, I've detected no difference. However, if no supporting index exists, TOP has an advantage because of an internal feature of the SQL Server 2000 query processor.
Ken Henderson, author of The Guru's Guide to Transact-SQL (Addison-Wesley, 2000), challenged the SQL Server Most Valuable Professionals (MVPs) to find a case in which TOP was reproducibly faster than SET ROWCOUNT. MVP Dejan Sarka came up with a script similar to the ones in Listing 3 and Listing 4 to prove that TOP is faster. Sarka's script gives the desired results only on SQL Server 2000, and the performance difference is in CPU time, not in I/O operations. The SQL Server feature that gives this performance improvement is the TOP-N engine. When running a query that includes TOP and ORDER BY clauses for which no supporting index exists, SQL Server maintains a sorted temporary table in memory. SQL Server replaces the last row of this table only when it finds a new satisfying row. For example, if your query is looking for the TOP 10 largest prices, SQL Server maintains a sorted in-memory temporary table of the 10 largest prices so far, along with pointers or keys for the rows that contain those prices. When SQL Server encounters a new price that's larger than the smallest of the 10 in-memory rows, it removes that smallest one and puts the new row in its proper sorted position in the in-memory table. So the I/O is the same as for a full sort; SQL Server must read all the rows in the table. But the processing effort is greatly reduced because a full sort is unnecessary, and only occasionally does SQL Server need to overwrite a row in the in-memory table.
Listing 3 contains T-SQL code to build a table to illustrate the improved performance of TOP in SQL Server 2000. The table needs to be very large for SQL Server to use the TOP-N engine, so this script populates table t1 with 100,000 rows by copying the table into itself 17 times.
Listing 4 shows the tests comparing TOP 10 SET with ROWCOUNT 10 and uses SET STATISTICS to report both the time taken and the I/O required to run the queries. Ignoring the times for parse and compile operations, Figure 3 shows the results that I got when I ran the script on my laptop. The effect of data caching isn't at issue because the script uses DBCC DROPCLEANBUFFERS to clear the data cache before each query. You can see that the I/O is the same for both queries, with 182 logical reads and 160 physical reads. However, the CPU time required for the TOP 10 query was only about 80 percent of the time needed with SET ROWCOUNT, and the elapsed time was reduced to about two-thirds.
If you have proper indexes in place, you might not think you need to use TOP for retrieving a subset of rows from a result set. However, when no useful index exists, TOP is a performance winner. Add to that the extra functionality of PERCENT and WITH TIES, and you'll find that TOP is a very useful feature indeed.