TOP (Transact-SQL)

Статья
03/07/2014

Ограничивает число строк, возвращаемых в результирующем наборе запроса до заданного числа или процентного значения в SQL Server 2014. Если предложение TOP используется совместно с предложением ORDER BY, то результирующий набор ограничен первыми N строками отсортированного результата. В противном случае возвращаются первые N строк в неопределенном порядке. Это предложение позволяет указать число строк, возвращаемых инструкцией SELECT или обработанных инструкциями INSERT, UPDATE, MERGE и DELETE.

Применимо для следующих объектов: SQL Server (SQL Server 2008 по текущую версию), База данных SQL Windows Azure (С первоначального выпуска по текущий выпуск).

Значок ссылки на раздел Синтаксические обозначения в Transact-SQL

Синтаксис

[ 
    TOP (expression) [PERCENT]
    [ WITH TIES ]
]

Аргументы

expression
Числовое выражение, определяющее количество возвращаемых строк. При указании PERCENT аргумент expression неявно преобразуется в тип float; в противном случае он преобразуется в тип bigint.
PERCENT
Указывает на то, что запрос возвращает только первые expression процентов строк из результирующего набора. Дробные значения округляются до следующего целого числа.
WITH TIES
Используется, если требуется вернуть две или более строки, которые совместно занимают последнее место в ограниченном результирующем наборе. Требуется использовать с предложением ORDER BY. WITH TIES может привести к тому, что вернется строк больше, чем указано в значении expression. Например, если expression имеет значение 5, но еще 2 строки соответствуют значениям в столбцах ORDER BY в строке 5, то результирующий набор будет содержать 7 строк.

Предложение TOP...WITH TIES может быть задано только в инструкциях SELECT, и только если указано предложение ORDER BY. Порядок возврата связанных записей произволен. ORDER BY не влияет на это правило.

Поддержка совместимости

В целях обратной совместимости скобки в инструкции SELECT необязательны. Рекомендуется всегда заключать TOP в скобки в инструкциях SELECT, чтобы обеспечить согласованность его использования с инструкциями INSERT, UPDATE, MERGE и DELETE, где они являются обязательными.

Совместимость

Выражение TOP в запросе не влияет на инструкции, которые могут быть выполнены из-за срабатывания триггера. Таблицы inserted и deleted в триггерах возвращают только те строки, которые реально обработаны инструкциями INSERT, UPDATE, MERGE и DELETE. Например, если триггер INSERT сработал в результате выполнения инструкции INSERT с предложением TOP,

то SQL Server позволяет обновлять строки через представления. Так как в определение представления может быть включено предложение TOP, определенные строки могут исчезнуть из представления из-за обновления, если строки больше не соответствуют требованиям выражения TOP.

Если предложение TOP указано в инструкции MERGE, то применяется после соединения всей исходной таблицы и всей целевой таблицы и удаления соединенных строк, которые не рассматриваются как предназначенные для выполнения операций вставки, обновления или удаления. Предложение TOP дополнительно сокращает количество соединенных строк до указанного значения, а затем к оставшимся соединенным строкам применяются операции вставки, обновления или удаления без учета порядка. Иными словами, порядок, в котором строки подвергаются операциям, определенным в предложениях WHEN, не задан. Например, указание значения TOP (10) затрагивает 10 строк. Из них 7 могут быть обновлены и 3 вставлены или одна строка может быть удалена, 5 обновлено, 4 вставлено и т. д. Инструкция MERGE выполняет полный просмотр исходной и целевой таблиц, поэтому при использовании предложения TOP для изменения большой таблицы путем создания нескольких пакетов производительность ввода-вывода может снизиться. В этом случае необходимо обеспечить, чтобы во всех подряд идущих пакетах осуществлялась обработка новых строк.

Указывайте предложение TOP в запросе, содержащем операторы UNION, UNION ALL, EXCEPT и INTERSECT, с осторожностью. Существует возможность, что написанный вложенный запрос вернут непредвиденные результаты, поскольку порядок, в котором логически обрабатываются предложения TOP и ORDER BY, не всегда интуитивно понятен, когда эти операторы используются в операции выбора. Например, исходя из следующих таблиц и данных, предположим, что необходимо вернуть самый недорогой красный и синий автомобили. Иными словами, красный седан и синий фургон.

CREATE TABLE dbo.Cars(Model varchar(15), Price money, Color varchar(10));
INSERT dbo.Cars VALUES
    ('sedan', 10000, 'red'), ('convertible', 15000, 'blue'), 
    ('coupe', 20000, 'red'), ('van', 8000, 'blue');

Чтобы получить такие результаты, можно написать следующий запрос.

SELECT TOP(1) Model, Color, Price
FROM dbo.Cars
WHERE Color = 'red'
UNION ALL
SELECT TOP(1) Model, Color, Price
FROM dbo.Cars
WHERE Color = 'blue'
ORDER BY Price ASC;

