怎样使用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.