Top

TOP:

Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server 2016.

You can specify the quantity of rows you want in one of two ways: as an exact number of rows, from TOP (0) to TOP (9223372036854775807) (the largest BIGINT value), or as a percentage of rows, from TOP (0E0) PERCENT to TOP (100E0) PERCENT, using a FLOAT value. SQL Server supports any self-contained expression, not just constants, with TOP.

When TOP is used in conjunction with the ORDER BY clause, the result set is limited to the first N number of ordered rows; otherwise, it returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement or affected by an INSERT, UPDATE, MERGE, or DELETE statement.

Syntax:

[

    TOP (expression) [PERCENT]

    [WITH TIES]

]

Arguments:

Expression:

Is the numeric expression that specifies the number of rows to be returned. expression is implicitly converted to a float value if PERCENT is specified; otherwise, it is converted to bigint.

PERCENT:

Indicates that the query returns only the first expression percent of rows from the result set. Fractional values are rounded up to the next integer value.

The table has 830 rows, and 1 percent of 830 is 8.3. Because only whole rows can be returned and 8.3 were requested, the actual number of rows returned is 9. When TOP . . .PERCENT is used and the specified percent includes a fractional row, the exact number of

Rows requested are rounded up.

WITH TIES:

Used when you want to return two or more rows that tie for last place in the limited results set. Must be used with the ORDER BY clause. WITH TIES may cause more rows to be returned than the value specified in expression. For example, if expression is set to 5 but 2 additional rows match the values of the ORDER BY columns in row 5, the result set will contain 7 rows.

TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified. The returned order of tying records is arbitrary. ORDER BY does not affect this rule.

Best Practices

In a SELECT statement, always use an ORDER BY clause with the TOP clause. This is the only way to predictably indicate which rows are affected by TOP.

Notice the usage of parentheses here for the input expression to the TOP option. Because SQL Server supports any self-contained expression as input, the expression must reside within parentheses. For purposes of backward compatibility, SQL Server still supports SELECT TOP queries that use a constant without parentheses. However, it’s good practice to put TOP constants in parentheses to conform to the current requirements.

The older syntax for TOP() did not include the parentheses and did not accept a variable. The newer syntax, with the parentheses, was introduced with SQL Server 2005 and is the best practice moving forward.

Use OFFSET and FETCH in the ORDER BY clause instead of the TOP clause to implement a query paging solution. A paging solution (that is, sending chunks or "pages" of data to the client) is easier to implement using OFFSET and FETCH clauses. For more information, see ORDER BY Clause (Transact-SQL).

Use TOP (or OFFSET and FETCH) instead of SET ROWCOUNT to limit the number of rows returned. These methods are preferred over using SET ROWCOUNT for the following reasons:

As a part of a SELECT statement, the query optimizer can consider the value of expression in the TOP or FETCH clauses during query optimization. Because SET ROWCOUNT is used outside a statement that executes a query, its value cannot be considered in a query plan.

Compatibility Support:

For backward compatibility, the parentheses are optional in SELECT statements. We recommend that you always use parentheses for TOP in SELECT statements for consistency with its required use in INSERT, UPDATE, MERGE, and DELETE statements in which the parentheses are required.

TOP and Determinism:

As I mentioned earlier, a TOP query doesn’t require an ORDER BY clause. However, such a query is nondeterministic. That is, running the same query twice against the same data might yield different result sets, and both would be correct. The following query returns three

orders, with no rule governing which three are returned:

SELECT TOP (3) orderid, custid, orderdate

FROM Sales.Orders;

But you might get a different output. SQL Server will return the first three rows it happened to access first.

A TOP query can be nondeterministic even when an ORDER BY clause is specified if the ORDER BY list is non unique.

For example, the following query returns the first three orders in order of increasing custid:

SELECT TOP (3) orderid, custid, orderdate

FROM Sales.Orders

ORDER BY custid;

