SQL Server 2005的ROW_NUMBER() 与自定义分页方法

自定义分页

对于需要读取什么数据和分页的页面怎么显示,有三个相关的变量:

• Start Row Index – 页面里显示数据的第一行的索引; 这个值可以通过页的索引乘每页显示的记录的条数加1得到. 例如, 如果一页显示10条数据, 那么对第一页来说(第一页的索引为0), 第一行的索引为0 * 10 + 1, or 1; 对第二页来说(索引为1), 第一行的索引为1 * 10 + 1, 即 11.
• Maximum Rows – 每页显示的最多记录的条数. 之所以称为“maximum” rows 是由于最后一页显示的数据可能会比page size要小. 比如, 当以每页10条记录来显示81条时, 最后一页也就是第九页只包含一条记录. 没有页面显示的记录条数会大于Maximum Rows 的值.
• Total Record Count – 显示数据的总条数. 不需要知道页面显示什么数据,但是记录总数会影响到分页. 比如, 如果对81条product记录分页,每页10条,那么总页数为9.

对默认分页来说,Start Row Index是由页索引和每页的记录数加1得到,Maximum Rows 就是每页的记录数.使用默认分页时,不管是呈现哪页的数据,都是要读取全部的数据,所有每行的索引都是已知的,这样获取Start Row Index变的没有价值.而且,记录的总条数是可以通过DataTable的总条数来获取的.

ROW_NUMBER() Keyword
有两种可以把整个要分页的数据和一个row index关联起来的方法.

• 使用SQL Server 2005的ROW_NUMBER() Keyword – SQL Server 2005的新特性,它可以将记录根据一定的顺序排列,每条记录和一个等级相关 这个等级可以用来作为每条记录的row index.
   
• 使用SET ROWCOUNT – SQL Server的 SET ROWCOUNT statement 可以用来指定有多少记录需要处理; table variables 是可以存放表格式的T-SQL 变量, 和temporary tables类似. 这个方法在Microsoft SQL Server 2005 和SQL Server 2000都可以用 (ROW_NUMBER() 方法只能在SQL Server 2005里用).
  
  
  这 个思路是,为要分页的数据创建一个table变量,这个table变量里有一个作为主健的IDENTITY列.这样需要分页的每条记录在table变量里 就和一个row index(通过IDENTITY列)关联起来了.一旦table变量产生,连接数据库表的SELECT语句就被执行,获取需要的记录.SET ROWCOUNT用来限制放到table变量里的记录的数量.
  当SET ROWCOUNT的值指定为Start Row Index 加上Maximum Rows时,这个方法的效率取决于被请求的页数.对于比较前面的页来说– 比如开始几页的数据– 这种方法非常有效. 但是对接近尾部的页来说,这种方法的效率和默认分页时差不多.

本教程用ROW_NUMBER()来实现自定义分页.如果需要知道更多的关于table变量和SET ROWCOUNT的技术,请看 A More Efficient Method for Paging Through Large Result Sets.

以下语句用来使用ROW_NUMBER()将一个等级和返回的每条记录关联:

SELECT  columnList,
            ROW_NUMBER()  OVER (orderByClause)
             FROM  TableName

ROW_NUMBER()返回一个根据指定排序的表示每条记录的等级的值.比如,我们可以用以下居于查看根据价格来排序(降序)的每个product的等级:

SELECT  ProductName, UnitPrice,
            ROW_NUMBER()  OVER ( ORDER   BY  UnitPrice  DESC )  AS  PriceRank
             FROM  Products
            

图5 是在Visual Studio里运行以上代码的结果. 注意product根据价格排序,每行有一个等级.

图 5: 返回的记录里每行有一个Price Rank

注意: ROW_NUMBER() 只是 SQL Server 2005里很多排级的功能中的一种. 想了解更多的ROW_NUMBER()的讨论,包括其它的排级功能,请看 Returning Ranked Results with Microsoft SQL Server 2005.

当使用OVER从句里的ORDER BY 列名(UnitPrice)来排级时,SQL Server会对结果排序.为了提升大数据量查询时的性能,可以为用来排序的列加上非聚集索引.更多的性能考虑参考Ranking Functions and Performance in SQL Server 2005.

ROW_NUMBER()返回的等级信息无法直接在WHERE从句中使 用.而在From后面的Select里可以返回ROW_NUMBER(),并在WHERE从句里使用.比如,下面的语句使用一个From后的Select 返回ProductName,UnitPrice,和ROW_NUMBER()的结果,然后使用一个WHERE从句来返回price rank在11到20之间的product.

SELECT  PriceRank, ProductName, UnitPrice
             FROM
            ( SELECT  ProductName, UnitPrice,
            ROW_NUMBER()  OVER ( ORDER   BY  UnitPrice  DESC )  AS  PriceRank
             FROM  Products
            )  AS  ProductsWithRowNumber
             WHERE  PriceRank  BETWEEN   11   AND   20

更进一步,我们可以根据这个方法返回给定Start Row Index 和Maximum Rows 的页的数据.

SELECT  PriceRank, ProductName, UnitPrice
             FROM
            ( SELECT  ProductName, UnitPrice,
            ROW_NUMBER()  OVER ( ORDER   BY  UnitPrice  DESC )  AS  PriceRank
             FROM  Products
            )  AS  ProductsWithRowNumber
             WHERE  PriceRank  >  StartRowIndex  AND  PriceRank  <=  (StartRowIndex +  MaximumRows)

注意:我们在本教程的后面会看到, ObjectDataSource 提供的StartRowIndex是从0开始的,而ROW_NUMBER()的值从1开始.因此,WHERE从句返回会严格返回PriceRank大于 StartRowIndex而小于StartRowIndex+MaximumRows的那些记录.