In SQL Server, you can use the TOP clause to limit the rows returned from a query result set. This clause provides similar functionality to LIMIT in MySQL, and ROWNUM in Oracle, although there are differences in how each of these work.
Below are examples of using the TOP clause to limit the result set in SQL Server.
Example 1 – Basic Usage
Here’s a basic example of how TOP works:
SELECT TOP(3) * FROM Albums;
Result:
+-----------+-----------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+-----------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 2 | Powerage | 1978-05-05 | 2 | 1 | | 3 | Singing Down the Lane | 1956-01-01 | 6 | 3 | +-----------+-----------------------+---------------+------------+-----------+
In this case I limited the results to just three rows.
Let’s run the query again, but this time without the TOP clause:
SELECT * FROM Albums;
Result:
+-----------+--------------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+--------------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 2 | Powerage | 1978-05-05 | 2 | 1 | | 3 | Singing Down the Lane | 1956-01-01 | 6 | 3 | | 4 | Ziltoid the Omniscient | 2007-05-21 | 5 | 1 | | 5 | Casualties of Cool | 2014-05-14 | 5 | 1 | | 6 | Epicloud | 2012-09-18 | 5 | 1 | | 7 | Somewhere in Time | 1986-09-29 | 1 | 1 | | 8 | Piece of Mind | 1983-05-16 | 1 | 1 | | 9 | Killers | 1981-02-02 | 1 | 1 | | 10 | No Prayer for the Dying | 1990-10-01 | 1 | 1 | | 11 | No Sound Without Silence | 2014-09-12 | 9 | 4 | | 12 | Big Swing Face | 1967-06-01 | 4 | 2 | | 13 | Blue Night | 2000-11-01 | 12 | 4 | | 14 | Eternity | 2008-10-27 | 12 | 4 | | 15 | Scandinavia | 2012-06-11 | 12 | 4 | | 16 | Long Lost Suitcase | 2015-10-09 | 7 | 4 | | 17 | Praise and Blame | 2010-06-26 | 7 | 4 | | 18 | Along Came Jones | 1965-05-21 | 7 | 4 | | 19 | All Night Wrong | 2002-05-05 | 3 | 2 | | 20 | The Sixteen Men of Tain | 2000-03-20 | 3 | 2 | | 21 | Yo Wassup | 2019-03-12 | 9 | 3 | | 22 | Busted | 1901-05-11 | 9 | 3 | +-----------+--------------------------+---------------+------------+-----------+
So we can see that the first query returned only the first three from a larger set.
Example 2 – Using the ORDER BY Clause
Microsoft states that it’s best practice to always use the ORDER BY when using the TOP clause. This is because, it’s the only way to predictably indicate which rows are affected by TOP.
Therefore, we could rewrite the first example to the following:
SELECT TOP(3) * FROM Albums ORDER BY AlbumId;
Result:
+-----------+-----------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+-----------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 2 | Powerage | 1978-05-05 | 2 | 1 | | 3 | Singing Down the Lane | 1956-01-01 | 6 | 3 | +-----------+-----------------------+---------------+------------+-----------+
It’s important to understand how ordering affects the results. Otherwise you could end up with unexpected results.
Here’s what happens if I use the same query again, but order by a different column:
SELECT TOP(3) * FROM Albums ORDER BY ArtistId;
Result:
+-----------+-------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+-------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 7 | Somewhere in Time | 1986-09-29 | 1 | 1 | | 8 | Piece of Mind | 1983-05-16 | 1 | 1 | +-----------+-------------------+---------------+------------+-----------+
Inserting, Deleting, & Updating in Order
Note that, although you can use the TOP clause in INSERT, UPDATE, MERGE, and DELETE statements, you can’t directly specify the ORDER BY clause in these statements. However, you can use a sub-select statement to insert, delete, or modify rows in a meaningful chronological order.
Example 3 – Using the WITH TIES Argument
You can use the optional WITH TIES argument to return all rows that tie for last place in the limited result set. This is only applicable (and can only be used) when using the ORDER BY clause.
If the ORDER BY clause causes two or more rows to tie for last place, using WITH TIES, will cause all of them to be returned. This can cause more rows to be returned than you actually specify.
This is easier explained with an example.
SELECT TOP(3) WITH TIES * FROM Albums ORDER BY ArtistId;
Result:
+-----------+-------------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+-------------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 7 | Somewhere in Time | 1986-09-29 | 1 | 1 | | 8 | Piece of Mind | 1983-05-16 | 1 | 1 | | 9 | Killers | 1981-02-02 | 1 | 1 | | 10 | No Prayer for the Dying | 1990-10-01 | 1 | 1 | +-----------+-------------------------+---------------+------------+-----------+
Here, I specify that only the top 3 rows should be returned, but 5 are actually returned. This is because there are 5 rows using the same ArtistId, and so rows 3 – 5 are all tying for last place. In this case I use WITH TIES to return them all.
If I remove WITH TIES, only 3 rows are returned:
SELECT TOP(3) * FROM Albums ORDER BY ArtistId;
Result:
+-----------+-------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+-------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 7 | Somewhere in Time | 1986-09-29 | 1 | 1 | | 8 | Piece of Mind | 1983-05-16 | 1 | 1 | +-----------+-------------------+---------------+------------+-----------+
Note that the WITH TIES argument can only be specified in SELECT statements, and only if they use the ORDER BY clause. Also, the returned order of tying records is arbitrary.
Example 4 – Using Percentages
You also have the option of specifying a percentage value instead of a set number of rows. To do this, use the PERCENT argument.
Example:
SELECT TOP(10) PERCENT * FROM Albums ORDER BY AlbumId;
Result:
+-----------+-----------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+-----------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 2 | Powerage | 1978-05-05 | 2 | 1 | | 3 | Singing Down the Lane | 1956-01-01 | 6 | 3 | +-----------+-----------------------+---------------+------------+-----------+
Note that fractional values are rounded up to the next integer value. In this case, 10 percent of 22 rows is 2.2, but because it was rounded up, we end up with 3 rows.
So doubling the percentage, won’t necessarily result in double the number of rows:
SELECT TOP(20) PERCENT * FROM Albums ORDER BY AlbumId;
Result:
+-----------+------------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+------------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 2 | Powerage | 1978-05-05 | 2 | 1 | | 3 | Singing Down the Lane | 1956-01-01 | 6 | 3 | | 4 | Ziltoid the Omniscient | 2007-05-21 | 5 | 1 | | 5 | Casualties of Cool | 2014-05-14 | 5 | 1 | +-----------+------------------------+---------------+------------+-----------+
In this case, 20 percent of 22 is 4.4. Once again, it’s rounded up, and we get 5 rows.
Example 5 – Removing the Parentheses
It’s possible to remove the parentheses when using the TOP clause, however, it’s not recommended.
Either way, here’s an example of removing the parentheses from the previous example:
SELECT TOP 20 PERCENT * FROM Albums ORDER BY AlbumId;
Result:
+-----------+------------------------+---------------+------------+-----------+ | AlbumId | AlbumName | ReleaseDate | ArtistId | GenreId | |-----------+------------------------+---------------+------------+-----------| | 1 | Powerslave | 1984-09-03 | 1 | 1 | | 2 | Powerage | 1978-05-05 | 2 | 1 | | 3 | Singing Down the Lane | 1956-01-01 | 6 | 3 | | 4 | Ziltoid the Omniscient | 2007-05-21 | 5 | 1 | | 5 | Casualties of Cool | 2014-05-14 | 5 | 1 | +-----------+------------------------+---------------+------------+-----------+
Microsoft recommends that you always use the parentheses, as it provides consistency with its required use in INSERT, UPDATE, MERGE, and DELETE statements.
The parentheses are optional for backward compatibility reasons.