Specifies that only the first set of rows will be returned from the query result. The set of rows can be either a number or a percent of the rows. The TOP expression can be used in SELECT, INSERT, UPDATE, MERGE, and DELETE statements.
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
When specified in the MERGE statement, the TOP clause is applied after the entire source table and the entire target table are joined and the joined rows that do not qualify for an insert, update, or delete action are removed. The TOP clause further reduces the number of joined rows to the specified value and the insert, update, or delete actions are applied to the remaining joined rows in an unordered fashion. That is, there is no order in which the rows are distributed among the actions defined in the WHEN clauses. For example, specifying TOP (10) affects 10 rows; of these rows, 7 may be updated and 3 inserted, or 1 may be deleted, 5 updated, and 4 inserted, and so on.
Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches. In this scenario, it is important to ensure that all successive batches target new rows. For more information, see Optimizing MERGE Statement Performance.
A. Using TOP with variables
The following example uses a variable to obtain the first 10 employees that are listed in the dbo.Employee table of the AdventureWorks2008R2 database.
B. Using TOP with PERCENT and WITH TIES
The following example obtains the top 10 percent of all employees with the highest salary and returns them in descending order according to salary base rate. Specifying WITH TIES makes sure that any employees that have salaries equal to the lowest salary returned are also included in the result set, even if doing this exceeds 10 percent of employees.
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;