MSSQL简单的分页存储过程

drop table t1
create table t1
(
 id int,
 s1 int
)

Insert into t1 values(1,4)
Insert into t1 values(2,5)
Insert into t1 values(3,5)
Insert into t1 values(4,4)
Insert into t1 values(5,3)
Insert into t1 values(6,2)
Insert into t1 values(7,1)

select * from t1 order by s1 desc,id desc

--select top [PageSize] * from t1 where id not in
--(select top [PageSize-1*PageNo] id from t1 order by s1 desc,id desc)
-- order by s1 desc ,id desc

--PageSize -每页显示多少条
--PageNo   -第几页

select top 3 * from t1 where id not in
(select top 3 id from t1 order by s1 desc,id desc)
 order by s1 desc ,id desc

以上的超过10W条就慢了.

如果用sql2005 就简单很多.

--添加数据

Declare @i int
set @i=1
while(@i<200000)
begin
 insert into t1 values(@i+1,@i)
 set @i=@i+1
end

select * from
(
 select ROW_NUMBER() OVER (order by s1 desc) as Pos from t1
) as T
where T.Pos >198000 and T.Pos<198050

超过20W也就1秒钟