批量插入100万条数据 用于测试
/*创建表*/
create table tb(
km_h int,
zkzh int,
ss_h int,
zw_h int
)
//科目号|考生编号|室试号|座位号
//1 158994 590 94
drop table tb; //删除表数据和表结构
delete from tb; //删除表数据,保留表结构
select * from tb; //查询表数据
/*使用sql server插入100万数据*/
declare @zkzh int,
@ss_h int,
@zw_h int;
set @zkzh=100001;
while @zkzh<=1100000
begin
set @ss_h=1;
while @ss_h<=10000
begin
set @zw_h=1;
while @zw_h<=100
begin
insert into bigdata.dbo.tb(km_h,zkzh,ss_h,zw_h) values(1,@zkzh,@ss_h,@zw_h);
set @zw_h=@zw_h+1;
set @zkzh=@zkzh+1;
end;
set @ss_h=@ss_h+1;
end;
end;
下面是分页查询代码:
这里只记录了性能最好的一种:原文地址:https://www.cnblogs.com/xiaoMzjm/p/3933077.html
select * from (
select *,row_number()
over(order by zkzh desc)n from tb
)hhh
where hhh.n>50000 and hhh.n<=50010