A Top element may be dragged onto the empty node under the Top Clause.
An integer or decimal numeric literal or parameter may be dragged onto the empty node underneath the Top node, representing the number of rows to return. An option is available via the Properties button to interpret the number as a percentage from 0-100. If more rows are requested than are present, all of the rows are returned without an error.
The Top operator is a non-standard function designed to limit the number of rows returned by a query. If the limited rows are to be returned from the top of an ordered set, the Order By Clause should be used in conjunction with the Top Clause to effect this. The Top operator is evaluated after all other SQL clauses have been evaluated.
A few of the limitations of the Top operator are mentioned here. It may not be used in a correlated subquery, a subquery in a search condition, or a seed or recursive statement in a recursive view or With Recursive clause. Also, it may not be used in conjunction with a Distinct operator, Qualify clause or Sample clause.
Note that the use of the Top operator in conjunction with an Order By clause is equivalent to the use of a Qualify Clause, qualifying by ROW_NUMBER over the Order By columns. When the With Ties option is used, it is equivalent to qualifying by Rank over the Order By columns. The Top operator may however deliver better performance, but not worse.
- Treat value as a Percentage from 0-100 — The integer or decimal number specified is interpreted as a percentage between 0 and 100 when this option is selected.
- Include ‘Ties’ in selected rows — Any rows where the sort value is the same as it is in the last returned row are also returned when this option is selected (in which case more than the requested number of rows may be returned).