How to write a SQL query for pagination in SQL Server?

In SQL Server, OFFSET and FETCH clauses can be used to achieve pagination. Below is an example SQL statement demonstrating how to use these two clauses:

SELECT *
FROM your_table
ORDER BY your_column
OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY;

In the above example, ‘your_table’ is the name of the table to be queried, and ‘your_column’ is the name of the column to be sorted. The OFFSET clause is used to specify the number of rows to skip (i.e., the page number multiplied by the number of rows to display per page), and the FETCH clause is used to specify the number of rows to retrieve.

This sample query will skip the first 10 rows and retrieve the next 20 rows, achieving the effect of pagination. You can adjust the values in the OFFSET and FETCH clauses as needed to modify the starting position and number of rows per page for pagination.

bannerAds