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 10SELECT * FROM dbo.OrdersWHERE EmployeeID = 5ORDER BY OrderDateOrderID CustomerID EmployeeID OrderDate                   ------- ---------- ---------- -----------------------10248   VINET      5          1996-07-04 00:00:00.00010254   CHOPS      5          1996-07-11 00:00:00.00010269   WHITC      5          1996-07-31 00:00:00.00010297   BLONP      5          1996-09-04 00:00:00.00010320   WARTH      5          1996-10-03 00:00:00.00010333   WARTH      5          1996-10-18 00:00:00.00010358   LAMAI      5          1996-11-20 00:00:00.00010359   SEVES      5          1996-11-21 00:00:00.00010372   QUEEN      5          1996-12-04 00:00:00.00010378   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, OrderDateFROM dbo.OrdersWHERE EmployeeID = 5ORDER BY OrderDateOrderID CustomerID EmployeeID OrderDate                   ------- ---------- ---------- -----------------------10248   VINET      5          1996-07-04 00:00:00.00010254   CHOPS      5          1996-07-11 00:00:00.00010269   WHITC      5          1996-07-31 00:00:00.00010297   BLONP      5          1996-09-04 00:00:00.00010320   WARTH      5          1996-10-03 00:00:00.00010333   WARTH      5          1996-10-18 00:00:00.00010358   LAMAI      5          1996-11-20 00:00:00.00010359   SEVES      5          1996-11-21 00:00:00.00010372   QUEEN      5          1996-12-04 00:00:00.00010378   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, OrderDateFROM dbo.OrdersWHERE EmployeeID = 5ORDER BY OrderDateOrderID CustomerID EmployeeID OrderDate                   ------- ---------- ---------- -----------------------10248   VINET      5          1996-07-04 00:00:00.00010254   CHOPS      5          1996-07-11 00:00:00.00010269   WHITC      5          1996-07-31 00:00:00.00010297   BLONP      5          1996-09-04 00:00:00.00010320   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 intASIF @RowCount IS NULL  SET @RowCount = 10SET ROWCOUNT @RowCountSELECT OrderID, CustomerID, EmployeeID, OrderDateFROM dbo.OrdersWHERE EmployeeID = 5ORDER 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.