create database DBTest use DBTest--创建测试表 create table pagetest (id int identity(1,1) not null, col01 int null, col02 nvarchar(50) null, col03 datetime null) --1000万记录集 delete from pagetest declare @i int set @i=0 while(@i<10000000) begin insert into pagetest select cast(floor(rand()*10000) as int),left(newid(),10),getdate() set @i=@i+1 end --数据库没插入那么多只插入了4005805 条 测试结果
--设定开始和结束的时间,页大小 和 页 declare @begin_date datetime,@end_date datetime declare @pagesize int ,@curPage int declare @strSql varchar(800) set @pagesize=50 set @curPage=78000 --第万页的数据 select @begin_date = getdate() --用top not in --set @strSql='select top '+ cast(@pagesize as varchar(50))+' * from pagetest where --id not in (select top '+Cast(@pagesize*(@curPage-1) as varchar(50)) +' id from pagetest)' --exec(@strSql)
![](https://i-blog.csdnimg.cn/blog_migrate/30737e2f07b400ce85b3f9314f2bdd52.png)
--用not exists --select top 50 * from pagetest where --not exists (select 1 from (select top (50*78000) id from pagetest) as a where a.id =pagetest.id ) --order by id
![](https://i-blog.csdnimg.cn/blog_migrate/cdf3e434f8ed2f5fec974dff32e3e444.png)
--用max、top --select top 50 * from pagetest --where id >(select max(id) from (select top (50*78000) id from pagetest) as a) --order by id
![](https://i-blog.csdnimg.cn/blog_migrate/7b3ea3505976a0e105708758f3d54434.png)
-- 用row_number --select top 50 * from --(select row_number()over(order by id) rid,* from pagetest) as tbTemp --where rid>(50*78000)
![](https://i-blog.csdnimg.cn/blog_migrate/167205baf02d0385904751d6357e362d.png)
--select * from (select row_number()over(order by id) rid,* from pagetest) as tbTemp --where rid>50*78000 and rid<=(50*(78000+1))
![](https://i-blog.csdnimg.cn/blog_migrate/cf7585fca03f5319ca76e2a9b0f33765.png)
--select * from (select row_number()over(order by id) rid,* from pagetest) as tbTemp --where rid between 50*78000 and 50*(78000+1)
![](https://i-blog.csdnimg.cn/blog_migrate/86c64312fced6be7da88ee77fd40244b.png)
--csdn 的另外的一种方法 select * from ( select row_number()over(order by rowcol) rid,* from (select top (50*(78000+1)) rowcol=0,* from dbo.pagetest order by id ) as a ) as b where b.rid >50*78000
![](https://i-blog.csdnimg.cn/blog_migrate/be1d213da4f9be7f8fbff60854f58fe4.png)
select @end_date = getdate() select datediff(ms,@begin_date,@end_date) as '毫秒' 在同样400万条数据中,用max .top 这种方法是最快的