In SQL Server, you can use the TOP clause to limit the rows returned from a query to a certain percentage of the result set.

For example, you could return the top 10% of the results, or whatever percentage you need.

Example 1 – The Full Result Set

First, let’s return the full result set:

SELECT
  AlbumId,
  AlbumName 
FROM Albums
ORDER BY AlbumId;

Result:

+-----------+--------------------------+
| AlbumId   | AlbumName                |
|-----------+--------------------------|
| 1         | Powerslave               |
| 2         | Powerage                 |
| 3         | Singing Down the Lane    |
| 4         | Ziltoid the Omniscient   |
| 5         | Casualties of Cool       |
| 6         | Epicloud                 |
| 7         | Somewhere in Time        |
| 8         | Piece of Mind            |
| 9         | Killers                  |
| 10        | No Prayer for the Dying  |
| 11        | No Sound Without Silence |
| 12        | Big Swing Face           |
| 13        | Blue Night               |
| 14        | Eternity                 |
| 15        | Scandinavia              |
| 16        | Long Lost Suitcase       |
| 17        | Praise and Blame         |
| 18        | Along Came Jones         |
| 19        | All Night Wrong          |
| 20        | The Sixteen Men of Tain  |
| 21        | Yo Wassup                |
| 22        | Busted                   |
+-----------+--------------------------+

So there are 22 rows in the full result set.

Example 2 – Return Top 10% of Results

Now let’s return the top 10 percent of those results:

SELECT TOP(10) PERCENT
  AlbumId,
  AlbumName 
FROM Albums
ORDER BY AlbumId;

Result:

+-----------+-----------------------+
| AlbumId   | AlbumName             |
|-----------+-----------------------|
| 1         | Powerslave            |
| 2         | Powerage              |
| 3         | Singing Down the Lane |
+-----------+-----------------------+

In this case, three rows are returned.

You might notice that 10 percent of 22 is actually 2.2 (not 3). Obviously SQL Server can’t present 2.2 rows, so it rounds the results up.

Example 2 – Rounding Up Instead of Down

In case you’re wondering why SQL Server doesn’t round the results down whenever the fractional part is less than 5, take a look at the following example.

SELECT TOP(1) PERCENT
  AlbumId,
  AlbumName 
FROM Albums
ORDER BY AlbumId;

Result:

+-----------+-------------+
| AlbumId   | AlbumName   |
|-----------+-------------|
| 1         | Powerslave  |
+-----------+-------------+

Remember that the full result set had 22 rows, so 1 percent of 22 rows would mean 0.22 rows should be returned.

And this is precisely why we wouldn’t want to be rounded down. If 0.22 rows are rounded down, no rows would be returned, thus incorrectly leading us to conclude that there were no matches for our query.

Example 3 – Zero Percent

As you might expect, using 0 percent will return zero rows.

SELECT TOP(0) PERCENT
  AlbumId,
  AlbumName 
FROM Albums
ORDER BY AlbumId;

Result:

(0 rows affected)

Example 4 – Maximum Percentage

You can only provide percentage values between 0 and 100.

Here’s an example of trying to use a value higher than 100:

SELECT TOP(120) PERCENT
  AlbumId,
  AlbumName 
FROM Albums
ORDER BY AlbumId;

Result:

Msg 1031, Level 15, State 1, Line 2
Percent values must be between 0 and 100.

Example 5 – Negative Percentages

As mentioned, percentage values must be between 0 and 100, so you’ll get an error if you provide a negative percentage.

SELECT TOP(-10) PERCENT
  AlbumId,
  AlbumName 
FROM Albums
ORDER BY AlbumId;

Result:

Msg 1031, Level 15, State 1, Line 3
Percent values must be between 0 and 100.