Pagination in MS SQL Server
source link: https://dzone.com/articles/pagination-in-ms-sql-server
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
What Is Pagination?
Pagination is the process of dividing large data into smaller data sets in discrete pages. It is widely used in web applications.
How Does Pagination Work in MS SQL Server?
In MS SQL Server, we can achieve the pagination functionality by using OFFSET
and FETCH
clauses with ORDER BY
in a SELECT
statement.
OFFSET
: Represents the number of rows to be skipped from the result set. It should be 0 or greater than 0.FETCH
:Represents the number of rows to be displayed in the result.
Important Points to Consider While Using OFFSET and FETCH:
ORDER BY
is mandatory to the useOFFSET FETCH
Clause.OFFSET
is mandatory andFETCH
is optional.- The
TOP
clause cannot be used in theSELECT
statement withOFFSET FETCH
.
Let's see examples:
In the below example, OFFSET 0
and FETCH NEXT 5 ROWS
means that skip no rows and return the next 5 rows in the dataset, which first 5 rows in the dataset.
SELECT EmployeeKey,FirstName,LastName,Title,EmailAddress,
Phone,EmergencyContactName,DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
ORDER BY EmployeeKey
OFFSET 0 ROWS
FETCH NEXT 5 ROWS ONLY
In the below example, OFFSET 3
and FETCH NEXT 5 ROWS
mean skip the first 3 rows and return the next 5 rows in the dataset.
SELECT EmployeeKey,FirstName,LastName,Title,EmailAddress,
Phone,EmergencyContactName,DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
ORDER BY EmployeeKey
OFFSET 3 ROWS
FETCH NEXT 5 ROWS ONLY
PageNumber
RowsOfPage
PageNumber
— Represents the page numberRowsOfPage
— Represents the no of rows on a page
Now, to calculate the number of rows to be skipped when we move to the next page the (@PageNumber-1) * @RowsOfPage
formula is being used in the OFFSET
, and the number of rows will be returned in FETCH
.
Here, PageNumber
is 2 and RowsOfPage
is 5, which means return the dataset for page number 2.
DECLARE @PageNumber AS INT
,@RowsOfPage AS INT
SET @PageNumber=2
SET @RowsOfPage=5
SELECT EmployeeKey,FirstName,LastName,Title,EmailAddress,
Phone,EmergencyContactName,DepartmentName
FROM dbo.DimEmployee WITH(NOLOCK)
ORDER BY EmployeeKey
OFFSET (@PageNumber-1) * @RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY
Conclusion
In the real-time application, this complete logic can be written in the stored procedure which is called on clicking the "Next" or page number button in the web application to display the set of records on the page.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK