How to Implement Pagination in SQL Server Using OFFSET and FETCH?

Use OFFSET and FETCH to implement paging in SQL Server

SQL Server has no direct equivalent of the LIMIT clause in MySQL or SQL. However, it provides an alternative way to achieve similar functionality using the OFFSET and FETCH clauses.

OFFSET and FETCH

To implement pagination using OFFSET and FETCH, follow these steps:

1. Sort data: Use the ORDER BY clause to sort the data in the desired order.
2. Skip rows using OFFSET: The OFFSET clause specifies how many rows at the beginning of the result set to skip.
3. Limit the number of rows using FETCH: The FETCH clause specifies the number of rows to return after skipping offset rows.

Example:

SELECT *
FROM sys.databases
ORDER BY name
OFFSET 5 ROWS
FETCH NEXT 5 ROWS ONLY;

This query will skip the first 5 rows (OFFSET 5 ROWS) and return the next 5 rows (FETCH NEXT 5 ROWS ONLY).

Alternatives for older SQL Server versions:

For SQL Server versions prior to 2012, a less efficient alternative exists:

• Use TOP and ORDER BY: This can be confusing and may not handle pagination correctly when using indexes.
• Using ROW_NUMBER() and WHERE: This involves creating a temporary table with a row number column and filtering based on the row number.

The reason for missing LIMIT

The LIMIT clause is missing in SQL Server primarily for historical and architectural reasons. Microsoft designed SQL Server with a focus on high performance and data integrity, and the OFFSET and FETCH mechanisms are considered more efficient and versatile for paging. However, in recent versions, SQL Server has been updated to include ROW_NUMBER() and other optimizations, making OFFSET and FETCH more comparable to LIMIT.

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