/*
方法1
用时21秒
快速向表中插入100w行数据
*/
if object_id('nums') is not null
drop table nums
go
create table nums (n int primary key);
declare @max int ,@rc int
select @max=1000000;
select @rc=1;
insert into nums (n) values (1);
while @rc*2<=@max
begin
insert into nums
select n+@rc from nums
set @rc=@rc*2
end
insert into nums select n+@rc from nums where n+@rc<=@max
--select count(*) from nums
/*
方法2
用时4分4秒
快速向表中插入100w行数据
*/
IF OBJECT_ID('NUMS1') IS NOT NULL
DROP TABLE NUMS1
go
create table nums1 (n int primary key);
GO
declare @max int, @rc int
select @max=1000000;
select @rc=1;
while @rc<=@max
begin
insert into nums1 values(@rc);
set @rc=@rc+1
end
----------------------------
--------------------
--------------------
declare @n as bigint
set @n=1000000;
with nums as
(
select 1 as n
union all
select n+1 from nums where n<@n ----递归---
)
select n from nums
option (maxrecursion 0) ----移除默认的100级递归限制-----
-----------------------
-----------------------8秒
declare @n as bigint
set @n=1000000;
with nums as
(
select 1 as n
union all
select n+1 from nums where n<ceiling(sqrt(@n))
),
expand as
(select 1 as c from nums as b1,nums as b2)
,
base as
(
select row_number() over(order by c) as n
from expand
)
select n from base
option (maxrecursion 0)
----------------------------------
----------------------------------9秒
declare @n as bigint
set @n=1000000;
with
L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2行
L1 AS (SELECT 1 AS C FROM L0 AS A,L0 AS B), --4行
L2 AS (SELECT 1 AS C FROM L1 AS A,L1 AS B), --16行
L3 AS (SELECT 1 AS C FROM L2 AS A,L2 AS B), --256
L4 AS (SELECT 1 AS C FROM L3 AS A,L3 AS B), --65536
L5 AS (SELECT 1 AS C FROM L4 AS A,L4 AS B),
NUMS as (select row_number() over(order by c) as n from L5)
SELECT N FROM NUMS WHERE N<=@N