Artikel
10/04/2012

TOP (Transact-SQL)

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.

Topic link icon Transact-SQL Syntax Conventions

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.

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.

Remarks

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.

Examples

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.

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

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;

Dela via