T-SQL 101: 26 Limiting output rows in SQL Server T-SQL queries by using the TOP operator

The TOP operator is used to restrict the number of rows being returned. If you’re familiar with other database engines, you might have used a LIMIT operator. TOP is a bit like that.

You can see that in the first query shown below:

If you look at the bottom query, you can see that it can also be used to limit the rows to a percentage instead of a number of rows.

In the first example,  the query is asking for the first 10 product rows when you place them in descending order by Description.

In the second example, the query is asking for the first 5% of the same rows. Keep in mind that it will be doing integer division on the total number of rows to work out that five percent, but usually that’s close enough.

Not a great design

I’m not a fan of how the TOP operator was designed.

First problem is that if you have the default order (i.e. ascending), it’s really returning the bottom 10 rows, not the top 10. For that reason, it really should have been called FIRST, not TOP.

Second problem is that it uses the same ORDER BY clause to determine which rows to return, that the query uses to determine the order of the output. What would you do in the first query, if you wanted the rows to be selected by description descending but wanted the rows returned in description ascending? It really should have had a separate ORDER BY clause, like the one used in an OVER clause that we’ll see in a later blog post.

TOP should almost always be used with an ORDER BY clause or you don’t know which rows would be returned.

Variables?

Instead of saying TOP (10) or TOP (5) PERCENT, you can also put a variable there inside the brackets like this:

TOP(@RowsToReturn)

We’ll talk more about variables in a later blog post.

It’s also worth noting that even though you can omit the brackets like this:

TOP 10 TOP 5 PERCENT

you should always put them there. In fact, if you use a variable rather than a constant number, you must have the brackets so for that reason, I tend to put them in all the time.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2019-07-15