What is the Difference between TOP and OFFSET & Fetch?

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.

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.

Top

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 is mainly 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 used with or without an 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.

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, users will just browse the beginning portion of results.

TOP can display ties value – 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

OFFSET and FETCH are used 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.

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 differences 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.