Select TOP Example

You may limit the number of rows returned by a single SELECT statement by using the keyword TOP in the statement and specifying a value for the maximum number of rows to return.

The specified number of rows must be a literal positive number. It is defined as a 32-bit unsigned integer.

A SELECT statement can include both TOP and an ORDER BY clause. If so, the database engine generates a temporary table and populates it with the query’s entire result set if no index can be used for the ORDER BY. The rows in the temporary table are ordered as specified by the ORDER BY clause and the TOP number of rows in the ordered result set are returned.

Views that contain a TOP clause may be joined with other tables or views.

The main difference between TOP and SET ROWCOUNT is that TOP affects only the current statement, while SET ROWCOUNT affects all subsequent statements issued during the current database session.

If both SET ROWCOUNT and TOP are applied to a given query, the query returns a number of rows equal to the lesser of the two values.


Cursor Types and TOP

A SELECT query with a TOP clause used in a cursor implicitly changes the cursor type under several circumstances. When using the table below, remember that any SELECT with ORDER BY on an unindexed column requires a temporary table. If the ORDER BY is on an indexed column, then a temporary table is not required.

Table 49

Effect of TOP Clause on Cursor Types

Original Cursor Type

Converted to This Type if SELECT query requires a temporary table:

Converted to This Type if SELECT query does not require a temporary table:






No change


No change

No change

SELECT TOP 10 * FROM person -- returns 10 rows


SELECT TOP 10 * FROM person; -- returns 5 rows


SELECT TOP 10 * FROM person ORDER BY id; -- returns the first 10 rows of the full list ordered by column id.

Friday, 19 April 2019 Posted in Example SQL Statements