Ниже приводится результирующий набор.

Model Color Price

------------- ---------- -------

sedan red 10000.00

convertible blue 15000.00

Возвращаются непредвиденные результаты, поскольку инструкция TOP логически выполняется раньше, чем предложение ORDER BY, которое сортирует результаты оператора (в данном случае UNION ALL). Таким образом, предыдущие запросы вернут произвольный красный и произвольный синий автомобили, а затем отсортируют результат объединения по цене. Следующий пример показывает, как правильно написать запрос, который позволит получить нужный результат.

SELECT Model, Color, Price
FROM (SELECT TOP(1) Model, Color, Price
      FROM dbo.Cars
      WHERE Color = 'red'
      ORDER BY Price ASC) AS a
UNION ALL
SELECT Model, Color, Price
FROM (SELECT TOP(1) Model, Color, Price
      FROM dbo.Cars
      WHERE Color = 'blue'
      ORDER BY Price ASC) AS b;

Использование TOP и ORDER BY во вложенной операции выбора гарантирует, что результаты предложения ORDER BY применяются к инструкции TOP, а не к сортировке результата операции UNION.

Ниже приводится результирующий набор.

Model Color Price

------------- ---------- -------

sedan red 10000.00

van blue 8000.00

Ограничения

При использовании TOP в инструкциях INSERT, UPDATE, MERGE и DELETE указанные строки никак не упорядочены и предложение ORDER BY не может быть прямо указано в этих инструкциях. Если необходимо использовать TOP при вставке, удалении или изменении строк в определенном хронологическом порядке, необходимо использовать TOP в сочетании с предложением ORDER BY, указанным в инструкции подзапроса выборки. См. подраздел «Примеры» далее в этом разделе.

Предложение TOP не может быть использовано вместе с инструкциями UPDATE и DELETE для секционированных представлений.

TOP нельзя сочетать с OFFSET и FETCH в одном выражении запроса (в той же области запроса). Дополнительные сведения см. в разделе Предложение ORDER BY (Transact-SQL).

Примеры

Категория	Используемые элементы синтаксиса
Базовый синтаксис	TOP • PERCENT
Включить равные значения	WITH TIES
Ограничение числа строк, обрабатываемых инструкциями DELETE, INSERT и UPDATE	DELETE • INSERT • UPDATE

Базовый синтаксис

В примерах этого раздела показана базовая функциональность предложения ORDER BY с использованием минимально необходимого синтаксиса.

А.Использование ключевого слова TOP со значением константы

В следующих примерах константа указывает число сотрудников, возвращаемых в результирующем наборе запроса. В первом примере возвращаются 10 произвольных строк, так как отсутствует предложение ORDER BY. Во втором примере предложение ORDER BY возвращает 10 сотрудников, последними принятых на работу.

USE AdventureWorks2012;
GO
-- Select the first 10 random employees.
SELECT TOP(10)JobTitle, HireDate
FROM HumanResources.Employee;
GO
-- Select the first 10 employees hired most recently.
SELECT TOP(10)JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY HireDate DESC;

Б.Использование ключевого слова TOP с переменной

В следующем примере переменная указывает число сотрудников, возвращаемых в результирующем наборе запроса.

USE AdventureWorks2012;
GO
DECLARE @p AS int = 10;
SELECT TOP(@p)JobTitle, HireDate, VacationHours
FROM HumanResources.Employee
ORDER BY VacationHours DESC
GO

В.Указание процентов

В следующем примере PERCENT указывает число сотрудников, возвращаемых в результирующем наборе запроса. В таблице HumanResources.Employee содержится 290 сотрудников. Поскольку 5% от 290 является дробным числом, значение округляется до следующего целого числа.

USE AdventureWorks2012;
GO
SELECT TOP(5)PERCENT JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY HireDate DESC;

Включить равные значения

А.Использование ключевого слова WITH TIES для включения строк, соответствующих значениям в последней строке

Следующий пример извлекает первые 10 процентов работников с наибольшей зарплатой и возвращает их в порядке убывания размера зарплаты. Указание параметра WITH TIES позволяет быть уверенным в том, что все работники с зарплатой, равной минимальной возвращенной зарплате (последняя строка), включены в результирующий набор, даже если это приведет к превышению ограничения в 10 процентов работников.

USE AdventureWorks2012;
GO
SELECT TOP(10)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;

Ограничение числа строк, обрабатываемых инструкциями DELETE, INSERT и UPDATE

А.Ограничение числа удаляемых строк с помощью ключевого слова TOP

