蓝派网(www.lan27.com)-精选网络资源,分享和交流! 文章首页站内搜索在线手册广告代码酷站欣赏万年历
您现在的位置: 蓝派网 >> 文章中心 >> 数据库 >> MS-SQL >> 正文

利用Sql Server 2005的新函数ROW_NUMBER()写的高效分页存储过程

作者:佚名    文章来源:网络    更新时间:2008-10-29 16:17:16

微软最新发布的MSSQL2005,对TSQL进行了小规模的加强,有些函数的确非常实用。就比如ROW_NUMBER()函数,实现了原先一直要用存储过程来进行大数据分页的功能。现在有了这个函数,只要简单一个语句就能解决任何情况下的数据分页,不管你要排序的字段是否唯一。
  个人感觉,这个函数应该能再进的一步优化性能,当然这个工作不是我们所能为,MS不去开发,我们就没办法了。
 程序代码
 select * from (Select ROW_NUMBER() OVER (order by id desc) as pos from [table] where year(birth)=1981) as sp where pos between 1 and 5

  实际底层的工作逻辑不是很清楚,假如是每次执行都需要进行全表排序的话,那真的是太浪费资源了。即使有了索引,不需要再进行全表排序,是否必然会进行一次ROW_NUMBER()函数的全表运算呢?
  我们假设索引都有一个索引表的rowID,就类似于ROW_NUMBER()产生的值。那么事先已经有过索引的,是不是就能直接根据索引表的rowID进行高效分页呢?这种方法应该是可行,具体MSSQL2005中是否有这个功能,还需要进一步学习。
  按照以上方法,应该能简单写出分页的SQL语句:
 程序代码
Select from [table] where year(birth)=1981 and ROW_NUMBER() between 1 and 5 order by id desc

  分析器执行此语句时,对于ROW_NUMBER()函数,首先检查是否有索引匹配,若匹配,则直接访问索引中的rowID进行条件读取,若是无匹配索引,则根据当前排序条件重新进行一次全表的ROW_NUMBER()计算。
不过这个方法可能会增加索引的工作强度,由于每次的索引重建都需要进行一次全表ROW_NUMBER()运算。不过由于索引是多字段及多方向的,例如,一个索引有2个字段,那么就必然需要4个ROW_NUMBER()表,如果索引字段更多,就变得很复杂了。因此,可以选择性的为某些字段的排序方向保存rowID。


  下面是我简单写的一个存储过程,希望对你有用,相对之前的存储分页,性能自然也是非常高效的。个人觉得用存储过程调用,不管是性能上还是使用上,都应该更方便一些。如果你觉得直接程序中用SQL语句调用,那也是完全没问题的。

 程序代码

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create PROCEDURE [dbo].[ShowPage]
@tblName   varchar(255),       -- 表名
@strGetFields varchar(1000) = ''*'',  -- 需要返回的列
@strOrder varchar(255)='''',      -- 排序的字段名
@PageSize   int = 10,          -- 页尺寸
@PageIndex  int = 1,           -- 页码
@strWhere  varchar(1500) = ''''  -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL   varchar(5000)

if @strWhere !=''''
  set @strWhere='' where ''+@strWhere

set @strSQL=''Select * FROM (Select ROW_NUMBER() OVER (''+@strOrder+'') AS pos,''+@strGetFields+'' FROM [''+@tblName+'']''+@strWhere+'') AS sp Where pos BETWEEN ''+str((@PageIndex-1)*@PageSize+1)+'' AND ''+str(@PageIndex*@PageSize)

exec (@strSQL)

发表评论】【打印此文】【关闭窗口】【点击数:
★好玩的休闲小游戏★

本 栏 推 荐

本 栏 热 门

站 内 推 荐

图 文 推 荐