较好的top分页方法

表bigtable

主键id,

列code (加非clustered索引),name

建表脚本

/****** 对象:  Table [dbo].[bigtable]    脚本日期: 04/17/2008 22:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bigtable](
 [id] [int] IDENTITY(1,1) NOT NULL,
 [code] [nchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [name] [nchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_bigtable1] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--数据脚本

 

declare @maxrowlen int
set @maxrowlen=10000000

set nocount on
while(@maxrowlen>0)
begin
insert into bigtable(code,[name])
values(cast(rand()*1000 as varchar(10)),
cast(rand()*1000 as varchar(10)))
set @maxrowlen=@maxrowlen-1
end

--产生的mdf文件有1.7g

 

--从此表中选择第31-40条纪录

--方法一

select top 10 id from
bigtable
where id>(select max(id)
from (select top 30 id from bigtable order by id )b
)
order by id 

--方法二，只sqlserver2005以上支持

--从一千万行的表中,找出第四百万－四百万零10条纪录

--只要2s.在我的笔记本上

with rows
as
(
select row_number() over(order by id) as rownum
from bigtable)
select * from rows where rownum between 4000000 and 4000010

 

 

--速度比较

declare @start datetime
declare @end datetime
declare @message varchar(50)
set @start=getdate()
;
with rows
as
(
select row_number() over(order by id) as rownum
from bigtable)
select * from rows where rownum between 4000000 and 4000010
;

set @end=getdate()

select  datediff(millisecond,@start,@end)

set @start=getdate()
select top 10 id from
bigtable
where id>(select max(id)
from (select top 4000000 id from bigtable order by id )b
)
order by id 
set @end=getdate()
select  datediff(millisecond,@start,@end)

 

--row_numer()  花2016 ms

--top max 方法　花　2160 ms

--row_number()胜出