add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table dbo.Test1 (
Id int identity primary key,
ThingA int not null,
ThingB int not null
);

create table dbo.Test2 (
Id int identity primary key,
Test1Id int not null foreign key references Test1 (Id),
Val int not null
);
create trigger test2ThingBCheck_Update
on dbo.Test2
instead of Update
as
begin
update
t2
set
Val = i.Val
from
dbo.Test2 t2
join
inserted i
on i.Id = t2.Id
join
dbo.Test1 t1
on t1.Id = i.Test1Id
where
t1.ThingB = 3;
end;
create trigger test2ThingBCheck_Insert
on dbo.Test2
instead of INSERT
as
begin

insert into dbo.Test2 (Test1Id, Val)
select
t.Id
,i.Val
from
Test1 t
join
inserted i
on i.Test1Id = t.Id
where
t.ThingB = 3;
end;
--------------------------------------------------------------------------------
-- ① Execution Plans
--------------------------------------------------------------------------------
set statistics profile on;
select max('Do this to get execution plans and stats') "Execution Plans"
from docs d1 cross join docs d2;
set statistics profile off;
Msg 208 Level 16 State 1 Line 5
Invalid object name 'docs'.
--------------------------------------------------------------------------------
-- Or use XML to see the visual representation, thanks to Justin Pealing and
-- his library: https://github.com/JustinPealing/html-query-plan
--------------------------------------------------------------------------------
set statistics xml on;
select max('Do this to get execution plans and stats') "Execution Plans"
from docs d1 cross join docs d2;
set statistics xml off;
Msg 208 Level 16 State 1 Line 6
Invalid object name 'docs'.
--------------------------------------------------------------------------------
-- ② Integers table
--------------------------------------------------------------------------------
with
p0(i) as (select 1 union all select 1 union all select 1 union all select 1)
, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows
, p2(i) as (select 1 from p1 as a, p1 as b)--1M rows
select row_number() over(order by i) as val
into integers
from p2
Rows Executes StmtText StmtId NodeId Parent PhysicalOp LogicalOp Argument DefinedValues EstimateRows EstimateIO EstimateCPU AvgRowSize TotalSubtreeCost OutputList Warnings Type Parallel EstimateExecutions
1048576 1 with
  p0(i) as (select 1 union all select 1 union all select 1 union all select 1)
, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows
, p2(i) as (select 1 from p1 as a, p1 as b)--1M rows
select row_number() over(order by i) as val
into integers
from p2		 1 1 0 null null null null 1048576 null null null 21.37179 null null SELECT INTO False null
1048576 1   |--Table Insert(OBJECT:([integers]), SET:([integers].[val] = [Expr1056])) 1 2 1 Table Insert Insert OBJECT:([integers]), SET:([integers].[val] = [Expr1056]) null 1048576 12.97622 1.048576 9 21.37179 null null PLAN_ROW False 1
1048576 1        |--Sequence Project(DEFINE:([Expr1056]=row_number)) 1 3 2 Sequence Project Compute Scalar DEFINE:([Expr1056]=row_number) [Expr1056]=row_number 1048576 0 0.08388608 15 7.347001 [Expr1056] null PLAN_ROW False 1
1048576 1             |--Segment 1 4 3 Segment Segment null null 1048576 0 0.02097152 15 7.263114 [Segment1057] null PLAN_ROW False 1
1048576 1                  |--Nested Loops(Inner Join) 1 5 4 Nested Loops Inner Join null null 1048576 0 4.383048 9 7.242143 null NO JOIN PREDICATE PLAN_ROW False 1
262144 1                       |--Nested Loops(Inner Join) 1 6 5 Nested Loops Inner Join null null 262144 0 1.095762 9 1.810519 null NO JOIN PREDICATE PLAN_ROW False 1
65536 1                       | |--Nested Loops(Inner Join) 1 7 6 Nested Loops Inner Join null null 65536 0 0.2739405 9 0.4526129 null NO JOIN PREDICATE PLAN_ROW False 1
16384 1                       | | |--Nested Loops(Inner Join) 1 8 7 Nested Loops Inner Join null null 16384 0 0.06848512 9 0.1131363 null NO JOIN PREDICATE PLAN_ROW False 1
4096 1                       | | | |--Nested Loops(Inner Join) 1 9 8 Nested Loops Inner Join null null 4096 0 0.01712128 9 0.02826702 null NO JOIN PREDICATE PLAN_ROW False 1
1024 1                       | | | | |--Nested Loops(Inner Join) 1 10 9 Nested Loops Inner Join null null 1024 0 0.00428032 9 0.007049585 null NO JOIN PREDICATE PLAN_ROW False 1
256 1                       | | | | | |--Nested Loops(Inner Join) 1 11 10 Nested Loops Inner Join null null 256 0 0.00107008 9 0.001745108 null NO JOIN PREDICATE PLAN_ROW False 1
64 1                       | | | | | | |--Nested Loops(Inner Join) 1 12 11 Nested Loops Inner Join null null 64 0 0.00026752 9 0.000418871 null NO JOIN PREDICATE PLAN_ROW False 1
16 1                       | | | | | | | |--Nested Loops(Inner Join) 1 13 12 Nested Loops Inner Join null null 16 0 6.688E-05 9 8.7194E-05 null NO JOIN PREDICATE PLAN_ROW False 1
4 1                       | | | | | | | | |--Constant Scan 1 14 13 Constant Scan Constant Scan null null 4 0 4.157E-06 9 4.157E-06 null null PLAN_ROW False 1
16 4                       | | | | | | | | |--Constant Scan 1 15 13 Constant Scan Constant Scan null null 4 0 4.157E-06 9 1.6157E-05 null null PLAN_ROW False 4
64 16                       | | | | | | | |--Constant Scan 1 16 12 Constant Scan Constant Scan null null 4 0 4.157E-06 9 6.4157E-05 null null PLAN_ROW False 16
256 64                       | | | | | | |--Constant Scan 1 17 11 Constant Scan Constant Scan null null 4 0 4.157E-06 9 6.4157E-05 null null PLAN_ROW False 16
1024 256                       | | | | | |--Constant Scan 1 18 10 Constant Scan Constant Scan null null 4 0 4.157E-06 9 0.001024157 null null PLAN_ROW False 256
4096 1024                       | | | | |--Constant Scan 1 19 9 Constant Scan Constant Scan null null 4 0 4.157E-06 9 0.001024157 null null PLAN_ROW False 256
16384 4096                       | | | |--Constant Scan 1 20 8 Constant Scan Constant Scan null null 4 0 4.157E-06 9 0.01638416 null null PLAN_ROW False 4096
65536 16384                       | | |--Constant Scan 1 21 7 Constant Scan Constant Scan null null 4 0 4.157E-06 9 0.01638416 null null PLAN_ROW False 4096
262144 65536                       | |--Constant Scan 1 22 6 Constant Scan Constant Scan null null 4 0 4.157E-06 9 0.2621441 null null PLAN_ROW False 65536
1048576 262144                       |--Constant Scan 1 23 5 Constant Scan Constant Scan null null 4 0 4.157E-06 9 0.2621441 null null PLAN_ROW False 65536
Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.3.1" Build="12.0.5000.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="with&#xa; p0(i) as (select 1 union all select 1 union all select 1 union all select 1)&#xa;, p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows&#xa;, p2(i) as (select 1 from p1 as a, p1 as b)--1M rows&#xa;select row_number() over(order by i) as val&#xa;into integers&#xa;from p2" StatementId="2" StatementCompId="1" StatementType="SELECT INTO" RetrievedFromCache="true" StatementSubTreeCost="21.3718" StatementEstRows="1.04858e+006" StatementOptmLevel="FULL" QueryHash="0xB127C3F292E989BD" QueryPlanHash="0x5CE8AC913CB3D741" CardinalityEstimationModelVersion="120"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="56" CompileTime="15" CompileCPU="15" CompileMemory="992"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209662" EstimatedPagesCached="13103" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="242136"/><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><RelOp NodeId="0" PhysicalOp="Table Insert" LogicalOp="Insert" EstimateRows="1.04858e+006" EstimateIO="12.9762" EstimateCPU="1.04858" AvgRowSize="9" EstimatedTotalSubtreeCost="21.3718" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1048576" ActualElapsedms="1343" ActualCPUms="1343" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Update DMLRequestSort="0"><Object Table="[integers]" Storage="RowStore"/><SetPredicate><ScalarOperator ScalarString="[integers].[val] = [Expr1056]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[integers]" Column="val"/><ScalarOperator><Identifier><ColumnReference Column="Expr1056"/></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1.04858e+006" EstimateIO="0" EstimateCPU="0.0838861" AvgRowSize="15" EstimatedTotalSubtreeCost="7.347" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1056"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1048576" ActualElapsedms="140" ActualCPUms="140" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1056"/><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"/></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1.04858e+006" EstimateIO="0" EstimateCPU="0.0209715" AvgRowSize="15" EstimatedTotalSubtreeCost="7.26311" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Segment1057"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1048576" ActualElapsedms="62" ActualCPUms="62" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Segment><GroupBy/><SegmentColumn><ColumnReference Column="Segment1057"/></SegmentColumn><RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.04858e+006" EstimateIO="0" EstimateCPU="4.38305" AvgRowSize="9" EstimatedTotalSubtreeCost="7.24214" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1048576" ActualElapsedms="46" ActualCPUms="46" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="262144" EstimateIO="0" EstimateCPU="1.09576" AvgRowSize="9" EstimatedTotalSubtreeCost="1.81052" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="262144" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="5" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="65536" EstimateIO="0" EstimateCPU="0.27394" AvgRowSize="9" EstimatedTotalSubtreeCost="0.452613" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="65536" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="6" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="16384" EstimateIO="0" EstimateCPU="0.0684851" AvgRowSize="9" EstimatedTotalSubtreeCost="0.113136" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="16384" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="4096" EstimateIO="0" EstimateCPU="0.0171213" AvgRowSize="9" EstimatedTotalSubtreeCost="0.028267" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4096" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="8" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1024" EstimateIO="0" EstimateCPU="0.00428032" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00704959" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1024" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="256" EstimateIO="0" EstimateCPU="0.00107008" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00174511" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="256" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="10" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="64" EstimateIO="0" EstimateCPU="0.00026752" AvgRowSize="9" EstimatedTotalSubtreeCost="0.000418871" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="64" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="11" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="16" EstimateIO="0" EstimateCPU="6.688e-005" AvgRowSize="9" EstimatedTotalSubtreeCost="8.7194e-005" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><Warnings NoJoinPredicate="1"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="16" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="12" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="4.157e-006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><ConstantScan/></RelOp><RelOp NodeId="13" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="1.6157e-005" Parallel="0" EstimateRebinds="0" EstimateRewinds="3" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="16" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="4" ActualExecutions="4"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp><RelOp NodeId="14" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="6.4157e-005" Parallel="0" EstimateRebinds="0" EstimateRewinds="15" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="64" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="16" ActualExecutions="16"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp><RelOp NodeId="15" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="6.4157e-005" Parallel="0" EstimateRebinds="0" EstimateRewinds="15" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="256" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="64" ActualExecutions="64"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp><RelOp NodeId="16" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00102416" Parallel="0" EstimateRebinds="0" EstimateRewinds="255" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1024" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="256" ActualExecutions="256"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp><RelOp NodeId="17" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00102416" Parallel="0" EstimateRebinds="0" EstimateRewinds="255" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4096" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1024" ActualExecutions="1024"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp><RelOp NodeId="18" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0163842" Parallel="0" EstimateRebinds="0" EstimateRewinds="4095" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="16384" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="4096" ActualExecutions="4096"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp><RelOp NodeId="19" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0163842" Parallel="0" EstimateRebinds="0" EstimateRewinds="4095" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="65536" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="16384" ActualExecutions="16384"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp><RelOp NodeId="20" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.262144" Parallel="0" EstimateRebinds="0" EstimateRewinds="65535" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="262144" ActualElapsedms="15" ActualCPUms="15" ActualEndOfScans="65536" ActualExecutions="65536"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp><RelOp NodeId="21" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="4" EstimateIO="0" EstimateCPU="4.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.262144" Parallel="0" EstimateRebinds="0" EstimateRewinds="65535" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1048576" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="262144" ActualExecutions="262144"/></RunTimeInformation><ConstantScan/></RelOp></NestedLoops></RelOp></Segment></RelOp></SequenceProject></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
with p0(i) as (select 1 union all select 1 union all select 1 union all select 1) , p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows , p2(i) as (select 1 from p1 as a, p1 as b)--1M rows select row_number() over(order by i) as val into integers from p2
SELECT INTO
SELECT INTO
Cached plan size56 KB
Estimated Operator Cost0 (0%)
Estimated Subtree Cost21.3718
Degree of Parallelism0
Estimated Number of Rows1.04858e+006
Statement
with p0(i) as (select 1 union all select 1 union all select 1 union all select 1) , p1(i) as (select 1 from p0 as a, p0 as b, p0 as c, p0 as d, p0 as e)--1K rows , p2(i) as (select 1 from p1 as a, p1 as b)--1M rows select row_number() over(order by i) as val into integers from p2
Table Insert
(Insert)
Cost: 66%
Table Insert
Insert input rows into the table specified in Argument field.
Physical OperationTable Insert
Logical OperationInsert
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1048576
Actual Number of Batches0
Estimated Operator Cost14.0248 (66%)
Estimated I/O Cost12.9762
Estimated CPU Cost1.04858
Estimated Subtree Cost21.3718
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1.04858e+006
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID0
Object
[integers]
Predicate
[integers].[val] = [Expr1056]
Sequence Project
(Compute Scalar)
Cost: 0%
Sequence Project
Physical OperationSequence Project
Logical OperationCompute Scalar
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1048576
Actual Number of Batches0
Estimated Operator Cost0.08389 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0838861
Estimated Subtree Cost7.347
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1.04858e+006
Estimated Row Size15 B
Actual Rebinds0
Actual Rewinds0
Node ID1
Output List
Expr1056
Segment
Cost: 0%
Segment
Physical OperationSegment
Logical OperationSegment
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1048576
Actual Number of Batches0
Estimated Operator Cost0.02097 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0209715
Estimated Subtree Cost7.26311
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1.04858e+006
Estimated Row Size15 B
Actual Rebinds0
Actual Rewinds0
Node ID2
Output List
Segment1057
Nested Loops
(Inner Join)
Cost: 24%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1048576
Actual Number of Batches0
Estimated Operator Cost5.169476 (24%)
Estimated I/O Cost0
Estimated CPU Cost4.38305
Estimated Subtree Cost7.24214
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1.04858e+006
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID3
Warnings
No Join Predicate
Nested Loops
(Inner Join)
Cost: 5%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows262144
Actual Number of Batches0
Estimated Operator Cost1.095763 (5%)
Estimated I/O Cost0
Estimated CPU Cost1.09576
Estimated Subtree Cost1.81052
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows262144
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID4
Warnings
No Join Predicate
Nested Loops
(Inner Join)
Cost: 2%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows65536
Actual Number of Batches0
Estimated Operator Cost0.3230928 (2%)
Estimated I/O Cost0
Estimated CPU Cost0.27394
Estimated Subtree Cost0.452613
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows65536
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID5
Warnings
No Join Predicate
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows16384
Actual Number of Batches0
Estimated Operator Cost0.0684848 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0684851
Estimated Subtree Cost0.113136
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows16384
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID6
Warnings
No Join Predicate
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows4096
Actual Number of Batches0
Estimated Operator Cost0.0201933 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0171213
Estimated Subtree Cost0.028267
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows4096
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID7
Warnings
No Join Predicate
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1024
Actual Number of Batches0
Estimated Operator Cost0.0042803 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0042803
Estimated Subtree Cost0.0070496
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows1024
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID8
Warnings
No Join Predicate
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows256
Actual Number of Batches0
Estimated Operator Cost0.0012621 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0010701
Estimated Subtree Cost0.0017451
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows256
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID9
Warnings
No Join Predicate
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows64
Actual Number of Batches0
Estimated Operator Cost0.0002675 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0002675
Estimated Subtree Cost0.0004189
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows64
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID10
Warnings
No Join Predicate
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
Physical OperationNested Loops
Logical OperationInner Join
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows16
Actual Number of Batches0
Estimated Operator Cost0.0000669 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000669
Estimated Subtree Cost0.0000872
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows16
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID11
Warnings
No Join Predicate
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows4
Actual Number of Batches0
Estimated Operator Cost0.0000042 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0000042
Estimated Number of Executions1
Number of Executions1
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID12
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows16
Actual Number of Batches0
Estimated Operator Cost0.0000162 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0000162
Estimated Number of Executions1
Number of Executions4
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID13
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows64
Actual Number of Batches0
Estimated Operator Cost0.0000642 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0000642
Estimated Number of Executions1
Number of Executions16
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID14
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows256
Actual Number of Batches0
Estimated Operator Cost0.0000642 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0000642
Estimated Number of Executions1
Number of Executions64
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID15
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1024
Actual Number of Batches0
Estimated Operator Cost0.0010242 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0010242
Estimated Number of Executions1
Number of Executions256
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID16
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows4096
Actual Number of Batches0
Estimated Operator Cost0.0010242 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0010242
Estimated Number of Executions1
Number of Executions1024
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID17
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows16384
Actual Number of Batches0
Estimated Operator Cost0.0163842 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0163842
Estimated Number of Executions1
Number of Executions4096
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID18
Constant Scan
Cost: 0%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows65536
Actual Number of Batches0
Estimated Operator Cost0.0163842 (0%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.0163842
Estimated Number of Executions1
Number of Executions16384
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID19
Constant Scan
Cost: 1%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows262144
Actual Number of Batches0
Estimated Operator Cost0.262144 (1%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.262144
Estimated Number of Executions1
Number of Executions65536
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID20
Constant Scan
Cost: 1%
Constant Scan
Physical OperationConstant Scan
Logical OperationConstant Scan
Actual Execution ModeRow
Estimated Execution ModeRow
Actual Number of Rows1048576
Actual Number of Batches0
Estimated Operator Cost0.262144 (1%)
Estimated I/O Cost0
Estimated CPU Cost0.0000042
Estimated Subtree Cost0.262144
Estimated Number of Executions1
Number of Executions262144
Estimated Number of Rows4
Estimated Row Size9 B
Actual Rebinds0
Actual Rewinds0
Node ID21