Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


advertisement
 

Limit Your Resultset with TOP or SET ROWCOUNT

To specify the number of records that are returned by a query, you can use either SET ROWCOUNT or the TOP keyword.


advertisement
ou're probably familiar with the typical "SELECT * FROM Table" SQL statement, which returns all the records in a table. By adding a WHERE clause to a SELECT statement, you limit the resultset to only those records that match the WHERE criteria. Although this may or may not limit the resultset, the number of records returned to the client is always unknown. To specify the number of records that are returned by a query, you can use either SET ROWCOUNT or the TOP keyword.

A SET ROWCOUNT statement simply limits the number of records returned to the client during a single connection. As soon as the number of rows specified is found, SQL Server stops processing the query. The syntax looks like this:

SET ROWCOUNT 10 SELECT * FROM dbo.Orders WHERE EmployeeID = 5 ORDER BY OrderDate OrderID CustomerID EmployeeID OrderDate ------- ---------- ---------- ----------------------- 10248 VINET 5 1996-07-04 00:00:00.000 10254 CHOPS 5 1996-07-11 00:00:00.000 10269 WHITC 5 1996-07-31 00:00:00.000 10297 BLONP 5 1996-09-04 00:00:00.000 10320 WARTH 5 1996-10-03 00:00:00.000 10333 WARTH 5 1996-10-18 00:00:00.000 10358 LAMAI 5 1996-11-20 00:00:00.000 10359 SEVES 5 1996-11-21 00:00:00.000 10372 QUEEN 5 1996-12-04 00:00:00.000 10378 FOLKO 5 1996-12-10 00:00:00.000

Although the SQL Server BOL claims that T-SQL doesn't support the TOP keyword, in fact it does—and this is a feature that's new to SQL Server 7.0. The TOP keyword also limits the number of rows returned but is used as part of the SELECT statement:


SELECT TOP 10 OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE EmployeeID = 5 ORDER BY OrderDate OrderID CustomerID EmployeeID OrderDate ------- ---------- ---------- ----------------------- 10248 VINET 5 1996-07-04 00:00:00.000 10254 CHOPS 5 1996-07-11 00:00:00.000 10269 WHITC 5 1996-07-31 00:00:00.000 10297 BLONP 5 1996-09-04 00:00:00.000 10320 WARTH 5 1996-10-03 00:00:00.000 10333 WARTH 5 1996-10-18 00:00:00.000 10358 LAMAI 5 1996-11-20 00:00:00.000 10359 SEVES 5 1996-11-21 00:00:00.000 10372 QUEEN 5 1996-12-04 00:00:00.000 10378 FOLKO 5 1996-12-10 00:00:00.000

It's important to remember that TOP and SET ROWCOUNT don't order your results in any way or select the records with the highest or greatest value. Rather, they take the first rows that are returned by your query. If you want to return a certain number of records depending on value, use the ORDER BY clause in conjunction with TOP or SET ROWCOUNT. In that case, the resultset is ordered first and then the top x records are returned.

The TOP keyword can be used to specify a percentage of records to be returned. For example, the following query returns the first 10 percent of all the records returned by the query. There are 42 records in the resultset so 10 percent is approximately 5 records. When using TOP PERCENT, the number you specify must be between 0 and 100:

SELECT TOP 10 PERCENT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE EmployeeID = 5 ORDER BY OrderDate OrderID CustomerID EmployeeID OrderDate ------- ---------- ---------- ----------------------- 10248 VINET 5 1996-07-04 00:00:00.000 10254 CHOPS 5 1996-07-11 00:00:00.000 10269 WHITC 5 1996-07-31 00:00:00.000 10297 BLONP 5 1996-09-04 00:00:00.000 10320 WARTH 5 1996-10-03 00:00:00.000

One of the primary advantages of SET ROWCOUNT is that you can use a variable to specify the number of rows; with SET TOP you cannot. So if you want to create a stored procedure that takes the row count as a parameter, you can only use SET ROWCOUNT like this:

CREATE PROCEDURE SelectOrders @RowCount int AS IF @RowCount IS NULL SET @RowCount = 10 SET ROWCOUNT @RowCount SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE EmployeeID = 5 ORDER BY OrderDate

Functionally, TOP and SET ROWCOUNT do the same thing. However, SET ROWCOUNT will affect any subsequent queries in a connection. To remove the row count option, use SET ROWCOUNT 0. Another thing to remember is that SET ROWCOUNT overrides a SELECT TOP only if the SET ROWCOUNT value is less. Because TOP is new to SQL Server 7.0, if you have your server compatibility level set to version 6.5, you'll receive an error message if you try to use it. You must first use sp_dbcmptlevel to change the compatibility to version 7.0.



   
Dianne Siebold is a consultant specializing in Visual Basic and SQL Server programming. She is also the author of the "Visual Basic Developer's Guide to SQL Server" (Sybex). Reach her by e-mail here.
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap