如何使用SQLServer实现分页查询（sqlserver做分页）

Paging query is one of the most basic functions of SQL. In order to improve the query efficiency, we can use paging query to query only several rows of data each time. In this way, we can save more time and space than loading all query data into memory at once. In this paper, we will introduce how to use SQLServer to realize paging query.

（A）Using rownumber

The most ideal and reliable way for SQL Server to implement paging query is to use the rownumber function of SQL. The syntax is as follows:

SELECT *

FROM (SELECT * , ROW_NUMBER() OVER (ORDER BY ShipmentDate) as rowNum

FROM TBL_SHIPMENT ) AS pagedResult

WHERE rowNum BETWEEN @startPage AND @endPage

The query results returned by this method are very accurate and meet the requirements of our paging query. In the above syntax, we can see that two parameters @startPage and @endPage are used as the start and end page conditions. It also contains an ordering clause that orders the rows by column shipmentdate (in SQL Server 2012 and above, we can use the OFFSET‐FETCH clause to sort results).

（B）Using top

Besides the rownumber method mentioned above, we can also use the TOP clause of SQL to fetch a certain number of rows from an ordered set of rows. The syntax is as follows:

SELECT TOP (@pagesize) *

FROM (SELECT * , ROW_NUMBER() OVER (ORDER BY ShipmentDate) as rowNum

FROM TBL_SHIPMENT ) AS paged

WHERE rowNum @startPage

This syntax is also commonly used in paging query. The main difference between this syntax and the rownumber method mentioned above is that we don’t need to calculate the @endPage parameter every time here. When we page results in batches, we just need to pass the page size parameter.

（C）Conclusion

To sum up, the two methods mentioned above can help us realize the paging query of SQLServer very well. There are a variety of ways to implement it depending on the actual situation, and the above explanation is just a part of it. Therefore, we need to choose a more suitable method according to the actual situation and conditions of our project.

我想要获取技术服务或软件
服务范围：MySQL、ORACLE、SQLSERVER、MongoDB、PostgreSQL 、程序问题
服务方式：远程服务、电话支持、现场服务，沟通指定方式服务
技术标签：数据恢复、安装配置、数据迁移、集群容灾、异常处理、其它问题

本站部分文章参考或来源于网络，如有侵权请联系站长。
数据库远程运维 如何使用SQLServer实现分页查询（sqlserver做分页）