The TOP clause allows you to specify the number or percentage of rows to be returned by a SELECT statement. SQL Server 2005 introduced the capability for the TOP clause to also be used in INSERT, UPDATE, and DELETE
statements. The syntax was also enhanced to allow the use of a numeric
expression for the number value rather than having to use a hard-coded
number.
The syntax for the TOP clause is as follows:
SELECT [TOP (numeric_expression) [PERCENT] [WITH TIES]]
FROM table_name ...[ORDER BY...]
DELETE [TOP (numeric_expression) [PERCENT]] FROM table_name ...
UPDATE [TOP (numeric_expression) [PERCENT]] table_name SET ...
INSERT [TOP (numeric_expression) [PERCENT]] INTO table_name ...
numeric_expression must be specified in parentheses. Specifying constants without parentheses is supported in SELECT queries only for backward compatibility. The parentheses around the expression are always required when TOP is used in UPDATE, INSERT, or DELETE statements.
If you do not specify the PERCENT option, the numeric expression must be implicitly convertible to the bigint data type. If you specify the PERCENT option, the numeric expression must be implicitly convertible to float and fall within the range of 0 to 100. The WITH TIES option with the ORDER BY clause is supported only with SELECT statements.
The following example shows the use of a local variable as the numeric expression for the TOP clause to limit the number of rows returned by a SELECT statement:
declare @rows int
select @rows = 5
select top (@rows) * from sales
go
stor_id ord_num ord_date qty payterms title_id
------- -------------------- ----------------------- ------ ------------ --------
6380 6871 2007-09-14 00:00:00.000 5 Net 60 BU1032
6380 722a 2007-09-13 00:00:00.000 3 Net 60 PS2091
6380 ONFFFFFFFFFFFFFFFFFF 2007-08-09 00:00:00.000 852 Net 30 FI1980
7066 A2976 2006-05-24 00:00:00.000 50 Net 30 PC8888
7066 ONAAAAAAAAAA 2007-01-13 00:00:00.000 948 Net 60 CH2480
Allowing the use of a numeric expression rather than a constant for the TOP
command is especially useful when the number of requested rows is
passed as a parameter to a stored procedure or function. When you use a
subquery as the numeric expression, it must be self-contained; it cannot
refer to columns of a table in the outer query. Using a self-contained
subquery allows you to more easily develop queries for dynamic requests,
such as “calculate the average number of titles published per week and
return that many titles which were most recently published”:
SELECT TOP(SELECT COUNT(*)/DATEDIFF(month, MIN(pubdate), MAX(pubdate))
FROM titles)
title_id, pub_id, pubdate
FROM titles
ORDER BY pubdate DESC
go
title_id pub_id pubdate
-------- ------ -----------------------
CH9009 9903 2009-05-31 00:00:00.000
PC9999 1389 2009-03-31 00:00:00.000
FI0375 9901 2008-09-24 00:00:00.000
DR4250 9904 2008-09-21 00:00:00.000
BI4785 9914 2008-09-20 00:00:00.000
BI0194 9911 2008-09-19 00:00:00.000
BI3224 9905 2008-09-18 00:00:00.000
FI0435 9917 2008-09-17 00:00:00.000
FI0792 9907 2008-09-13 00:00:00.000
Note
Be aware that the TOP keyword does not speed up a query if the query also contains an ORDER BY clause. The reason is that the entire result set is selected into a worktable and sorted before the top N rows in the ordered result set are returned.
When using the TOP keyword, you can also add the WITH TIES
option to specify that additional rows should be returned from the
result set if duplicate values of the columns specified in the ORDER BY clause exist within the last values returned. The WITH TIES option can be specified only if an ORDER BY clause is specified. The following query returns the top four most expensive books:
SELECT TOP 4 price, title
FROM titles
ORDER BY price DESC
go
price title
--------------------- --------------------------------------
17.1675 But Is It User Friendly?
17.0884 Is Anger the Enemy?
15.9329 Emotional Security: A New Algorithm
15.894 You Can Combat Computer Stress!
If you use WITH TIES, you can see that there is an additional row with the same price (15.894) as the last row returned by the previous query:
SELECT TOP 4 WITH TIES price, title
FROM titles
ORDER BY price DESC
go
price title
--------------------- ---------------------------------------
17.1675 But Is It User Friendly?
17.0884 Is Anger the Enemy?
15.9329 Emotional Security: A New Algorithm
15.894 The Gourmet Microwave
15.894 You Can Combat Computer Stress!
In versions of SQL Server prior to 2005, if you wanted to limit the number of rows affected by an UPDATE statement or a DELETE statement, you had to use the SET ROWCOUNT statement:
set rowcount 100
DELETE sales where ord_date < (select dateadd(year, 1, min(ord_date)) from sales)
set rowcount 0
SET ROWCOUNT often
was used in this way to allow backing up and pruning of the transaction
log during a purge process and also to prevent lock escalation. The
problem with SET ROWCOUNT is that it applies to the entire current user session. You have to remember to set the rowcount back to 0 to be sure you don’t limit the rows affected by subsequent statements. With TOP, you can more easily specify the desired number of rows for each individual statement:
DELETE top (100) sales
where ord_date < (select dateadd(year, 1, min(ord_date)) from sales)
UPDATE top (100) titles
set royalty = royalty * 1.25
You may be thinking that using TOP in INSERT statements is not really necessary because you can always specify it in a SELECT query, as shown in Listing 1.
Listing 1. Limiting Rows for Insert with TOP in a SELECT Statement
CREATE TABLE top_sales
(stor_id char(4),
ord_num varchar(20),
ord_date datetime NOT NULL,
qty smallint NOT NULL,
payterms varchar(12) ,
title_id dbo.tid NOT NULL)
go
insert top_sales
select top 100 * from sales
where qty > 1700
order by qty desc
|
However, you may find using the TOP clause in an INSERT statement useful when inserting the result of an EXEC command or the result of a UNION operation, as shown in Listing 2.
Listing 2. Using TOP in an Insert with a UNION ALL Query
insert top (50) into top_sales
select stor_id, ord_num, ord_date, qty, payterms, title_id from sales
where qty >= 1800
union all
select stor_id, ord_num, ord_date, qty, payterms, title_id from sales_big
where qty >= 1800
order by qty desc
|
When a TOP (n) clause is used with DELETE, UPDATE, or INSERT, the selection of rows on which the operation is performed is not guaranteed. If you want the TOP(n) clause to operate on rows in a meaningful chronological order, you must use TOP together with ORDER BY in a subselect statement. The following query deletes the 10 rows of the sales_big table that have the earliest order dates:
delete from sales_big
where sales_id in (select top 10 sales_id
from sales_big order by ord_date)
To ensure that only 10 rows are deleted, the column specified in the subselect statement (sales_id)
must be the primary key of the table. Using a nonkey column in the
subselect statement could result in the deletion of more than 10 rows if
the specified column matched duplicate values.
Note
SQL Server Books Online states that when you use TOP (n) with INSERT, UPDATE, and DELETE operations, the rows affected should be a random selection of the TOP(n) rows from the underlying table. In practice, this behavior has not been observed. Using TOP (n) with INSERT, UPDATE, and DELETE appears to affect only the first n matching rows. However, because the row selection is not guaranteed, it is still recommended that you use TOP together with ORDER BY in a subselect to ensure the expected result.