You are guaranteed to get the orders with the lowest custid values. However, because the custid column is not unique, you cannot guarantee which rows among the ones with the same custid values will be returned in case of ties. Again, you will get the ones that SQL

Server happens to access first. One way to guarantee determinism is to add a tiebreaker that makes the ORDER BY list unique.

For example, the primary key:

SELECT TOP (3) orderid, custid, orderdate

FROM Sales.Orders

ORDER BY custid, orderid;

Another way to guarantee determinism is to use the WITH TIES option. When you use WITH TIES, the query generates a result set including any additional rows that have the same values in the sort column or columns as the last row returned. For example, consider the following

query:

SELECT TOP (3) WITH TIES orderid, custid, orderdate

FROM Sales.Orders

ORDER BY custid;

TOP and Input Expressions:

As the input to TOP, SQL Server supports any self-contained expression yielding a scalar result.

An expression that is independent of the outer query can be used—a

                                  Variable or parameter,

                                  An arithmetic expression, or

                                  Even the result of a subquery.

For example, the following query returns the @n most recent orders, where @n is a variable:

DECLARE @n AS INT = 2;

SELECT TOP (@n) orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC;

The following query shows the use of a subquery as the input to TOP. As always, the input to TOP specifi es the number of rows the query returns—for this example, the number of rows returned is the monthly average number of orders. The ORDER BY clause in this example

specifi es that the rows returned are the most recent ones, where orderid is the tiebreaker (higher ID wins):

SELECT TOP (SELECT COUNT(*)/(DATEDIFF(month,

MIN(orderdate), MAX(orderdate))+1)

FROM Sales.Orders)

orderid, orderdate, custid, empid

FROM Sales.Orders

ORDER BY orderdate DESC, orderid DESC;

The average number of monthly orders is the count of orders divided by one more than the difference in months between the maximum and minimum order dates. Because 830 orders in the table were placed during a period of 23 months, the output has the most recent

36 orders.

Note:
If you are moving from Access to SQL Server, be aware that Access, by default, adds the WITH TIES option to the TOP() predicate automatically.

An alternative to TOP() is the SET ROWCOUNT command, which limits any DML command to affecting only n number of rows until it’s turned off with SET ROWCOUNT 0. The issue is that ROWCOUNT isn’t portable either, and it’s been deprecated for INSERT, UPDATE, and DELETE in SQL Server 2008.

Note: I can think of very few reasons to use SELECT TOP without ORDER BY, and I don’t recommend it.

One reason is to serve as a quick reminder of the structure or column names of a table or to find out if the table contains any data at all.

Another reason is to create an empty table with the same structure as another table or query. In this case, you can use SELECT TOP (0) <column list> INTO <table name> FROM . . . . Obviously, you don’t need an ORDER BY clause to indicate “which zero rows” you want to select!

Note Some applications must guarantee determinism. For example, if you’re using the TOP option to implement paging, you don’t want the same row to end up on two successive pages just because the query was nondeterministic. Remember that you can always add the primary key as a tiebreaker to guarantee determinism in case the ORDER BY list is not unique.

SET ROWCOUNT (Transact-SQL)

Causes SQL Server to stop processing the query after the specified number of rows are returned.

Syntax

                    SET ROWCOUNT { number | @number_var }   

Arguments

number | @number_var
Is the number, an integer, of rows to be processed before stopping the specific query.

 

Remarks 

Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in a future 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. For a similar behavior, use the TOP syntax.

To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

Setting the SET ROWCOUNT option causes most Transact-SQL statements to stop processing when they have been affected by the specified number of rows. This includes triggers. The ROWCOUNT option does not affect dynamic cursors, but it does limit the rowset of keyset and insensitive cursors. This option should be used with caution.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

The setting of SET ROWCOUNT is set at execute or run time and not at parse time.

 

Permissions

Requires membership in the public role.

SET ROWCOUNT overrides the SELECT statement TOP keyword if the rowcount is the smaller value.

set rowcount 1

select top (2) id from test2

id

-----------

20

(1 row(s) affected)