Limiting Result Sets by Using TOP and PERCENT
You can use the TOP clause to limit the number of rows that are returned in the result set.
TOP ( expression ) [ PERCENT ] [ WITH TIES ]
expression is a numeric expression that specifies the number of rows to be returned; or if PERCENT is specified, the percentage (specified by expression) of the result set rows is returned. For example:
TOP (120) /*Return the top 120 rows of the result set. */
TOP (15) PERCENT /* Return the top 15 percent of the result set. */.
TOP(@n) /* Return the top @n rows of the result set, with the variable declaration: DECLARE @n AS BIGINT; SET @n = 2 */.
If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The whole result set is built in the specified order and the top n rows in the ordered result set are returned. If WITH TIES is also specified, all rows that contain the last value returned by the ORDER BY clause are returned, even if doing this exceeds the number specified by expression.
TOP vs. SET ROWCOUNT
The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in the following ways:
The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is ended when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.
The TOP clause applies to the single SELECT statement that it is specified in. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.
Important
Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new development work, and plan to modify applications that currently use it. We recommend that DELETE, INSERT, and UPDATE statements that currently are using SET ROWCOUNT be rewritten to useTOP.
Although the effect of SET ROWCOUNT on SELECT statements remains unchanged, using TOP with SELECT is preferred over using SET ROWCOUNT for the following reasons:
- SET ROWCOUNT causes most SELECT, INSERT, UPDATE, and DELETE statements to stop processing when they have affected the specified number of rows. This behavior applies inside the firing of triggers too.
- As a part a SELECT statement, the query optimizer can use the value of expression in the TOP clause as part of generating an execution plan for a query. Because SET ROWCOUNT is used outside a statement that executes a query, its value cannot be used to generate a query plan for a query.
See Also
Other Resources
TOP (Transact-SQL)
SELECT (Transact-SQL)
SET ROWCOUNT (Transact-SQL)