27,579
社区成员
发帖
与我相关
我的任务
分享
快速生成一个数据表,只有一列,这一列的值从1到8000,
sql只需要执行14次
(第一条sql插入数据1,后面12次把2的12次方4096以内的数据插入,后面剩下的8000-4096个数字一次生成)
而不是8000次:
语句如下:
----------------------------------------------------------------------------------------------
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECTID(“dbo.Nums“) IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 8000;
SET @rc = 1;
INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO
------------------
--载自http://www.cnblogs.com/zuoqs/archive/2010/01/24/1655451.html,作者:左其盛
本屌丝刚刚做了个实验吓尿了,因此,不明白为什么@左其盛老师说他的比@周强老师快:结果如下:
1.循环,楼上(benbenLY)屌丝的代码:(做了点修改)
DECLARE @i INT
set @i=1
WHILE @i<8000
BEGIN
INSERT INTO Nums SELECT @i
SET @i=@i+1
END
执行结果:
--------------------------------------------------------------------
2.@左老师代码
(见楼上)
执行结果:
--------------------------------------------------------------------
3.@周强老师代码
(见楼上)
执行结果:
-----------------------------------------------------------------------
这其中的差距还是蛮大的,我看到的结果是:@周强老师的速度是最快的,循环当然必须是最慢的,可是,在博客中看到@左老师与@周老师的互动,@左老师说通过验证,他的是最快的。有点疑惑!!!
DECLARE @i INT =1
WHILE @i<8000
BEGIN
INSERT INTO Nums SELECT @i
SET @i=@i+1
END
;with t1 as
(
select 1 as col
union all
select 1
)
,t2 as
(
select i.col
from t1 as i
cross join t1 as p
cross join t1 as q
)
,t3 as
(
select i.col
from t2 as i
cross join t2 as p
)
,t4 as
(
select i.col
from t3 as i
cross join t3 as p
cross join t3 as q
)
select top (8000) row_number() over(order by t4.col)
from t4
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
insert into Nums
select top 8000 row_number() over (order by (select 1)) from sysobjects,syscolumns