In the past when you would write a query and a large set of results would return, one would typically use the “TOP” or “row number” statements in the query to help reduce the load.
In SQL Server 2012 and onwards, a new set of commands has been made available. You can now apply pagination by using “OFFSET” and “FETCH” commands.
For instance, let’s says we have the following customer table which has 24 records (Displaying 12 of 24 records in table below). We would like to split the records into 4 pages of 6 results each.
Doing pagination is a two-step process:
- First mark the start of the row by using “OFFSET” command. (begin at 0)
- Second specify how many rows you want to fetch by using “FETCH” command. (Display 6 results)
You can see in the query below the use of “OFFSET” to mark the start of row from position. A very important note order by clause is compulsory for “OFFSET” command.
select * from tblcustomer
order by customercode Asc
offset 0 rows
In the below code snippet we have specified we want to fetch “6” rows by beginning at position “0” as specified in the “OFFSET” command.
Now if you run the above SQL you should see 6 rows.
To fetch the following 6 rows, simply replace the Offset value of 0 to 6 (this is where you want the next results to begin at).
select * from tblcustomer
order by customercode Asc
offset 6 rows
The above query above displays the next 6 records, below is how the output looks.
You would then repeat the previous step twice, first by replacing the Offset value from 6 to 12 and then from 12 to 18 in order to return the remaining 12 values of the total 24.
Leave a Comment