By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Parent (Id int identity primary key, SomeCol bit not null, OtherCol int not null)
CREATE TABLE Child (Id int identity primary key, ParentId int not null references Parent (Id) INDEX IX_Parent NONCLUSTERED)
SET STATISTICS XML ON;
SELECT c.*
FROM Child c
JOIN Parent p ON p.Id = c.ParentId;
| Id | ParentId |
|---|
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT c.*
FROM Child c
JOIN Parent p ON p.Id = c.ParentId" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0032831" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x71F0F53520D1021C" QueryPlanHash="0x4E7FD3C261E44941" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="128"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4035880"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Index="[IX_Parent]" Alias="[c]" IndexKind="NonClustered" Storage="RowStore"></Object></IndexScan></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
SELECT c.*
FROM Child c
JOIN Parent p ON p.Id = c.ParentId
SELECT
SELECT
| Cached plan size | 16 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0032831 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
SELECT c.*
FROM Child c
JOIN Parent p ON p.Id = c.ParentId
Index Scan (NonClustered)
[Child].[IX_Parent].[c]
Cost: 100%
Index Scan (NonClustered)
| Physical Operation | Index Scan |
|---|---|
| Logical Operation | Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (100%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001581 |
| Estimated Subtree Cost | 0.0032831 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 15 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | False |
| Node ID | 0 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[IX_Parent].[c]
CREATE VIEW dbo.vChild
WITH SCHEMABINDING
AS
SELECT c.Id, c.ParentId
FROM dbo.Child c
JOIN dbo.Parent p ON p.Id = c.ParentId
WHERE p.SomeCol = 0;
CREATE UNIQUE CLUSTERED INDEX CX_vChild ON vChild (Id)
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="insert [dbo].[vChild] select * from [dbo].[vChild] option (maxdop 1)" StatementId="1" StatementCompId="1" StatementType="INSERT" RetrievedFromCache="false" StatementSubTreeCost="0.0165719" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xD943D8A07FFFD837" QueryPlanHash="0xC67DA087B362D947" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="MaxDOPSetToOne" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="208"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4035880"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Index Insert" LogicalOp="Insert" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0165719" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><CreateIndex><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[vChild]" Index="[CX_vChild]" IndexKind="Clustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00657086" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Index="[PK__Child__3214EC077C50CFCA]" Alias="[c]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0743A73AD1]" Alias="[p]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Alias="[p]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[ParentId] as [c].[ParentId]"><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol] as [p].[SomeCol]=(0)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Alias="[p]" Column="SomeCol"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></CreateIndex></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
insert [dbo].[vChild] select * from [dbo].[vChild] option (maxdop 1)
INSERT
INSERT
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0165719 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
insert [dbo].[vChild] select * from [dbo].[vChild] option (maxdop 1)
Index Insert
(Insert)
Cost: 60%
Index Insert
| Physical Operation | Index Insert |
|---|---|
| Logical Operation | Insert |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.010001 (60%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.0165719 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[CX_vChild]
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 Operation | Nested Loops |
|---|---|
| Logical Operation | Inner Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000047 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000042 |
| Estimated Subtree Cost | 0.0065709 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 15 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 1 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Outer References
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Clustered Index Scan (Clustered)
[Child].[PK__Child__3214EC077C50CFC…
Cost: 20%
Clustered Index Scan (Clustered)
Scanning a clustered index, entirely or only a range.
| Physical Operation | Clustered Index Scan |
|---|---|
| Logical Operation | Clustered Index Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (20%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001581 |
| Estimated Subtree Cost | 0.0032831 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 15 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 2 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[PK__Child__3214EC077C50CFCA].[c]
Clustered Index Seek (Clustered)
[Parent].[PK__Parent__3214EC0743A73…
Cost: 20%
Clustered Index Seek (Clustered)
Scanning a particular range of rows from a clustered index.
| Physical Operation | Clustered Index Seek |
|---|---|
| Logical Operation | Clustered Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (20%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001581 |
| Estimated Subtree Cost | 0.0032831 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows to be Read | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 3 |
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[PK__Parent__3214EC0743A73AD1].[p]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id = Scalar Operator([fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[ParentId] as [c].[ParentId])
Predicate
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol] as [p].[SomeCol]=(0)
SET STATISTICS XML ON;
UPDATE Parent
SET SomeCol = 1
WHERE Id BETWEEN 100 AND 200;
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="UPDATE [Parent] set [SomeCol] = @1 WHERE [Id]>=@2 AND [Id]<=@3" StatementId="1" StatementCompId="2" StatementType="UPDATE" RetrievedFromCache="false" StatementSubTreeCost="0.0381416" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x60FF52B9C5209FE4" QueryPlanHash="0x941FF3B44C538698" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="56" CompileTime="15" CompileCPU="15" CompileMemory="504"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="1024792" GrantedMemory="1024" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4035880"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Sequence" LogicalOp="Sequence" EstimateRows="1" EstimateIO="0" EstimateCPU="3e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0381416" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Sequence><RelOp NodeId="2" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="2" EstimateIO="0" EstimateCPU="5.0475e-05" AvgRowSize="16" EstimatedTotalSubtreeCost="0.00674305" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ColumnReference Column="Act1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="3" PhysicalOp="Split" LogicalOp="Split" EstimateRows="2" EstimateIO="0" EstimateCPU="1.5e-06" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0132857" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ColumnReference Column="Act1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Split><DefinedValues><DefinedValue><ColumnReference Column="Act1013"></ColumnReference></DefinedValue></DefinedValues><ActionColumn><ColumnReference Column="Act1013"></ColumnReference></ActionColumn><RelOp NodeId="4" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0132842" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ColumnReference Column="SomeCol_OLD"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SimpleUpdate DMLRequestSort="0"><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></DefinedValue></DefinedValues><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0743A73AD1]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(100)"><Const ConstValue="(100)"></Const></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(200)"><Const ConstValue="(200)"></Const></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew><SetPredicate><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol] = [Expr1002]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate></SimpleUpdate></RelOp></Split></RelOp></Spool></RelOp><RelOp NodeId="5" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0313956" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="1"><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[vChild]" Index="[CX_vChild]" IndexKind="ViewClustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[Id] = RaiseIfNullUpdate([fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[Id] as [c].[Id]),[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[ParentId] = RaiseIfNullUpdate([fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[ParentId] as [c].[ParentId])"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[vChild]" Column="Id"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[vChild]" Column="ParentId"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><ActionColumn><ColumnReference Column="Act1013"></ColumnReference></ActionColumn><RelOp NodeId="6" PhysicalOp="Collapse" LogicalOp="Collapse" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-06" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0213946" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference><ColumnReference Column="Act1013"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[vChild]" Column="Id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Collapse><GroupBy><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></GroupBy><RelOp NodeId="8" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100019" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0213926" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference><ColumnReference Column="Act1013"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="0"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Act1013"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0100313" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference><ColumnReference Column="Act1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></OuterReferences><RelOp NodeId="11" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="9.6e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00674401" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference><ColumnReference Column="Act1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="12" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="2" EstimateIO="0" EstimateCPU="5.0475e-05" AvgRowSize="16" EstimatedTotalSubtreeCost="0.00674305" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ColumnReference Column="Act1013"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool PrimaryNodeId="2"></Spool></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol]=(0)"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="13" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Index="[IX_Parent]" Alias="[c]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[Id]"><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Sort></RelOp></Collapse></RelOp></Update></RelOp></Sequence></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference><ColumnReference Column="@3" ParameterDataType="tinyint" ParameterCompiledValue="(200)" ParameterRuntimeValue="(200)"></ColumnReference><ColumnReference Column="@2" ParameterDataType="tinyint" ParameterCompiledValue="(100)" ParameterRuntimeValue="(100)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
UPDATE [Parent] set [SomeCol] = @1 WHERE [Id]>=@2 AND [Id]<=@3
UPDATE
UPDATE
| Cached plan size | 56 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0381416 |
| Degree of Parallelism | 0 |
| Memory Grant | 1024 |
| Estimated Number of Rows | 1 |
Statement
UPDATE [Parent] set [SomeCol] = @1 WHERE [Id]>=@2 AND [Id]<=@3
Sequence
Cost: 0%
Sequence
| Physical Operation | Sequence |
|---|---|
| Logical Operation | Sequence |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000029 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.000003 |
| Estimated Subtree Cost | 0.0381416 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 1 |
Table Spool
(Eager Spool)
Cost: 0%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Eager Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000505 |
| Estimated Subtree Cost | 0.0067431 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 2 |
| Estimated Row Size | 16 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 2 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].SomeCol
Act1013
Split
Cost: 0%
Split
| Physical Operation | Split |
|---|---|
| Logical Operation | Split |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000015 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000015 |
| Estimated Subtree Cost | 0.0132857 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 2 |
| Estimated Row Size | 16 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 3 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].SomeCol
Act1013
Clustered Index Update
(Update)
Cost: 35%
Clustered Index Update
| Physical Operation | Clustered Index Update |
|---|---|
| Logical Operation | Update |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0132842 (35%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.0132842 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 12 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 4 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].SomeCol
SomeCol_OLD
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[PK__Parent__3214EC0743A73AD1]
Predicate
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol] = [Expr1002]
Clustered Index Update
(Update)
Cost: 26%
Clustered Index Update
| Physical Operation | Clustered Index Update |
|---|---|
| Logical Operation | Update |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.010001 (26%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.0313956 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 5 |
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[CX_vChild]
Predicate
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[Id] = RaiseIfNullUpdate([fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[Id] as [c].[Id]),[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[ParentId] = RaiseIfNullUpdate([fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[ParentId] as [c].[ParentId])
Collapse
Cost: 0%
Collapse
| Physical Operation | Collapse |
|---|---|
| Logical Operation | Collapse |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.000002 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.000002 |
| Estimated Subtree Cost | 0.0213946 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 23 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 6 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Act1013
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].Id
Sort
Cost: 30%
Sort
Sort the input.
| Physical Operation | Sort |
|---|---|
| Logical Operation | Sort |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0113613 (30%) |
| Estimated I/O Cost | 0.0112613 |
| Estimated CPU Cost | 0.0001 |
| Estimated Subtree Cost | 0.0213926 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 19 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 8 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Act1013
Order By
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id Ascending
Act1013 Ascending
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 Operation | Nested Loops |
|---|---|
| Logical Operation | Inner Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000042 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000042 |
| Estimated Subtree Cost | 0.0100313 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 19 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 9 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Act1013
Outer References
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
Filter
Cost: 0%
Filter
| Physical Operation | Filter |
|---|---|
| Logical Operation | Filter |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.006744 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 15 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 11 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
Act1013
Predicate
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol]=(0)
Table Spool
(Eager Spool)
Cost: 18%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Eager Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0067431 (18%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000505 |
| Estimated Subtree Cost | 0.0067431 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 2 |
| Estimated Row Size | 16 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 12 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].SomeCol
Act1013
Index Seek (NonClustered)
[Child].[IX_Parent].[c]
Cost: 9%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
| Physical Operation | Index Seek |
|---|---|
| Logical Operation | Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (9%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001581 |
| Estimated Subtree Cost | 0.0032831 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows to be Read | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 15 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 13 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[IX_Parent].[c]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].ParentId = Scalar Operator([fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[Id])
SET STATISTICS XML ON;
UPDATE Parent
SET OtherCol = 1
WHERE Id BETWEEN 100 AND 200;
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="UPDATE [Parent] set [OtherCol] = @1 WHERE [Id]>=@2 AND [Id]<=@3" StatementId="1" StatementCompId="2" StatementType="UPDATE" RetrievedFromCache="true" StatementSubTreeCost="0.0132841" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xEED39FDF2CE0291E" QueryPlanHash="0x265F27ACDC089299" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="272"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4035880"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0132841" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SimpleUpdate DMLRequestSort="0"><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0743A73AD1]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@2],0)"><Identifier><ColumnReference Column="ConstExpr1009"><ScalarOperator><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@2"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@3],0)"><Identifier><ColumnReference Column="ConstExpr1010"><ScalarOperator><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@3"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew><SetPredicate><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[OtherCol] = RaiseIfNullUpdate([@1])"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="OtherCol"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate></SimpleUpdate></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference><ColumnReference Column="@3" ParameterDataType="tinyint" ParameterCompiledValue="(200)" ParameterRuntimeValue="(200)"></ColumnReference><ColumnReference Column="@2" ParameterDataType="tinyint" ParameterCompiledValue="(100)" ParameterRuntimeValue="(100)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
UPDATE [Parent] set [OtherCol] = @1 WHERE [Id]>=@2 AND [Id]<=@3
UPDATE
UPDATE
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0132841 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
UPDATE [Parent] set [OtherCol] = @1 WHERE [Id]>=@2 AND [Id]<=@3
Clustered Index Update
(Update)
Cost: 100%
Clustered Index Update
| Physical Operation | Clustered Index Update |
|---|---|
| Logical Operation | Update |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0132841 (100%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.0132841 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[PK__Parent__3214EC0743A73AD1]
Predicate
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[OtherCol] = RaiseIfNullUpdate([@1])
SET STATISTICS XML ON;
INSERT Parent (SomeCol, OtherCol)
VALUES (0, 100)
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="INSERT INTO [Parent]([SomeCol],[OtherCol]) values(@1,@2)" StatementId="1" StatementCompId="2" StatementType="INSERT" RetrievedFromCache="false" StatementSubTreeCost="0.0234929" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x0C2FE2F4D51A6A25" QueryPlanHash="0xDA60FCB2C813DCEE" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="320"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4035880"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Sequence" LogicalOp="Sequence" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0234929" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Sequence><RelOp NodeId="2" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="5.05459e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00510133" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="5" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="3" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0100025" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0743A73AD1]" IndexKind="Clustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol] = [Expr1003],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[OtherCol] = [Expr1008],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[Id] = [Expr1002]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="OtherCol"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="16" EstimatedTotalSubtreeCost="1.457e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="(100)"><Const ConstValue="(100)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="12" EstimatedTotalSubtreeCost="1.357e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="1.257e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="getidentity((581577110),(639),NULL)"><Intrinsic FunctionName="getidentity"><ScalarOperator><Const ConstValue="(581577110)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(639)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="1.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan></ConstantScan></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Update></RelOp></Spool></RelOp><RelOp NodeId="8" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0183896" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[vChild]" Index="[CX_vChild]" IndexKind="ViewClustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[Id] = [fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[Id] as [c].[Id],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[ParentId] = [fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[ParentId] as [c].[ParentId]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[vChild]" Column="Id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[vChild]" Column="ParentId"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00838861" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></OuterReferences><RelOp NodeId="11" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="5.05459e-05" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00510133" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool PrimaryNodeId="2"></Spool></RelOp><RelOp NodeId="12" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Index="[IX_Parent]" Alias="[c]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[Id]"><Identifier><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp></Sequence></RelOp><ParameterList><ColumnReference Column="@2" ParameterDataType="int" ParameterCompiledValue="(100)" ParameterRuntimeValue="(100)"></ColumnReference><ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(0)" ParameterRuntimeValue="(0)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
INSERT INTO [Parent]([SomeCol],[OtherCol]) values(@1,@2)
INSERT
INSERT
| Cached plan size | 40 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0234929 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
INSERT INTO [Parent]([SomeCol],[OtherCol]) values(@1,@2)
Sequence
Cost: 0%
Sequence
| Physical Operation | Sequence |
|---|---|
| Logical Operation | Sequence |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.000002 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.000002 |
| Estimated Subtree Cost | 0.0234929 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 1 |
Table Spool
(Eager Spool)
Cost: 0%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Eager Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Number of Rows Read | 1 |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000505 |
| Estimated Subtree Cost | 0.0051013 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 11 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 2 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
Clustered Index Insert
(Insert)
Cost: 43%
Clustered Index Insert
| Physical Operation | Clustered Index Insert |
|---|---|
| Logical Operation | Insert |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.010001 (43%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.0100025 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 11 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 3 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[PK__Parent__3214EC0743A73AD1]
Predicate
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol] = [Expr1003],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[OtherCol] = [Expr1008],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[Id] = [Expr1002]
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.0000015 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 16 B |
| Node ID | 4 |
Output List
Expr1002
Expr1003
Expr1008
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.0000014 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 12 B |
| Node ID | 5 |
Output List
Expr1002
Expr1003
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.0000013 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 11 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 6 |
Output List
Expr1002
Constant Scan
Cost: 0%
Constant Scan
| Physical Operation | Constant Scan |
|---|---|
| Logical Operation | Constant Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000012 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000012 |
| Estimated Subtree Cost | 0.0000012 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 7 |
Clustered Index Insert
(Insert)
Cost: 43%
Clustered Index Insert
| Physical Operation | Clustered Index Insert |
|---|---|
| Logical Operation | Insert |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.010001 (43%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.0183896 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 8 |
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[CX_vChild]
Predicate
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[Id] = [fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[Id] as [c].[Id],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[vChild].[ParentId] = [fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[ParentId] as [c].[ParentId]
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 Operation | Nested Loops |
|---|---|
| Logical Operation | Inner Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000042 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000042 |
| Estimated Subtree Cost | 0.0083886 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 15 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 9 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Outer References
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
Table Spool
(Eager Spool)
Cost: 22%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Eager Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0051013 (22%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000505 |
| Estimated Subtree Cost | 0.0051013 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 11 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 11 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].Id
Index Seek (NonClustered)
[Child].[IX_Parent].[c]
Cost: 14%
Index Seek (NonClustered)
Scan a particular range of rows from a nonclustered index.
| Physical Operation | Index Seek |
|---|---|
| Logical Operation | Index Seek |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Actual Number of Rows | 0 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032831 (14%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001581 |
| Estimated Subtree Cost | 0.0032831 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 15 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Ordered | True |
| Node ID | 12 |
Output List
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].Id
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[c].ParentId
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].[IX_Parent].[c]
Seek Predicates
Seek Keys[1]: Prefix: [fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Child].ParentId = Scalar Operator([fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[Id])
SET STATISTICS XML ON;
INSERT Parent (SomeCol, OtherCol)
VALUES (1, 100)
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="INSERT INTO [Parent]([SomeCol],[OtherCol]) values(@1,@2)" StatementId="1" StatementCompId="2" StatementType="INSERT" RetrievedFromCache="false" StatementSubTreeCost="0.0100025" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x0C2FE2F4D51A6A25" QueryPlanHash="0xCCCCBDAA1D9CC013" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="264"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4035880"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0100025" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0743A73AD1]" IndexKind="Clustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol] = [Expr1003],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[OtherCol] = [Expr1008],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[Id] = [Expr1002]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="OtherCol"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="16" EstimatedTotalSubtreeCost="1.457e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="(100)"><Const ConstValue="(100)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="12" EstimatedTotalSubtreeCost="1.357e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="1.257e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="getidentity((581577110),(639),NULL)"><Intrinsic FunctionName="getidentity"><ScalarOperator><Const ConstValue="(581577110)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(639)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="1.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan></ConstantScan></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@2" ParameterDataType="int" ParameterCompiledValue="(100)" ParameterRuntimeValue="(100)"></ColumnReference><ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
INSERT INTO [Parent]([SomeCol],[OtherCol]) values(@1,@2)
INSERT
INSERT
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0100025 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
INSERT INTO [Parent]([SomeCol],[OtherCol]) values(@1,@2)
Clustered Index Insert
(Insert)
Cost: 100%
Clustered Index Insert
| Physical Operation | Clustered Index Insert |
|---|---|
| Logical Operation | Insert |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.010001 (100%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000001 |
| Estimated Subtree Cost | 0.0100025 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Object
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[PK__Parent__3214EC0743A73AD1]
Predicate
[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[SomeCol] = [Expr1003],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[OtherCol] = [Expr1008],[fiddle_4fae05d0c55b4a06a22a2a0d22e4ddb2].[dbo].[Parent].[Id] = [Expr1002]
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.0000015 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 16 B |
| Node ID | 1 |
Output List
Expr1002
Expr1003
Expr1008
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.0000014 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 12 B |
| Node ID | 2 |
Output List
Expr1002
Expr1003
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000001 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.0000013 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 11 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 3 |
Output List
Expr1002
Constant Scan
Cost: 0%
Constant Scan
| Physical Operation | Constant Scan |
|---|---|
| Logical Operation | Constant Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 1 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000012 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000012 |
| Estimated Subtree Cost | 0.0000012 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 4 |