What is Pagination in SQL Server 2012?

There are instances when you want to display large result sets to the end user. The best way to display large result set is to split them i.e.  apply pagination. So developers had their own hacky ways of achieving pagination using “top”, “row_number” etc. But from SQL Server 2012 onwards we can do pagination by using “OFFSET” and “FETCH’ commands.

For instance let’s says we have the following customer table which has 12 records. We would like to split the records in to 6 and 6. 

So doing pagination is a two-step process: -

• First mark the start of the row by using “OFFSET” command.
• Second specify how many rows you want to fetch by using “FETCH” command.

You can see in the below code snippet we have used “OFFSET” to mark the start of row from “0”position. A very important note order by clause is compulsory for “OFFSET” command.

 Collapse | Copy Code

select * from
tblcustomer order by customercode
offset 0 rows – start from zero

In the below code snippet we have specified we want to fetch “6” rows from the start “0”position specified in the “OFFSET”.

 Collapse | Copy Code

fetch next 6 rows only

Now if you run the above SQL you should see 6 rows.

To fetch the next 6 rows just change your “OFFSET” position. You can see in the below code snippet I have modified the offset to 6. That means the row start position will from “6”.

 Collapse | Copy Code

select * from
tblcustomer order by customercode
offset 6 rows



fetch next 6 rows only

The above code snippet displays the next “6” records , below is how the output looks.