数据库中有这样一张表,包含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毫秒(计算机配置较高),当创建索引时,数据即刻取出。