sqlserver分页存储过程

环境： winNT32 ,sqlserver2005

 

众所周知，当我们需要在网页展示数据时，经常会设计到分页的功能，分页很多种实现方法，每种数据库都有自己独有的分页方式，现在笔者以sqlserver为例，解释在sqlserver服务器下如何使用T-sql来实现分页机制：

 

分页基本原理: 在分页技术中，我们需要得到至关重要的两个数据，1，当前页码号，2，每页显示数据的量，，有了这两个数据，即可以定位虚拟表内的一段数据，最早的分页算法是基于top配合not in 语法来实现，这种分页查询的效率极其低下，我们无法使用此种算法用作拥有大量数据的虚拟表，在sqlserver2005中，有函数row_number() ,此函数的作用是在虚拟表内增加一个字段，此字段按顺序标识了每一条数据，在每次查询时会重新生成新的row_number()，再配合between xx and xx则可以实现分页。

 

 

为了测试此理论是否成立，我们写一段t-sql语句，插入500万条数据在表内，然后对其进行查询。

 

declare @count int
set @count = 1
while @count<=5000000
	begin
		insert into test ('test',getdate(),'this is crazy test')
		set @count=@count+1
	end

 

执行完后，test表中含有500万条数据，并且每条数据都具有唯一性，

以下为row_number()分页存储过程

 

create proc Pager_Proc
@pageIndex int,
@pageSize int,
@tableName varchar(20),
@mID varchar(20) 
as
declare @sql varchar(200)
declare @startNumber int
declare @endNumber int 

set @startNumber = (@pageIndex-1)*@pageSize+1
set @endNumber = @pageIndex*@pageSize

set @sql = 'select * from
(select *,ROW_NUMBER() over(order by '+@mID+' asc) as number from '+@tableName+') as test
where test.number between '+str(@startNumber)+' and '+str(@endNumber)+''
exec(@sql)

 

其中我们声明了startNumber和endNumber，这是和top查询所不一样的地方，在between and中需要两个数字型变量，我们可以通过

set @startNumber = (@pageIndex-1)*@pageSize+1

set @endNumber = @pageIndex*@pageSize

来算出我们需要从多少开始，到多少结束。

 

 

以下为调用过程

exec Pager_Proc 50000,100,'test','testID'

其中我们需要指定表名test 和 主键列testID

参数一：从第5万条数据开始

参数二：查出100条。