Limit Your Resultset with TOP or SET ROWCOUNT

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.