TOP Operator in SQL Server

Limits the rows returned in a query result set to a specified number of rows or percentage of rows in SQL Server 2014. 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.

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.

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.