幾種常用方法介紹
1. 二次 TOP
這種方法效率較低,問題主要處在那個 not in 上面，另外如果Id 是可重復的，得出的結果是
GO
if exists (select * from sysobjects where id = object_id('PagedProc') and type = 'p')
drop procedure PagedProc
GO
create procedure PagedProc
@currentpage int, -- page no
@pagesize int --page size
as
declare
@sqlstr nvarchar(4000) --Query string
if @currentpage = 1
begin
set @sqlstr = 'SELECT TOP ' + Str(@pagesize) + '* from r_student order by Id'
end
else
begin
set @sqlstr = 'SELECT TOP ' + Str(@pagesize) + ' * from r_student where id not in';
set @sqlstr = @sqlstr + '(SELECT TOP '+ Str((@currentpage-1)*@pagesize) + ' id from r_student order by Id)'
end
exec (@sqlstr)
GO
2. ROWNUMBER
這個方法不受排序字段，以及重復鍵等的約束，非常通用。效率也不錯。說白了，就是先將查詢結果 存到臨時表中，
並為這個臨時表提供一個自增長的索引字段，然後根據這個字段進行查詢范圍。
if exists (select * from sysobjects where id = object_id('PagedProcUseROW_NUMBER') and type = 'p')
drop procedure PagedProcUseROW_NUMBER
GO
create procedure PagedProcUseROW_NUMBER
@currentpage int, -- page no
@pagesize int --page size
as
begin
WITH student AS
(
SELECT *,
ROW_NUMBER() OVER (ORDER BY Id) AS 'RowNumber'
FROM r_student
)
SELECT *
FROM student
WHERE RowNumber BETWEEN (@currentpage-1)*@pagesize + 1 AND (@currentpage)*@pagesize;
end
GO
