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 the top, but there are differences that 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 SQL?

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

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

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 a 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:

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

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.

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:

When OFFSET is set to 0 no rows are skipped.

Unlike TOP, with OFFSET there are not built-in means to return a percentage of rows. You can however roll your own by calculating the percentage as so:

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.

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:

Summary of Differences

Here is a summary of some of the major differences between TOP and OFFSET & FETCH.

Comparison of TOP to OFFSET & FETCH

In general, if your aim is to just return the first several rows, then I think TOP is a 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.