|
表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,@end)
?
--row_numer()? 花2016 ms
--top max 方法 花 2160 ms
--row_number()胜出
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
