The TOP Operator

The TOP Operator

Although the TOP operator has been around since SQL Server 7.0, in the past it only accepted a constant as the number of rows to return or as the percentage of the rows to return. The TOP statement in SQL Server 7.0 and SQL Server 2000 was also limited to SELECT statements. Thanks to improvements in the TOP operator for SQL Server 2005, the TOP statement can now accept variables and subqueries for the number of rows to return or the percentage of the rows to return. The TOP operator has also been enhanced so that it can be used with INSERT, UPDATE, and DELETE statements in addition to the SELECT statement.

Traditionally, developers utilized the TOP operator to reduce the number of rows returned by a query. When developers needed to page the results from a SQL Server query, they would either have to just return a static number of rows each time or build a dynamic query in which they could control the actual number of rows being returned by the query. The following query returns only 10 rows of data, and by changing the value of the variable, you could essentially page through a large result set:

USE AdventureWorks; GO DECLARE @startAfter INTEGER; SET @startAfter = 0; SELECT TOP (10) * FROM HumanResources.Employee    WHERE EmployeeID > @startAfter    ORDER BY EmployeeID; GO

You could then return the result set to the client, and when you needed to page down, you would just reset the variable to the largest EmployeeID value (for example, 10) returned in the current set of data:

DECLARE @startAfter INTEGER; SET @startAfter = 10; SELECT TOP (10) * FROM HumanResources.Employee    WHERE EmployeeID > @startAfter    ORDER BY EmployeeID; GO

Most of the time, this method is fine, but what if there were only two rows left in the table? Would you really want to incur an additional network trip to return only two rows? You could get around this issue with the use of dynamic SQL statements to change the number of rows returned by the query to include those two additional rows, as shown here:

DECLARE @strSQL NVARCHAR(1000); DECLARE @intRows INTEGER; DECLARE @startAfter INTEGER; SET @intRows = 10 ; SET @startAfter = 0; SET @strSQL = 'SELECT TOP (' + CAST(@intRows AS VARCHAR(3)) + ') *      FROM HumanResources.Employee      WHERE EmployeeID > ' + CAST(@startAfter AS VARCHAR(3)) + '      ORDER BY EmployeeID'; EXEC sys.sp_executesql @strSQL; GO

For the next result set, if 12 rows should be returned, you can set @intRows to 12, and the dynamic SQL execution will take care of returning the requested number of rows. This way, you can prevent a third stored procedure call and an additional network trip.

With SQL Server 2005, the ability to pass an expression with the TOP operator enables the application to simply state how many rows of data it requires; this prevents the need for dynamic SQL and the problems associated with using dynamic SQL:

DECLARE @intRows INTEGER; DECLARE @startAfter INTEGER; SET @intRows = 12 ; SET @startAfter = 10; SELECT TOP (@intRows) * FROM HumanResources.Employee    WHERE EmployeeID > @startAfter    ORDER BY EmployeeID; GO

You can still use the PERCENT keyword to return a percentage of rows instead of a fixed number of rows:

DECLARE @intPercRows INTEGER DECLARE @intPage INTEGER SET @intPercRows = 12 SET @intPage = 10 SELECT TOP (@intPercRows) PERCENT * FROM HumanResources.Employee    WHERE EmployeeID > @intPage    ORDER BY EmployeeID; GO

In addition to passing an expression, you can also use a subquery to satisfy the TOP statement value requirement. Here is an example of passing a subquery with the TOP operator:

DECLARE @startAfter INTEGER; SET @startAfter = 0; SELECT TOP (SELECT COUNT(*)/11 FROM HumanResources.Employee) *   FROM HumanResources.Employee   WHERE EmployeeID > @startAfter   ORDER BY EmployeeID; GO

Considering that the HumanResources.Employee table has 290 rows, you can use this script to retrieve 11 batches of 26 rows, and the remaining 12th batch of 4 rows, by changing the value of the @startAfter variable.