快速插入100W唯一数字的几种方案
--1.while循环
declare @i as int
set @i = 0
while(@i < 1000000)
begin
insert into numbers values(@i)
set @i =@i+ 1
end
--极度慢
--2.6表卡迪尔
DECLARE @t TABLE
(
NUMBER INT
)
INSERT INTO @t
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
INSERT INTO numbers
SELECT t1.NUMBER + t2.NUMBER * 10 + t3.NUMBER * 100 + t4.NUMBER * 1000 + t5.NUMBER * 10000 + t6.NUMBER * 100000
FROM @t AS t1,
@t AS t2,
@t AS t3,
@t AS t4,
@t AS t5,
@t AS t6
--测试脚本
--------------------------------------------------------------------------
DECLARE @beginTime DATETIME
SET @beginTime=Getdate()
---------------------------------
SET STATISTICS IO ON
DECLARE @t TABLE (
NUMBER INT )
INSERT INTO @t
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
INSERT INTO numbers
SELECT t1.NUMBER + t2.NUMBER * 10 + t3.NUMBER * 100 + t4.NUMBER * 1000 + t5.NUMBER * 10000 + t6.NUMBER * 100000
FROM @t AS t1,
@t AS t2,
@t AS t3,
@t AS t4,
@t AS t5,
@t AS t6
--------------------------------
SELECT Datediff(ms, @beginTime, Getdate()) AS [Time(ms)]
SET STATISTICS IO off
TRUNCATE TABLE numbers
---强行释放内存，清除缓冲区数据
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
exec sp_configure 'max server memory', 128
EXEC ('RECONFIGURE' )
WAITFOR DELAY '00:00:03'
EXEC sp_configure 'max server memory', 2147483647
EXEC ('RECONFIGURE' )
GO
---------------------------------------------------------------------------
--8343毫秒
/*
表 '#0CBAE877'。扫描计数 0，逻辑读取 10 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(10 行受影响)
表 'numbers'。扫描计数 0，逻辑读取 1003344 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#0CBAE877'。扫描计数 6，逻辑读取 111111 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1000000 行受影响)
*/
--3.3表卡迪尔
DECLARE @t TABLE ( number INT )
INSERT INTO @t
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
UNION ALL
SELECT 11
UNION ALL
SELECT 12
UNION ALL
SELECT 13
UNION ALL
SELECT 14
UNION ALL
SELECT 15
UNION ALL
SELECT 16
UNION ALL
SELECT 17
UNION ALL
SELECT 18
UNION ALL
SELECT 19
UNION ALL
SELECT 20
UNION ALL
SELECT 21
UNION ALL
SELECT 22
UNION ALL
SELECT 23
UNION ALL
SELECT 24
UNION ALL
SELECT 25
UNION ALL
SELECT 26
UNION ALL
SELECT 27
UNION ALL
SELECT 28
UNION ALL
SELECT 29
UNION ALL
SELECT 30
UNION ALL
SELECT 31
UNION ALL
SELECT 32
UNION ALL
SELECT 33
UNION ALL
SELECT 34
UNION ALL
SELECT 35
UNION ALL
SELECT 36
UNION ALL
SELECT 37
UNION ALL
SELECT 38
UNION ALL
SELECT 39
UNION ALL
SELECT 40
UNION ALL
SELECT 41
UNION ALL
SELECT 42
UNION ALL
SELECT 43
UNION ALL
SELECT 44
UNION ALL
SELECT 45
UNION ALL
SELECT 46
UNION ALL
SELECT 47
UNION ALL
SELECT 48
UNION ALL
SELECT 49
UNION ALL
SELECT 50
UNION ALL
SELECT 51
UNION ALL
SELECT 52
UNION ALL
SELECT 53
UNION ALL
SELECT 54
UNION ALL
SELECT 55
UNION ALL
SELECT 56
UNION ALL
SELECT 57
UNION ALL
SELECT 58
UNION ALL
SELECT 59
UNION ALL
SELECT 60
UNION ALL
SELECT 61
UNION ALL
SELECT 62
UNION ALL
SELECT 63
UNION ALL
SELECT 64
UNION ALL
SELECT 65
UNION ALL
SELECT 66
UNION ALL
SELECT 67
UNION ALL
SELECT 68
UNION ALL
SELECT 69
UNION ALL
SELECT 70
UNION ALL
SELECT 71
UNION ALL
SELECT 72
UNION ALL
SELECT 73
UNION ALL
SELECT 74
UNION ALL
SELECT 75
UNION ALL
SELECT 76
UNION ALL
SELECT 77
UNION ALL
SELECT 78
UNION ALL
SELECT 79
UNION ALL
SELECT 80
UNION ALL
SELECT 81
UNION ALL
SELECT 82
UNION ALL
SELECT 83
UNION ALL
SELECT 84
UNION ALL
SELECT 85
UNION ALL
SELECT 86
UNION ALL
SELECT 87
UNION ALL
SELECT 88
UNION ALL
SELECT 89
UNION ALL
SELECT 90
UNION ALL
SELECT 91
UNION ALL
SELECT 92
UNION ALL
SELECT 93
UNION ALL
SELECT 94
UNION ALL
SELECT 95
UNION ALL
SELECT 96
UNION ALL
SELECT 97
UNION ALL
SELECT 98
UNION ALL
SELECT 99
INSERT INTO numbers
SELECT t1.number *10 + t2.number * 1000 + t3.number *10000
* 1000
FROM @t AS t1 ,
@t AS t2 ,
@t AS t3
--测试脚本
-----------------------------------------------------------------------
DECLARE @beginTime DATETIME
SET @beginTime=Getdate()
---------------------------------
SET STATISTICS IO ON
DECLARE @t TABLE ( number INT )
INSERT INTO @t
SELECT 0
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
UNION ALL
SELECT 11
UNION ALL
SELECT 12
UNION ALL
SELECT 13
UNION ALL
SELECT 14
UNION ALL
SELECT 15
UNION ALL
SELECT 16
UNION ALL
SELECT 17
UNION ALL
SELECT 18
UNION ALL
SELECT 19
UNION ALL
SELECT 20
UNION ALL
SELECT 21
UNION ALL
SELECT 22
UNION ALL
SELECT 23
UNION ALL
SELECT 24
UNION ALL
SELECT 25
UNION ALL
SELECT 26
UNION ALL
SELECT 27
UNION ALL
SELECT 28
UNION ALL
SELECT 29
UNION ALL
SELECT 30
UNION ALL
SELECT 31
UNION ALL
SELECT 32
UNION ALL
SELECT 33
UNION ALL
SELECT 34
UNION ALL
SELECT 35
UNION ALL
SELECT 36
UNION ALL
SELECT 37
UNION ALL
SELECT 38
UNION ALL
SELECT 39
UNION ALL
SELECT 40
UNION ALL
SELECT 41
UNION ALL
SELECT 42
UNION ALL
SELECT 43
UNION ALL
SELECT 44
UNION ALL
SELECT 45
UNION ALL
SELECT 46
UNION ALL
SELECT 47
UNION ALL
SELECT 48
UNION ALL
SELECT 49
UNION ALL
SELECT 50
UNION ALL
SELECT 51
UNION ALL
SELECT 52
UNION ALL
SELECT 53
UNION ALL
SELECT 54
UNION ALL
SELECT 55
UNION ALL
SELECT 56
UNION ALL
SELECT 57
UNION ALL
SELECT 58
UNION ALL
SELECT 59
UNION ALL
SELECT 60
UNION ALL
SELECT 61
UNION ALL
SELECT 62
UNION ALL
SELECT 63
UNION ALL
SELECT 64
UNION ALL
SELECT 65
UNION ALL
SELECT 66
UNION ALL
SELECT 67
UNION ALL
SELECT 68
UNION ALL
SELECT 69
UNION ALL
SELECT 70
UNION ALL
SELECT 71
UNION ALL
SELECT 72
UNION ALL
SELECT 73
UNION ALL
SELECT 74
UNION ALL
SELECT 75
UNION ALL
SELECT 76
UNION ALL
SELECT 77
UNION ALL
SELECT 78
UNION ALL
SELECT 79
UNION ALL
SELECT 80
UNION ALL
SELECT 81
UNION ALL
SELECT 82
UNION ALL
SELECT 83
UNION ALL
SELECT 84
UNION ALL
SELECT 85
UNION ALL
SELECT 86
UNION ALL
SELECT 87
UNION ALL
SELECT 88
UNION ALL
SELECT 89
UNION ALL
SELECT 90
UNION ALL
SELECT 91
UNION ALL
SELECT 92
UNION ALL
SELECT 93
UNION ALL
SELECT 94
UNION ALL
SELECT 95
UNION ALL
SELECT 96
UNION ALL
SELECT 97
UNION ALL
SELECT 98
UNION ALL
SELECT 99
INSERT INTO numbers
SELECT t1.number *10 + t2.number * 1000 + t3.number *10000
* 1000
FROM @t AS t1 ,
@t AS t2 ,
@t AS t3
--------------------------------
SELECT Datediff(ms, @beginTime, Getdate()) AS [Time(ms)]
SET STATISTICS IO off
TRUNCATE TABLE numbers
---强行释放内存，清除缓冲区数据
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
exec sp_configure 'max server memory', 128
EXEC ('RECONFIGURE' )
WAITFOR DELAY '00:00:03'
EXEC sp_configure 'max server memory', 2147483647
EXEC ('RECONFIGURE' )
GO
--8123毫秒，稍微比2方案快，以为第三个表的逻辑扫描减少
/*
表 '#1367E606'。扫描计数 0，逻辑读取 100 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(100 行受影响)
表 'numbers'。扫描计数 0，逻辑读取 1003344 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 '#1367E606'。扫描计数 3，逻辑读取 10101 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
(1000000 行受影响)
*/
--4.Identity
declare @beginTime datetime
set @beginTime=getdate()
SELECT TOP 1000000 IDENTITY(INT,0,1) AS idx
INTO dbo.Numbers2
FROM master.sys.all_columns c1
CROSS JOIN master.sys.all_columns c2
select datediff(ms,@beginTime,getdate()) as [Time(ms)]
-----------------------------------------------------
SET STATISTICS IO off
---强行释放内存，清除缓冲区数据
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
exec sp_configure 'max server memory', 128
EXEC ('RECONFIGURE' )
WAITFOR DELAY '00:00:03'
EXEC sp_configure 'max server memory', 2147483647
EXEC ('RECONFIGURE' )
GO
--2876毫秒，无IO逻辑读取
--5.row_number
declare @beginTime datetime
set @beginTime=getdate()
SELECT TOP 1000000
ROW_NUMBER() over (order by c1.object_id) AS N
INTO dbo.Numbers3
FROM master.sys.all_columns c1
CROSS JOIN master.sys.all_columns c2
select datediff(ms,@beginTime,getdate()) as [Time(ms)]
-----------------------------------------------------
SET STATISTICS IO off
---强行释放内存，清除缓冲区数据
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC FREESYSTEMCACHE('All')
DBCC DROPCLEANBUFFERS
exec sp_configure 'max server memory', 128
EXEC ('RECONFIGURE' )
WAITFOR DELAY '00:00:03'
EXEC sp_configure 'max server memory', 2147483647
EXEC ('RECONFIGURE' )
GO
--1270毫秒，无IO逻辑读取
