What Exactly Is Pagination?
Pagination is the process of separating big amounts of data into smaller data sets that are displayed on discrete pages. It's commonly utilized in online applications.
How Does MS SQL Server Pagination Work?
We can accomplish pagination capabilities in MS SQL Server by using OFFSET and FETCH clauses with ORDER BY in a SELECT query.
- OFFSET: The number of rows in the result set that will be skipped. It should be greater than zero.
- FETCH: The number of rows that will be displayed in the result.
Considerations When Using OFFSET and FETCH
- ORDER BY is required when using the OFFSET FETCH Clause.
- FETCH is optional, but OFFSET is required.
- The TOP clause is incompatible with the SELECT statement using OFFSET FETCH.
Let's look at few examples.
In the following example, OFFSET 0 and FETCH NEXT 5 ROWS indicate that no rows are skipped and the next 5 rows in the dataset are returned, rather than the 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
OFFSET 3 and FETCH NEXT 5 ROWS in the example below indicate skip the first 3 rows and retrieve 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
Page Number and Rows Of Page
- PageNumber - Represents the page number
- RowsOfPage - 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
Page Number and Rows Of Page
- PageNumber - Represents the page number
- RowsOfPage - 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
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
