This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a demo on SQL Server 2012 Pagination or Custom paging query using OFFSET and FETCH.
Whenever thousands or millions of rows are 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 a stored procedure using two main parameters:
Page number
Page size
SQL Server 2012 introduced powerful FETCH and OFFSET features.
FETCH and OFFSET retires only small portion of rows from the primary result set.
Using FETCH, you can set a total number of rows.
Using OFFSET, you can skip some rows.
First, create a sample table and insert sample 10000 records.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tbl_SamplePaging ( EmpID INT IDENTITY(1,1) ,EmpName VARCHAR(250) ) GO INSERT INTO tbl_SamplePaging(EmpName) VALUES ('ABCD' + CAST((SELECT MAX(EmpID) FROM tbl_SamplePaging) AS VARCHAR)) GO 10000 |
The custom paging query using PageSize and PageNumber parameters:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
DECLARE @PageSize INT = 10, @PageNumber INT = 1; SELECT COUNT(1) OVER() AS TotalCount ,EmpID ,EmpName FROM tbl_SamplePaging ORDER BY EmpID OFFSET (@PageNumber-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY |
The result of first page with 10 records:
