再说分页

数据库中有这样一张表，包含1000000条数据，数据字段如下：Gid,title,fariqi,neibuyonghu,reader

现对其进行分页。

--create clustered index myIndex on dbo.TGongwen(fariqi,gid);
--drop index dbo.TGongwen.myIndex;

declare @pagesize as int,@pagenum as int;
set @pagesize=10;
set @pagenum=10;
with myCTE as
(
select
row_number() over(order by fariqi,gid) as rownumber,
Gid,title,fariqi,neibuyonghu,reader from dbo.TGongwen
)
select
Gid,title,fariqi,neibuyonghu,reader from myCTE
where rownumber>=(@pagenum-1)*@pagesize+1
and rownumber<=@pagesize*@pagenum
order by rownumber
;

此方法在不创建聚集索引时耗时3毫秒(计算机配置较高)，当创建索引时，数据即刻取出。