How Can I Efficiently Paginate SQL Server Results and Get the Total Count?

SQL Server efficient paging and total acquisition

In SQL Server, paged retrieval allows you to fetch data in batches instead of fetching all data at once. This can significantly improve performance, especially when working with large data sets.

Best Practices for SQL Server 2012 and Later

SQL Server 2012 version introduces a simpler paging method. The OFFSET and FETCH clauses provide a straightforward way to retrieve the required number of results while getting the total number.

For example, to get the next 10 rows of data and get the total at the same time:

<code class="language-sql">-- 获取带有行号的接下来的10行
SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RowNum
FROM TableName;

-- 统计总行数
SELECT COUNT(*) AS TotalCount FROM TableName;</code>

Paging using OFFSET and FETCH

Starting with SQL Server 2012, you can explicitly specify the rows to retrieve using the OFFSET and FETCH clauses:

<code class="language-sql">SELECT *
FROM TableName
ORDER BY id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;</code>

Key Notes on OFFSET and FETCH

• When using OFFSET and FETCH, the ORDER BY clause is required.
• OFFSET and FETCH must be used together; OFFSET cannot be used alone.
• OFFSET and FETCH cannot be combined with TOP in the same query.

The above is the detailed content of How Can I Efficiently Paginate SQL Server Results and Get the Total Count?. For more information, please follow other related articles on the PHP Chinese website!