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="4001264"></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_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" 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_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" 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_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Child].[c].Id
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Child].[c].ParentId
Object
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[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;
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.0132841" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x293266036DC6A4A5" QueryPlanHash="0x4AEECE23F69378E3" 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="160"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4002976"></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.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="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><CreateIndex><Object Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[vChild]" Index="[CX_vChild]" IndexKind="Clustered" Storage="RowStore"></Object><RelOp NodeId="1" 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_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" 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_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Child]" Alias="[c]" Column="ParentId"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Child]" Index="[PK__Child__3214EC07AF827D1A]" Alias="[c]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></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.0132841 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
insert [dbo].[vChild] select * from [dbo].[vChild] option (maxdop 1)
Index Insert
(Insert)
Cost: 75%
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 (75%) |
| 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_035b12bfa8b9464abc6521264c3a3c95].[dbo].[vChild].[CX_vChild]
Clustered Index Scan (Clustered)
[Child].[PK__Child__3214EC07AF827D1…
Cost: 25%
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 (25%) |
| 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 | 1 |
Output List
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Child].[c].Id
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Child].[c].ParentId
Object
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Child].[PK__Child__3214EC07AF827D1A].[c]
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="true" StatementSubTreeCost="0.0132842" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x60FF52B9C5209FE4" 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="304"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4002976"></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.0132842" 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"><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(bit,[@1],0)"><Identifier><ColumnReference Column="ConstExpr1012"><ScalarOperator><Convert DataType="bit" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><Object Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0712E82435]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@2],0)"><Identifier><ColumnReference Column="ConstExpr1010"><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_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Column="Id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@3],0)"><Identifier><ColumnReference Column="ConstExpr1011"><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_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[SomeCol] = RaiseIfNullUpdate([Expr1002])"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></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 [SomeCol] = @1 WHERE [Id]>=@2 AND [Id]<=@3
UPDATE
UPDATE
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0132842 |
| Degree of Parallelism | 0 |
| Estimated Number of Rows | 1 |
Statement
UPDATE [Parent] set [SomeCol] = @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.0132842 (100%) |
| 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 | 9 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Object
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[PK__Parent__3214EC0712E82435]
Predicate
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[SomeCol] = RaiseIfNullUpdate([Expr1002])
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="264"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4002976"></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_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0712E82435]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" 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_035b12bfa8b9464abc6521264c3a3c95]" 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_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[OtherCol] = RaiseIfNullUpdate([@1])"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" 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_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[PK__Parent__3214EC0712E82435]
Predicate
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[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="true" StatementSubTreeCost="0.0100024" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x0C2FE2F4D51A6A25" QueryPlanHash="0xD4E0EC43C6330E98" 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="192"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4002976"></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.0100024" 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="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0712E82435]" IndexKind="Clustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[SomeCol] = RaiseIfNullInsert([Expr1003]),[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[OtherCol] = RaiseIfNullInsert([@2]),[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[Id] = [Expr1002]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Column="OtherCol"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="@2"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" 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="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="CONVERT_IMPLICIT(bit,[@1],0)"><Convert DataType="bit" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" 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),(642),NULL)"><Intrinsic FunctionName="getidentity"><ScalarOperator><Const ConstValue="(581577110)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(642)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" 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></Update></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 | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0100024 |
| 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.0100024 |
| 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_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[PK__Parent__3214EC0712E82435]
Predicate
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[SomeCol] = RaiseIfNullInsert([Expr1003]),[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[OtherCol] = RaiseIfNullInsert([@2]),[fiddle_035b12bfa8b9464abc6521264c3a3c95].[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.0000014 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 12 B |
| Node ID | 1 |
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 | 2 |
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 | 3 |
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="true" StatementSubTreeCost="0.0100024" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x0C2FE2F4D51A6A25" QueryPlanHash="0xD4E0EC43C6330E98" 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="192"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="4002976"></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.0100024" 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_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Index="[PK__Parent__3214EC0712E82435]" IndexKind="Clustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[SomeCol] = RaiseIfNullInsert([Expr1003]),[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[OtherCol] = RaiseIfNullInsert([@2]),[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[Id] = [Expr1002]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Column="SomeCol"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" Schema="[dbo]" Table="[Parent]" Column="OtherCol"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="@2"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Database="[fiddle_035b12bfa8b9464abc6521264c3a3c95]" 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="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="CONVERT_IMPLICIT(bit,[@1],0)"><Convert DataType="bit" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" 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),(642),NULL)"><Intrinsic FunctionName="getidentity"><ScalarOperator><Const ConstValue="(581577110)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(642)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" 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></Update></RelOp><ParameterList><ColumnReference Column="@2" ParameterDataType="int" ParameterCompiledValue="(100)" ParameterRuntimeValue="(100)"></ColumnReference><ColumnReference Column="@1" ParameterDataType="int" ParameterCompiledValue="(0)" 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.0100024 |
| 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.0100024 |
| 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_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[PK__Parent__3214EC0712E82435]
Predicate
[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[SomeCol] = RaiseIfNullInsert([Expr1003]),[fiddle_035b12bfa8b9464abc6521264c3a3c95].[dbo].[Parent].[OtherCol] = RaiseIfNullInsert([@2]),[fiddle_035b12bfa8b9464abc6521264c3a3c95].[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.0000014 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 12 B |
| Node ID | 1 |
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 | 2 |
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 | 3 |