大體思路就是利用sqlserver2005最新提供的函數使用新方法[row_number() over(order by xxx)]為數據加入行號來給數據添加行號，以此來方便的進行數據檢索。
通過試驗，251萬條數據進行查詢僅需3秒左右。
而使用sqlserver2000的存儲過程(點這裡)需要7秒左右
調用方法:
exec proc_fenye2005 'testbigdata','*','id desc','id%2=0',200,20
--此存儲過程，將返回2個結果集，第一個信息集，第二個數據集
create proc proc_fenye2005
@tablename varchar(50),--表名
@fIElds varchar(5000)='*',--字段名(全部字段為*)
@orderfIEld nvarchar(4000),--排序字段(必須!支持多字段)
@sqlwhere varchar(5000)=null,--條件語句(不用加where)
@currentpage int,--第N頁
@pagesize int--每頁行數
as
begin
begin tran;--開始事務
declare @rowcount int,@totalpage int,@tmpcount int,@sql nvarchar(4000); if (@sqlwhere='' or @sqlwhere=null)
set @sql='select @rowcount=count(*) from '+@tablename;
else
set @sql='select @rowcount=count(*) from '+@tablename+' where '+@sqlwhere;
exec sp_executesql @sql,N'@rowcount int output',@rowcount output;--計算總記錄數 set @totalpage=ceiling((@rowcount+0.0)/@pagesize);
if (@sqlwhere='' or @sqlwhere=null)
set @sql='select * from (select row_number() over(order by '+@orderfield+') as rowid,'+@fIElds+' from '+@tablename;
else
set @sql='select * from (select row_number() over(order by '+@orderfield+') as rowid,'+@fIElds+' from '+@tablename+' where '+@sqlwhere; if @currentpage<1 set @currentpage=1;
if @currentpage>@totalpage set @currentpage=@totalpage; set @tmpcount=(@currentpage-1)*@pagesize+1;
set @sql=@sql + ') as '+@tablename+' where rowid between '+convert(varchar(50),@tmpcount)+' and '; set @tmpcount = @tmpcount+@pagesize-1;
if @tmpcount>@rowcount set @tmpcount=@rowcount;
set @sql=@sql+convert(varchar(50),@tmpcount);
exec(@sql);
if @@error <> 0
begin
rollback tran;
set @currentpage=0;
set @pagesize=0;
set @rowcount=0;
set @totalpage=0;
end
else
begin
commit tran;
end
select @currentpage as [currentpage],@pagesize as [pagesize],@rowcount as [rowcount],@totalpage as [totalpage];
end