Если с инструкцией DELETE применяется предложение TOP (n), то операция удаления производится над произвольной выборкой из n строк. Таким образом, инструкция DELETE выбирает любое число (n) строк, которые удовлетворяют условию, указанному в предложении WHERE. Следующий пример удаляет 20 строк из таблицы PurchaseOrderDetail, имеющих дату ранее 1 июля 2002 г.

USE AdventureWorks2012;
GO
DELETE TOP (20) 
FROM Purchasing.PurchaseOrderDetail
WHERE DueDate < '20020701';
GO

Если необходимо с помощью предложения TOP удалять строки в значимом хронологическом порядке, то вместе с ним в инструкции вложенного запроса выборки следует использовать ORDER BY. Следующий запрос удаляет из таблицы PurchaseOrderDetail 10 строк, имеющих самую раннюю дату. Чтобы гарантировать удаление только 10 строк, столбец, указанный в инструкции подзапроса выборки (PurchaseOrderID) должен являться первичным ключом таблицы. Использование неключевого столбца в инструкции подзапроса выборки может привести к удалению более чем 10 строк, если указанный столбец содержит повторяющиеся значения.

USE AdventureWorks2012;
GO
DELETE FROM Purchasing.PurchaseOrderDetail
WHERE PurchaseOrderDetailID IN
   (SELECT TOP 10 PurchaseOrderDetailID 
    FROM Purchasing.PurchaseOrderDetail 
    ORDER BY DueDate ASC);
GO

Б.Ограничение числа вставляемых строк с помощью ключевого слова TOP

В следующем примере создается таблица EmployeeSales и вставляется имя и данные о продажах за текущий год для 5 наиболее успешных сотрудников, случайно выбираемых в таблице HumanResources.Employee. Инструкция INSERT выбирает любые 5 строк, возвращаемых инструкцией SELECT, удовлетворяющих определенному условию в предложении WHERE. Предложение OUTPUT отображает строки, вставляемые в таблицу EmployeeSales. Обратите внимание, что предложение ORDER BY в инструкции SELECT не используется для определения 5 наиболее успешных сотрудников.

USE AdventureWorks2012 ;
GO
IF OBJECT_ID ('dbo.EmployeeSales', 'U') IS NOT NULL
    DROP TABLE dbo.EmployeeSales;
GO
CREATE TABLE dbo.EmployeeSales
( EmployeeID   nvarchar(11) NOT NULL,
  LastName     nvarchar(20) NOT NULL,
  FirstName    nvarchar(20) NOT NULL,
  YearlySales  money NOT NULL
 );
GO
INSERT TOP(5)INTO dbo.EmployeeSales
    OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
    SELECT sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.SalesYTD > 250000.00
    ORDER BY sp.SalesYTD DESC;

Если для вставки строк в значимом хронологическом порядке решено использовать предложение TOP, вместе с ним в инструкции подзапроса выборки следует использовать предложение ORDER BY, как показано в следующем примере. Предложение OUTPUT отображает строки, вставляемые в таблицу EmployeeSales. Обратите внимание, что вставка данных пяти наиболее успешных сотрудников выполняется теперь на основе результатов предложения ORDER BY, а не произвольных строк.

INSERT INTO dbo.EmployeeSales
    OUTPUT inserted.EmployeeID, inserted.FirstName, inserted.LastName, inserted.YearlySales
    SELECT TOP (5) sp.BusinessEntityID, c.LastName, c.FirstName, sp.SalesYTD 
    FROM Sales.SalesPerson AS sp
    INNER JOIN Person.Person AS c
        ON sp.BusinessEntityID = c.BusinessEntityID
    WHERE sp.SalesYTD > 250000.00
    ORDER BY sp.SalesYTD DESC;

В.Ограничение числа обновляемых строк с помощью ключевого слова TOP

В следующем примере предложение TOP используется для обновления строк в таблице. Если в предложении TOP (n) используется инструкция UPDATE, то операция обновления выполняется для произвольного подмножества строк. Таким образом, инструкция UPDATE выбирает любое число (n) строк, которые удовлетворяют условию, указанному в предложении WHERE. В следующем примере 10 случайно выбранных заказчиков переназначаются от одного менеджера по продажам к другому.

USE AdventureWorks2012;
UPDATE TOP (10) Sales.Store
SET SalesPersonID = 276
WHERE SalesPersonID = 275;
GO

Если нужно применить изменения с предложением TOP в определенной последовательности, укажите во вложенном запросе SELECT предложение ORDER BY. В следующем примере изменяется длительность отпуска для 10 сотрудников, имеющих наибольший стаж работы.

UPDATE HumanResources.Employee
SET VacationHours = VacationHours + 8
FROM (SELECT TOP 10 BusinessEntityID FROM HumanResources.Employee
     ORDER BY HireDate ASC) AS th
WHERE HumanResources.Employee.BusinessEntityID = th.BusinessEntityID;
GO