In this post, I am providing demo on SQL Server 2012 Pagination or Custom paging query using OFFSET and FETCH.
Whenever thousands or millions of rows is returning from 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 parameter,
- Page number
- Page size
SQL Server 2012 introduced powerful FETCH and OFFSET features.
FETCH and OFFSET retire only small portion of rows from a main result set.
Using FETCH you can set total number of rows which are limited.
Using OFFSET you can skip number of rows which number are defined by OFFESET.
First, create sample table and insert sample 10000 records.
CREATE TABLE tbl_SamplePaging
EmpID INT IDENTITY(1,1)
INSERT INTO tbl_SamplePaging(EmpName)
VALUES ('ABCD' + CAST((SELECT MAX(EmpID) FROM tbl_SamplePaging) AS VARCHAR))
The custom paging query using PageSize and PageNumber parameters:
@PageSize INT = 10,
@PageNumber INT = 1;
COUNT(1) OVER() AS TotalCount
ORDER BY EmpID
OFFSET (@PageNumber-1)*@PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY
The result of first page with 10 records:
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.