How Does SQL Server Handle Pagination Without LIMIT and OFFSET?

SQL Server Pagination: Alternatives to LIMIT and OFFSET

PostgreSQL’s LIMIT and OFFSET syntax can efficiently implement result set paging. However, there is no direct equivalent syntax for SQL Server.

SQL Server paging method

Starting with SQL Server 2012, a powerful solution has emerged:

Grammar:

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

Parameters:

• ORDER BY: required. Determine the sort order of rows.
• OFFSET: Optional. Specify the number of rows to skip.
• FETCH NEXT: required. Determines the number of rows to retrieve after skipping the specified number of rows.

Example:

To select rows 11 to 20 where emailTable is 3 from the user_id table:

<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:

The
• ORDER BY clause is required for paging functionality.
The
• OFFSET clause is optional and allows you to skip a specific number of rows.
The
• FETCH NEXT clause is required and specifies the number of rows to retrieve after the offset.

Reference: https://www.php.cn/link/26fcf9e127023b55bc1dab3feacf45a8

The above is the detailed content of How Does SQL Server Handle Pagination Without LIMIT and OFFSET?. For more information, please follow other related articles on the PHP Chinese website!