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.

Parentheses that delimit expression in TOP is required in INSERT, UPDATE, MERGE, and DELETE statements. For backward compatibility, TOP expression without parentheses in SELECT statements is supported, but we do not recommend this.

If the query includes an ORDER BY clause, the first expression rows, or expression percent of rows, ordered by the ORDER BY clause are returned. If the query has no ORDER BY clause, the order of the rows is arbitrary.

PERCENT

Indicates that the query returns only the first expression percent of rows from the result set.

WITH TIES

Specifies that additional rows be returned from the base result set with the same value in the ORDER BY columns appearing as the last of the TOP n (PERCENT) rows. TOP...WITH TIES can be specified only in SELECT statements, and only if an ORDER BY clause is specified.

Note
The returned order of tying records is arbitrary. ORDER BY does not affect this rule.

TOP cannot be used together with UPDATE and DELETE statements on partitioned views.

The rows referenced in the TOP expression used with INSERT, UPDATE, MERGE, or DELETE are not arranged in any order. TOP n returns n random rows. For example, the following INSERT statement contains the ORDER BY clause, and yet this clause does not affect the rows directly referenced by the INSERT statement.

INSERT TOP (2) INTO Table2 (ColumnB) 
    SELECT ColumnA FROM Table1 
    ORDER BY ColumnA;

The ORDER BY clause in the previous query references only the rows that are returned by the nested SELECT statement. The INSERT statement chooses any two rows returned by the SELECT statement. To make sure that the top two rows from the SELECT subquery are inserted, rewrite the query as follows.

INSERT INTO Table2 (ColumnB) 
    SELECT TOP (2) ColumnA FROM Table1 
    ORDER BY ColumnA;

SQL Server allows for updating views that were created with the TOP clause. Because the TOP clause is included in the view definition, certain rows may disappear from the view because of an update, if the result no longer meets the requirements of the TOP expression. For more information, see Modifying Data Through a View.

The TOP expression in a query does not affect statements that may be executed because of a trigger fired. The inserted and updated tables in the triggers will show only the rows that are truly affected by the INSERT, UPDATE, MERGE, or DELETE statements.

Using TOP in the MERGE Statement

A. Using TOP with variables

USE AdventureWorks2008R2;
GO
DECLARE @p AS int;
SELECT @p=10
SELECT TOP(@p)*
FROM HumanResources.Employee;
GO

B. Using TOP with PERCENT and WITH TIES

USE AdventureWorks2008R2;
GO
SELECT TOP(10) PERCENT WITH TIES
pp.FirstName, pp.LastName, e.JobTitle, e.Gender, r.Rate
FROM Person.Person AS pp 
    INNER JOIN HumanResources.Employee AS e
        ON pp.BusinessEntityID = e.BusinessEntityID
    INNER JOIN HumanResources.EmployeePayHistory AS r
        ON r.BusinessEntityID = e.BusinessEntityID
ORDER BY Rate DESC;

Reference