SQL Server 2012: Custom paging using OFFSET and FETCH NEXT

In this post, I am providing a demo on SQL Server 2012 Pagination or Custom paging query using OFFSET and FETCH.

Whenever thousands or millions of rows is returning from a database at that time paging is required.

Using custom paging we can return only limited data for a particular page.

For example, database stored procedure returned 10,00,00 records at one call. How can you arrange this many data into the application grid or any report?

In this kind of requirement, database paging is playing an important role.

The Application has to call stored procedure using two main parameters,

  • Page number
  • Page size

SQL Server 2012 introduced powerful FETCH and OFFSET features.

FETCH and OFFSET retire only small portion of rows from the main result set.

Using FETCH you can set a total number of rows.

Using OFFSET you can skip a number of rows.

First, create sample table and insert sample 10000 records.

The custom paging query using PageSize and PageNumber parameters:

The result of first page with 10 records:

SQL Server Custom Paging

Anvesh Patel

Leave a Reply

3 Comments on "SQL Server 2012: Custom paging using OFFSET and FETCH NEXT"

Notify of
avatar
Sort by:   newest | oldest | most voted
Leeon
Guest

Hi, just wanted to mention, I enjoyed this post.
It was practical. Continue posting!

Dinesh Sharma
Guest

Hi Anvesh,

I am facing performance bottleneck due to this FETCH NEXT way of pagination as my table contains more than a Billion records. Can you please guide on alternate approach of pagination (fast)?

Thank You!

wpDiscuz