How to Implement Pagination in SQL Server?

SQL Server paging technology

PostgreSQL uses the LIMIT and OFFSET keywords to easily paginate result sets. So, what is the equivalent syntax in SQL Server?

Microsoft SQL Server paging syntax

SQL Server 2012 and later provides equivalent syntax. How to use:

<code class="language-sql">SELECT email FROM emailTable 
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;</code>

Detailed syntax explanation:

• ORDER BY: Required parameter, specifies the sort order of rows.
• OFFSET: Optional parameter specifying the number of rows to skip before returning the desired result. This example skips 10 lines.
• FETCH NEXT: Required parameter, specifying the number of subsequent rows to be returned. This example returns the next 10 rows.

Example

To select rows 11 to 20 from emailTable, you can use the following query:

<code class="language-sql">SELECT email FROM emailTable 
WHERE user_id=3
ORDER BY Id
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;</code>

Other instructions

• OFFSET Optional, omitting will start from the first line.
• FETCH NEXT Required.
• You can use the ROW_NUMBER() function in conjunction with the OFFSET and FETCH NEXT syntax to implement pagination.

The above is the detailed content of How to Implement Pagination in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!