Both TOP and OFFSET & FETCH can be used to limit the number of rows returned. OFFSET and FETCH can return similar results to top, but there are differences which may influence which method is best for you to use in your given situation.
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide Getting Started Using SQL Server.
What is the Difference between TOP and OFFSET & Fetch?
In the following sections we cover TOP and OFFSET & FETCH. I think once you read both sections you’ll understand their differences. These are also summarized in a table at the end of this article.
SQL TOP is used to return a specific number of rows from a query result.
The TOP clause has been available for some time in older versions of SQL server, such as SQL 2005. TOP isn’t ANSI compliant and its use manly limited to Microsoft products such as SQL Server and MS-Access.
The TOP clause is used in the SELECT statement’s column list and can be use with or without an SQL ORDER BY.
For example to select the ten least costly products use
SELECT TOP 10 Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost
Though the ORDER BY isn’t required, it is best practice, as without it, you have no guarantee for which rows are returned.
Check out Our Video – Select The Top Row From Your Result
Top with Percentages
One item unique to TOP is the ability to return a percentage of rows. The statement
SELECT TOP 10 PERCENT Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost
returns the top 51 products with the lowest StandardCost (i.e. 504 rows x 10% = 50.4, rounded up to 51).
You can also select specified number of records from the bottom of a query result. To do this you can use a trick to order the records in descending order as so:
SELECT TOP 10 Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost DESC
Keep in mind when the results are sorted in descending order, the largest amounts are returned first. Because of this, TOP returns those products with the highest standard cost.
TOP can be very useful on large results as returning thousands of rows can affect performance. In many cases uses will just browse the beginning portion of results.
TOP can display ties vale – WITH TIES allows you to also display additional records that have the same value as the last row from the base result.
OFFSET and FETCH
Use OFFSET and FETCH to return a window of records from a result set. OFFSET specifies how many rows to skip within the result, and FETCH specifies how many rows from that point forward to return in the result.
Both OFFSET and FETCH were recently introduced in SQL Server 2012 and are ANSI compliant.
You can use OFFSET without FETCH, but FETCH can’t be used by itself. Regardless, OFFSET must be used with an ORDER BY clause. The reason is simple as OFFSET and FETCH are part of the ORDER BY clause.
In this example the first ten rows of the result are skipped, then the next 10 displayed in the result.
SELECT Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
You can replicate the rows returned from TOP using OFFSET and FETCH provided the results are ordered. The following statement is the same as returning the TOP 10:
SELECT Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
When OFFSET is set to 0 no rows are skipped.
Unlike TOP, with OFFSET there is not built in means to return a percentage of rows. You can however roll your own by calculating the percentage as so:
SELECT Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost OFFSET 0 ROWS FETCH NEXT (SELECT CAST(CEILING(COUNT(*) * .1) as INT) FROM Production.Product) ROWS ONLY
The trick is in the subquery. By calculating the number of rows in the result we’re able to then obtain a percentage. Here is the subquery. If you wish, you can run this in the SQL Server Management Studio.
SELECT CAST(CEILING(COUNT(*) * .1) as INT) FROM Production.Product
There are several items to note:
- CEILING rounds numbers up. In our example CEILING(50.4) returns 51.0. This mimics the behavior of TOP.
- FETCH requires an integer value so CAST is used to make that conversion. Thus 51.0 becomes, 51
To return the bottom 10 records of a result there are two methods. We can reverse the sort order as we did for TOP:
SELECT Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY
Or we can preserve the sort order and calculate the offset such that all rows up to the last ten are skipped. We explore this technique in more detail in the SQL lesson about OFFSET and FETCH.
Summary of Differences
Here is a summary of some the major difference between TOP and OFFSET & FETCH.
In general, if your aim is to just return the first several rows, then I think TOP is simple solution, however; if you’re looking for a paging solution, or have a need to select rows in the middle or bottom of the query results without affecting the original sort order, then I think OFFSET & FETCH are best.
TOP cannot be used in a subquery but FETCH can.
I didn’t know that! Thanks for letting me know.
Nice example. Thanks. Are there performance differences, in other words, which one is faster?
Really when someone doesn’t know after that its up to other viewers that they
will assist, so here it takes place.
In the table comparing TOP to OFFSET and FETCH, for the value “Limit number rows returned”, you have that TOP set to “No”. However, if I specify SELECT TOP 10, doesn’t that in fact limit the number of rows to 10? Shouldn’t that be set to “Yes”?
Good catch. You’re right, it should be yes.
I suppose I was so excited to talk about OFFSET and FETCH I got carried away and got “down” on TOP. My bad… :)
Trying to study for the 70-461. Any tips?
Many of the topics covered on the 70-461 exam are covered on my Start Here page: https://www.essentialsql.com/getting-started/
So far I’ve covered most the topics on SELECT — more to come in a couple of weeks.