declare @t table ( A int )insert into @A select 1 union all select 2 .......
declare @i int set @i=0; if @i<100000 begin insert table num values(@i) @i=@i+1 end
如果是插入相同的数据insert tb select 1 go 100000
用 SqlBulkCopy 批量插入删除类啊 ，这样可以
凑个数create table t (num int)insert t select CAST(RAND()*100000 as int) go 100000
create table tb(num int)set nocount on insert into tb select number from master..spt_values go 43select count(*) from tb /* 100878 */
create table tb(num int) insert into tb values(1) go 100000
create table tb(num int) go with t as (select top 1415 id from syscolumns) insert into tb select top 2000000 ROW_NUMBER() over(order by a.id) from t a ,t b 8秒的
dbcc dropcleanbuffers dbcc freeproccache select top 100000 identity(int,0,1) as rid into #t from master.sys.all_columns c1 cross join master.sys.all_columns c2 select count(*) from #t /* 100000 */ drop table #t --貌似1秒内,但是不太符合题意，题目要求是先创建表，我的是直接into表。
--7s if (not object_id('tempdb..#temp') is null) drop table #temp select * into #temp from (select 1 as id union all select 2) c declare @i int set @i = 1 while(@i < 21) begin insert into #temp select id + POWER(2,@i) from #temp set @i = @i + 1 end
-- 1秒的 -- 2000 的 select top 1500 identity(int,0,1) as rid into #t from master.sys.all_columns c1 select top 2000000 identity(int,0,1) as rid into #t2 from #t a,#t b--select * from #t2drop table #t drop table #t2-- 2005 的 ;with t as (select top 1500 row_number() over(order by getdate()) as rid from master.sys.all_columns c1 ) select top 2000000 identity(int,0,1) as rid into #t2 from t a,t b select * from #t2drop table #t2
/*************************************接上边*********************** Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions -------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------- ----------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------- ------------------ ---------------------------------------------------------------- -------- ------------------ 101000 1 with cte as ( select top 10 number from master..spt_values where type = 'p' )insert into tb select a.num from tb a cross join cte--select count(*) from tb 3 1 0 NULL NULL NULL NULL 101000 NULL NULL NULL 1.34995 NULL NULL INSERT 0 NULL 101000 1 |--Table Insert(OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [model].[dbo].[tb].[num] as [a].[num])) 3 2 1 Table Insert Insert OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [model].[dbo].[tb].[num] as [a].[num]) NULL 101000 0.7529859 0.101 9 1.34995 NULL NULL PLAN_ROW 0 1 101000 1 |--Top(ROWCOUNT est 0) 3 3 2 Top Top TOP EXPRESSION:((0)) NULL 101000 0 0.0101 11 0.4959646 [a].[num] NULL PLAN_ROW 0 1 101000 1 |--Nested Loops(Inner Join) 3 4 3 Nested Loops Inner Join NULL NULL 101000 0 0.42218 11 0.4858645 [a].[num] NO JOIN PREDICATE PLAN_ROW 0 1 10 1 |--Top(TOP EXPRESSION:((10))) 3 5 4 Top Top TOP EXPRESSION:((10)) NULL 10 0 1E-06 9 0.003317612 NULL NULL PLAN_ROW 0 1 10 1 | |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]), WHERE:([master].[dbo].[spt_values].[type]=N'p')) 3 6 5 Index Scan Index Scan OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]), WHERE:([master].[dbo].[spt_values].[type]=N'p') [master].[dbo].[spt_values].[type] 10 0.006828704 0.0027376 13 0.003311113 [master].[dbo].[spt_values].[type] NULL PLAN_ROW 0 1 101000 10 |--Table Spool 3 8 4 Table Spool Eager Spool NULL NULL 10100 0.013125 0.0037361 11 0.06036695 [a].[num] NULL PLAN_ROW 0 10 10100 1 |--Table Scan(OBJECT:([model].[dbo].[tb] AS [a])) 3 9 8 Table Scan Table Scan OBJECT:([model].[dbo].[tb] AS [a]) [a].[num] 10100 0.01497685 0.011267 11 0.02624385 [a].[num] NULL PLAN_ROW 0 1(8 行受影响) SQL Server 执行时间: CPU 时间 = 312 毫秒，占用时间 = 305 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。 SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
set statistics profile on set statistics io on set statistics time on gocreate table tb(num int) insert into tb select 100001 gogoset statistics profile off set statistics io off set statistics time off/*************************************接上边***********************SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 表 'tb'。扫描计数 0，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(1 行受影响) Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions -------------------- -------------------- -------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------- ------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------ 1 1 INSERT INTO [tb] SELECT @1 1 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL 1 1 |--Table Insert(OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [@1])) 1 2 1 Table Insert Insert OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [@1]) NULL 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1(2 行受影响) SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
declare @n bigint set @n=1000000 ;with cte1 as (select 1 as c union all select 1) ,cte2 as (select 1 as c from cte1 a,cte1 b) ,cte3 as (select 1 as c from cte2 a,cte2 b) ,cte4 as (select 1 as c from cte3 a,cte3 b) ,cte5 as (select 1 as c from cte4 a,cte4 b) ,cte6 as (select 1 as c from cte5 a,cte5 b) ,nums as (select row_number() over(order by c) as n from cte6)select n into tb from nums where n<=@n
set statistics profile on set statistics io on set statistics time on gocreate table tb(num int) godeclare @n bigint set @n=1000000 ;with cte1 as (select 1 as c union all select 1) ,cte2 as (select 1 as c from cte1 a,cte1 b) ,cte3 as (select 1 as c from cte2 a,cte2 b) ,cte4 as (select 1 as c from cte3 a,cte3 b) ,cte5 as (select 1 as c from cte4 a,cte4 b) ,cte6 as (select 1 as c from cte5 a,cte5 b) ,nums as (select row_number() over(order by c) as n from cte6)insert into tb select n from nums where n<=@ndrop table tb goset statistics profile off set statistics io off set statistics time off/*************************************接上边***********************SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。 SQL Server 分析和编译时间: CPU 时间 = 15 毫秒，占用时间 = 23 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 表 'tb'。扫描计数 0，逻辑读取 1001607 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。中间很多，省略…………………………SQL Server 执行时间: CPU 时间 = 2672 毫秒，占用时间 = 2827 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 26 毫秒。 SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
(
A int
)insert into @A
select 1 union all
select 2
.......
declare @i int
set @i=0;
if @i<100000
begin
insert table num values(@i)
@i=@i+1
end
go 100000
go 100000
create table tb(num int)set nocount on
insert into tb select number from master..spt_values
go 43select count(*) from tb
/*
100878
*/
create table tb(num int)
insert into tb values(1)
go 100000
go
with t as
(select top 1415 id from syscolumns)
insert into tb
select top 2000000 ROW_NUMBER() over(order by a.id) from t a ,t b
8秒的
dbcc dropcleanbuffers
dbcc freeproccache
select top 100000 identity(int,0,1) as rid into #t
from master.sys.all_columns c1
cross join master.sys.all_columns c2
select count(*) from #t
/*
100000
*/
drop table #t
--貌似1秒内,但是不太符合题意，题目要求是先创建表，我的是直接into表。
--7s
if (not object_id('tempdb..#temp') is null)
drop table #temp
select * into #temp from (select 1 as id union all select 2) c
declare @i int
set @i = 1 while(@i < 21)
begin
insert into #temp select id + POWER(2,@i) from #temp
set @i = @i + 1
end
-- 2000 的
select top 1500 identity(int,0,1) as rid into #t
from master.sys.all_columns c1
select top 2000000 identity(int,0,1) as rid into #t2 from #t a,#t b--select * from #t2drop table #t
drop table #t2-- 2005 的
;with t as
(select top 1500 row_number() over(order by getdate())
as rid
from master.sys.all_columns c1
)
select top 2000000 identity(int,0,1) as rid into #t2 from t a,t b
select * from #t2drop table #t2
create table tb(num int)
insert into tb select 100001
go
100001 是 10w 以上的数据.结帖给分!
又追加了一条新记录，目前豆子最大了，哈哈
set statistics profile on
set statistics io on
set statistics time on
goselect top 1500 identity(int,0,1) as rid into #t
from master.sys.all_columns c1 select top 2000000 identity(int,0,1) as rid into #t2 from #t a,#t b--select * from #t2drop table #t
drop table #t2
goset statistics profile off
set statistics io off
set statistics time off
/*************************************接上边***********************
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 4 毫秒。
表 'syscolrdb'。扫描计数 1，逻辑读取 11 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'syscolpars'。扫描计数 1，逻辑读取 7 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(1500 行受影响)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------------------------- --------------------------------------- ------------- ------------- ------------- ----------- ---------------- --------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
1500 1 select top 1500 identity(int,0,1) as rid into #t
from master.sys.all_columns c1 1 1 0 NULL NULL NULL NULL 1500 NULL NULL NULL 0.05732555 NULL NULL SELECT INTO 0 NULL
1500 1 |--Table Insert(OBJECT:([#t]), SET:([#t].[rid] = [Expr1061])) 1 2 1 Table Insert Insert OBJECT:([#t]), SET:([#t].[rid] = [Expr1061]) NULL 1500 0.01437804 0.0015 9 0.05732555 NULL NULL PLAN_ROW 0 1
1500 1 |--Compute Scalar(DEFINE:([Expr1061]=getidentity((-7),(0),N'#t'))) 1 3 2 Compute Scalar Compute Scalar DEFINE:([Expr1061]=getidentity((-7),(0),N'#t')) [Expr1061]=getidentity((-7),(0),N'#t') 1500 0 0.00015 11 0.04144751 [Expr1061] NULL PLAN_ROW 0 1
1500 1 |--Top(ROWCOUNT est 0) 1 4 3 Top Top TOP EXPRESSION:((0)) NULL 1500 0 0.00015 9 0.04129751 NULL NULL PLAN_ROW 0 1
1500 1 |--Top(TOP EXPRESSION:((1500))) 1 5 4 Top Top TOP EXPRESSION:((1500)) NULL 1500 0 0.00015 9 0.04114751 NULL NULL PLAN_ROW 0 1
1500 1 |--Concatenation 1 6 5 Concatenation Concatenation NULL NULL 1500 0 0.0003811117 9 0.04099751 NULL NULL PLAN_ROW 0 1
560 1 |--Filter(WHERE:(has_access('CO',[master].[sys].[syscolpars].[id])=(1))) 1 7 6 Filter Filter WHERE:(has_access('CO',[master].[sys].[syscolpars].[id])=(1)) NULL 115.1174 0 0.0007168 9 0.007577763 NULL NULL PLAN_ROW 0 1
560 1 | |--Index Scan(OBJECT:([master].[sys].[syscolpars].[nc]), WHERE:([master].[sys].[syscolpars].[number]=(0))) 1 8 7 Index Scan Index Scan OBJECT:([master].[sys].[syscolpars].[nc]), WHERE:([master].[sys].[syscolpars].[number]=(0)) [master].[sys].[syscolpars].[id] 115.1174 0.006087963 0.000773 13 0.006860963 [master].[sys].[syscolpars].[id] NULL PLAN_ROW 0 1
940 1 |--Index Scan(OBJECT:([mssqlsystemresource].[sys].[syscolrdb].[ncl]), WHERE:([mssqlsystemresource].[sys].[syscolrdb].[number]=(0))) 1 10 6 Index Scan Index Scan OBJECT:([mssqlsystemresource].[sys].[syscolrdb].[ncl]), WHERE:([mssqlsystemresource].[sys].[syscolrdb].[number]=(0)) NULL 1384.883 0.07423611 0.0118148 9 0.03429112 NULL NULL PLAN_ROW 0 1(9 行受影响)
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 4 毫秒。
表 '#t__________________________________________________________________________________________________________________00000000000C'。扫描计数 3，逻辑读取 7 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(2000000 行受影响)
/*************************************接上边***********************Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ---------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------- ---------------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------- ------------------ ---------------------------------------------------------------- -------- ------------------
2000000 1 select top 2000000 identity(int,0,1) as rid into #t2 from #t a,#t b--select * from #t2 2 1 0 NULL NULL NULL NULL 2000000 NULL NULL NULL 23.25665 NULL NULL SELECT INTO 0 NULL
2000000 1 |--Table Insert(OBJECT:([#t2]), SET:([#t2].[rid] = [Expr1010])) 2 2 1 Table Insert Insert OBJECT:([#t2]), SET:([#t2].[rid] = [Expr1010]) NULL 2000000 14.84963 2 9 23.25665 NULL NULL PLAN_ROW 0 1
2000000 1 |--Compute Scalar(DEFINE:([Expr1010]=getidentity((-7),(0),N'#t2'))) 2 3 2 Compute Scalar Compute Scalar DEFINE:([Expr1010]=getidentity((-7),(0),N'#t2')) [Expr1010]=getidentity((-7),(0),N'#t2') 2000000 0 0.2 11 6.40702 [Expr1010] NULL PLAN_ROW 0 1
2000000 1 |--Top(ROWCOUNT est 0) 2 4 3 Top Top TOP EXPRESSION:((0)) NULL 2000000 0 0.2 9 6.20702 NULL NULL PLAN_ROW 0 1
2000000 1 |--Top(TOP EXPRESSION:((2000000))) 2 5 4 Top Top TOP EXPRESSION:((2000000)) NULL 2000000 0 0.2 9 6.00702 NULL NULL PLAN_ROW 0 1
2000000 1 |--Parallelism(Gather Streams) 2 6 5 Parallelism Gather Streams NULL NULL 2000000 0 1.281 9 5.80702 NULL NULL PLAN_ROW 1 1
2004457 2 |--Nested Loops(Inner Join) 2 7 6 Nested Loops Inner Join NULL NULL 2000000 0 4.7025 9 4.52602 NULL NO JOIN PREDICATE PLAN_ROW 1 1
1337 2 |--Table Scan(OBJECT:([tempdb].[dbo].[#t] AS [a])) 2 8 7 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#t] AS [a]) NULL 1333.333 0.005425722 0.00086425 9 0.005951227 NULL NULL PLAN_ROW 1 1
2004457 1337 |--Row Count Spool 2 9 7 Row Count Spool Lazy Spool NULL NULL 1500 0 0.00025 9 0.3405153 NULL NULL PLAN_ROW 1 1333.444
3000 2 |--Table Scan(OBJECT:([tempdb].[dbo].[#t] AS [b])) 2 10 9 Table Scan Table Scan OBJECT:([tempdb].[dbo].[#t] AS [b]) NULL 1500 0.005347222 0.001807 9 0.007154222 NULL NULL PLAN_ROW 1 1(10 行受影响)
SQL Server 执行时间:
CPU 时间 = 1594 毫秒，占用时间 = 1668 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 16 毫秒，占用时间 = 5 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
插入100002,比插入100001 多消耗一个CPU周期.
set statistics profile on
set statistics io on
set statistics time on
go
create table tb(num int)
goinsert into tb
select top 100 c1.number
from master..spt_values c1
cross join master..spt_values c2insert into tb
select a.num
from tb a cross join tb b;with cte as
(
select top 10 number
from master..spt_values
where type = 'p'
)insert into tb
select a.num
from tb a cross join cte--select count(*) from tb
drop table tb
go
set statistics profile off
set statistics io off
set statistics time off/*************************************接上边***********************SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
表 'tb'。扫描计数 0，逻辑读取 100 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'spt_values'。扫描计数 2，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(100 行受影响)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------------- -------------- ------------- ------------- ------------- ----------- ---------------- -------------- ------------------ ---------------------------------------------------------------- -------- ------------------
100 1 insert into tb
select top 100 c1.number
from master..spt_values c1
cross join master..spt_values c2 1 1 0 NULL NULL NULL NULL 100 NULL NULL NULL 0.01721864 NULL NULL INSERT 0 NULL
100 1 |--Table Insert(OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [master].[dbo].[spt_values].[number] as [c1].[number])) 1 2 1 Table Insert Insert OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [master].[dbo].[spt_values].[number] as [c1].[number]) NULL 100 0.01 0.0001 9 0.01721864 NULL NULL PLAN_ROW 0 1
100 1 |--Top(ROWCOUNT est 0) 1 3 2 Top Top TOP EXPRESSION:((0)) NULL 100 0 1E-05 11 0.007118644 [c1].[number] NULL PLAN_ROW 0 1
100 1 |--Top(TOP EXPRESSION:((100))) 1 4 3 Top Top TOP EXPRESSION:((100)) NULL 100 0 1E-05 11 0.007108644 [c1].[number] NULL PLAN_ROW 0 1
100 1 |--Nested Loops(Inner Join) 1 5 4 Nested Loops Inner Join NULL NULL 100 0 23.00553 11 0.007098644 [c1].[number] NO JOIN PREDICATE PLAN_ROW 0 1
1 1 |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc] AS [c1])) 1 6 5 Index Scan Index Scan OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc] AS [c1]) [c1].[number] 1 0.006828704 0.0027376 11 0.0032831 [c1].[number] NULL PLAN_ROW 0 1
100 1 |--Row Count Spool 1 7 5 Row Count Spool Lazy Spool NULL NULL 100 0 0.0003346 9 0.003658361 NULL NULL PLAN_ROW 0 1
100 1 |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc] AS [c2])) 1 8 7 Index Scan Index Scan OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc] AS [c2]) NULL 100 0.006828704 0.0027376 9 0.003548361 NULL NULL PLAN_ROW 0 1(8 行受影响)
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。
表 'tb'。扫描计数 2，逻辑读取 10018 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 1，逻辑读取 203 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(10000 行受影响)
/*************************************接上边***********************Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ----------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ---------------------------------------------------------------------------------------------------- ------------- ------------- ------------- ------------- ----------- ---------------- ---------- ------------------ ---------------------------------------------------------------- -------- ------------------
10000 1 insert into tb
select a.num
from tb a cross join tb b 2 1 0 NULL NULL NULL NULL 10000 NULL NULL NULL 0.1613203 NULL NULL INSERT 0 NULL
10000 1 |--Table Insert(OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [model].[dbo].[tb].[num] as [a].[num])) 2 2 1 Table Insert Insert OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [model].[dbo].[tb].[num] as [a].[num]) NULL 10000 0.07747519 0.01 9 0.1613203 NULL NULL PLAN_ROW 0 1
10000 1 |--Top(ROWCOUNT est 0) 2 3 2 Top Top TOP EXPRESSION:((0)) NULL 10000 0 0.001 11 0.0738451 [a].[num] NULL PLAN_ROW 0 1
10000 1 |--Nested Loops(Inner Join) 2 4 3 Nested Loops Inner Join NULL NULL 10000 0 0.0418 11 0.0728451 [a].[num] NO JOIN PREDICATE PLAN_ROW 0 1
100 1 |--Table Spool 2 5 4 Table Spool Eager Spool NULL NULL 100 0.013125 0.0001361 11 0.0166531 [a].[num] NULL PLAN_ROW 0 1
100 1 | |--Table Scan(OBJECT:([model].[dbo].[tb] AS [a])) 2 6 5 Table Scan Table Scan OBJECT:([model].[dbo].[tb] AS [a]) [a].[num] 100 0.003125 0.000267 11 0.003392 [a].[num] NULL PLAN_ROW 0 1
10000 100 |--Row Count Spool 2 7 4 Row Count Spool Eager Spool NULL NULL 100 0 0.00011 9 0.014392 NULL NULL PLAN_ROW 0 100
100 1 |--Table Scan(OBJECT:([model].[dbo].[tb] AS [b])) 2 8 7 Table Scan Table Scan OBJECT:([model].[dbo].[tb] AS [b]) NULL 100 0.003125 0.000267 9 0.003392 NULL NULL PLAN_ROW 0 1(8 行受影响)
SQL Server 执行时间:
CPU 时间 = 31 毫秒，占用时间 = 35 毫秒。
表 'tb'。扫描计数 1，逻辑读取 101179 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 1，逻辑读取 20662 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'spt_values'。扫描计数 1，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(101000 行受影响)
/*************************************接上边***********************
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------- ----------- ----------- ------------------------------ ------------------------------ -------------------------------------------------------------------------------------------------------------- ----------------------------------- ------------- ------------- ------------- ----------- ---------------- ----------------------------------- ------------------ ---------------------------------------------------------------- -------- ------------------
101000 1 with cte as
(
select top 10 number
from master..spt_values
where type = 'p'
)insert into tb
select a.num
from tb a cross join cte--select count(*) from tb 3 1 0 NULL NULL NULL NULL 101000 NULL NULL NULL 1.34995 NULL NULL INSERT 0 NULL
101000 1 |--Table Insert(OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [model].[dbo].[tb].[num] as [a].[num])) 3 2 1 Table Insert Insert OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [model].[dbo].[tb].[num] as [a].[num]) NULL 101000 0.7529859 0.101 9 1.34995 NULL NULL PLAN_ROW 0 1
101000 1 |--Top(ROWCOUNT est 0) 3 3 2 Top Top TOP EXPRESSION:((0)) NULL 101000 0 0.0101 11 0.4959646 [a].[num] NULL PLAN_ROW 0 1
101000 1 |--Nested Loops(Inner Join) 3 4 3 Nested Loops Inner Join NULL NULL 101000 0 0.42218 11 0.4858645 [a].[num] NO JOIN PREDICATE PLAN_ROW 0 1
10 1 |--Top(TOP EXPRESSION:((10))) 3 5 4 Top Top TOP EXPRESSION:((10)) NULL 10 0 1E-06 9 0.003317612 NULL NULL PLAN_ROW 0 1
10 1 | |--Index Scan(OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]), WHERE:([master].[dbo].[spt_values].[type]=N'p')) 3 6 5 Index Scan Index Scan OBJECT:([master].[dbo].[spt_values].[ix2_spt_values_nu_nc]), WHERE:([master].[dbo].[spt_values].[type]=N'p') [master].[dbo].[spt_values].[type] 10 0.006828704 0.0027376 13 0.003311113 [master].[dbo].[spt_values].[type] NULL PLAN_ROW 0 1
101000 10 |--Table Spool 3 8 4 Table Spool Eager Spool NULL NULL 10100 0.013125 0.0037361 11 0.06036695 [a].[num] NULL PLAN_ROW 0 10
10100 1 |--Table Scan(OBJECT:([model].[dbo].[tb] AS [a])) 3 9 8 Table Scan Table Scan OBJECT:([model].[dbo].[tb] AS [a]) [a].[num] 10100 0.01497685 0.011267 11 0.02624385 [a].[num] NULL PLAN_ROW 0 1(8 行受影响)
SQL Server 执行时间:
CPU 时间 = 312 毫秒，占用时间 = 305 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 2 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
set statistics profile on
set statistics io on
set statistics time on
gocreate table tb(num int)
insert into tb select 100001
gogoset statistics profile off
set statistics io off
set statistics time off/*************************************接上边***********************SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
表 'tb'。扫描计数 0，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。(1 行受影响)
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
-------------------- -------------------- -------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------- ------------- ------------- ------------- ------------- ----------- ---------------- ---------- -------- ---------------------------------------------------------------- -------- ------------------
1 1 INSERT INTO [tb] SELECT @1 1 1 0 NULL NULL NULL NULL 1 NULL NULL NULL 0.01000216 NULL NULL INSERT 0 NULL
1 1 |--Table Insert(OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [@1])) 1 2 1 Table Insert Insert OBJECT:([model].[dbo].[tb]), SET:([model].[dbo].[tb].[num] = [@1]) NULL 1 0.01 1E-06 9 0.01000216 NULL NULL PLAN_ROW 0 1(2 行受影响)
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
set @n=1000000
;with cte1 as
(select 1 as c union all select 1)
,cte2 as
(select 1 as c from cte1 a,cte1 b)
,cte3 as
(select 1 as c from cte2 a,cte2 b)
,cte4 as
(select 1 as c from cte3 a,cte3 b)
,cte5 as
(select 1 as c from cte4 a,cte4 b)
,cte6 as
(select 1 as c from cte5 a,cte5 b)
,nums as (select row_number() over(order by c) as n from cte6)select n into tb from nums where n<=@n
set statistics profile on
set statistics io on
set statistics time on
gocreate table tb(num int)
godeclare @n bigint
set @n=1000000
;with cte1 as
(select 1 as c union all select 1)
,cte2 as
(select 1 as c from cte1 a,cte1 b)
,cte3 as
(select 1 as c from cte2 a,cte2 b)
,cte4 as
(select 1 as c from cte3 a,cte3 b)
,cte5 as
(select 1 as c from cte4 a,cte4 b)
,cte6 as
(select 1 as c from cte5 a,cte5 b)
,nums as (select row_number() over(order by c) as n from cte6)insert into tb select n from nums where n<=@ndrop table tb
goset statistics profile off
set statistics io off
set statistics time off/*************************************接上边***********************SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 15 毫秒，占用时间 = 23 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
表 'tb'。扫描计数 0，逻辑读取 1001607 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。中间很多，省略…………………………SQL Server 执行时间:
CPU 时间 = 2672 毫秒，占用时间 = 2827 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 26 毫秒。
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。