Microsoft introduced OFFSET FETCH NEXT clause in SQL Server 2012 to paginate data. It replaced TOP and ROW_NUMBER in this use. There are no doubts, it is a step in the right direction as it is ANSI SQL standard.

On the other hand, I was disappointed in my article about the same feature in Oracle 12c, which appeared to be not optimized enough. This time, I am going to check the feature in SQL 2012 and 2014 in more details to verify if it was release too fast (as in Oracle 12c) or not. Of course I will focus on performance as it is my biggest doubt in this case.

What OFFSET FETCH NEXT is

When a database is queried for data, not always all rows that match the join and WHERE conditions are needed. Sometimes a user is interested only in the first 10 rows or rows between 11 and 20, or other. A good example is a database used for an internet forum. Usually, posts are presented on pages. The first page contains the latest posts, the second page consists of posts a little bit older than the first page posts etc. If you open the 5th page, not all posts from the whole history are needed to be read from the database, rendered by web server and displayed by your browser. Only a few posts are needed (those from the 5th page).

The question is – how to write a query that returns only the required rows and it is done in an optimized way?

Let’s assume a query to get all posts looks as below:

SELECT *
FROM posts
ORDER BY post_id DESC

A web page on the forum contains 10 posts and you are interested in the 5th page. Then the first page contains rows from 1 to 10, the second one – 11 to 20 etc. The 5th page contains rows from 51 to 60.

At this moment OFFSET FETCH NEXT feature can be used. The following query demonstrates that:

SELECT *
FROM posts
ORDER BY post_id DESC
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY

OFFSET 50 means the first 50 rows are simply skipped and the next 10 rows are returned (FETCH NEXT 10 ROWS ONLY). Of course all rows before skipping and after are sorted by post_id in the descending order (ORDER BY post_id DESC).

SQL Server 2014 test

I have prepared a test table with about 750 000 rows. If I wanted to retrieve all rows from the table, I would use the following simple query:

SELECT *
FROM MyDatabase.dbo.test

SQL Server 2014 tells me, the following execution plan would be used:

It can be easily inferred from the above plan, all rows would be read by the clustered index scan operation. It is good if all rows are needed, but it is not if I am interested in only 10 rows.

The following query uses OFFSET FETCH NEXT functionality to limit a number of returned rows:

SELECT *
FROM MyDatabase.[dbo].[test]
ORDER BY id
OFFSET 50 ROWS FETCH NEXT 10 ROWS ONLY

My database will use the below execution plan to return me those 10 rows from the 5th page.

Interestingly, the clustered index scan method is also used in this case, but the difference is that only 60 rows are the subject of scanning. It is because the query requests rows from 51 to 60. The next operation – Top, limits those 60 rows to the last 10.

OFFSET FETCH NEXT definitely is optimized in SQL Server 2014.

SQL Server 2012 test

Ok, it is tuned in SQL 2014 but the feature was introduced in SQL 2012. Does it perform well in that version?

I generated an execution plan on SQL Server 2012 and you can see the result below.

It shows exactly the same plan as in the SQL 2014 case. 60 rows are scanned, then limited to the last 10 and returned to the client.

OFFSET FETCH NEXT is also optimized in SQL Server 2012.

Conclusion

If you had any doubts about performance of the OFFSET FETCH NEXT feature in SQL 2012 or 2014? Do not. It is optimized as it supposed to be. I am happy to say Microsoft did a good job (as it supposed to) and optimized the feature it released, not like Oracle.