SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.ElementBase
(
    id integer NOT NULL,
    parent_id integer NOT NULL,
    element_type char(1) NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.ElementBase id]
        PRIMARY KEY CLUSTERED (id),

    -- fk target
    CONSTRAINT [UQ dbo.ElementBase id, element_type]
        UNIQUE NONCLUSTERED (id, element_type),
 
    -- self fk
    CONSTRAINT [FK dbo.ElementBase parent_id id]
        FOREIGN KEY (parent_id)
        REFERENCES dbo.ElementBase (id),

    -- valid element types
    CONSTRAINT [CK dbo.ElementBase element_type]
        CHECK (element_type IN ('a', 'b', 'c')),

    -- for maintenance
    INDEX [IX dbo.ElementBase parent_id] 
        NONCLUSTERED (parent_id)
);
CREATE TABLE dbo.BData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'b') PERSISTED,

    -- id key
    CONSTRAINT [PK dbo.BData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Bdata ElementBase id, element_type]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE CASCADE
);
CREATE TABLE dbo.AData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'a') PERSISTED,
    b_element integer NULL,

    -- id key
    CONSTRAINT [PK dbo.AData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Adata ElementBase id, element_type]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE NO ACTION,

    -- fk to BData
    CONSTRAINT [FK dbo.AData dbo.BData id b_element]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE SET NULL,

    -- fk lookup
    INDEX [IDX dbo.AData b_element] 
        NONCLUSTERED (b_element),
);
CREATE TABLE dbo.CData
(
    id integer NOT NULL,
    element_type AS CONVERT(char(1), 'c') PERSISTED,
    b_element integer NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.CData id]
        PRIMARY KEY CLUSTERED (id),

    -- fk to ElementBase
    CONSTRAINT [FK Cdata ElementBase]
        FOREIGN KEY (id, element_type)
        REFERENCES dbo.ElementBase (id, element_type)
        ON DELETE NO ACTION,

    -- fk to BData
    CONSTRAINT [FK dbo.CData dbo.BData b_element id]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE CASCADE,

    -- fk lookup
    INDEX [IDX dbo.CData b_element] 
        NONCLUSTERED (b_element),
);
CREATE TABLE dbo.S
(
    s_id integer NOT NULL,
    b_element integer NOT NULL,

    -- id key
    CONSTRAINT [PK dbo.S s_id]
        PRIMARY KEY CLUSTERED (s_id),

    -- fk to BData
    CONSTRAINT [FK dbo.S dbo.BData b_element id]
        FOREIGN KEY (b_element)
        REFERENCES dbo.BData (id)
        ON DELETE CASCADE,

    -- fk lookup
    INDEX [IDX dbo.S b_element] 
        NONCLUSTERED (b_element),
);
CREATE OR ALTER TRIGGER [dbo.ElementBase IOD Cascade]
ON dbo.ElementBase
INSTEAD OF DELETE AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT * FROM Deleted) RETURN;

    CREATE TABLE #ToDelete 
    (
        id integer PRIMARY KEY,
        element_type varchar(1) NOT NULL
    );

    -- Find all related ElementBase records
    WITH R AS
    (
        -- Anchor: parent ElementBase rows
        SELECT D.id, D.element_type
        FROM Deleted AS D

        UNION ALL

        -- Recursive: children
        SELECT EB.id, EB.element_type
        FROM R
        JOIN dbo.ElementBase AS EB
            ON EB.parent_id = R.id
            AND EB.id <> R.id
    )
    INSERT #ToDelete
        (id, element_type)
    SELECT DISTINCT 
        R.id,
        R.element_type
    FROM R
    OPTION (MAXRECURSION 0);

    -- Delete related CData records
    DELETE CD
    FROM #ToDelete AS TD
    JOIN dbo.CData AS CD
        ON CD.id = TD.id
    WHERE
        TD.element_type = 'c';

    -- Delete related AData records
    DELETE AD
    FROM #ToDelete AS TD
    JOIN dbo.AData AS AD
        ON AD.id = TD.id
    WHERE
        TD.element_type = 'a';

    -- Delete ElementBase (and BData, S records via cascade)
    DELETE EB
    FROM #ToDelete AS TD
    JOIN dbo.ElementBase AS EB
        ON EB.id = TD.id;
END;
INSERT dbo.ElementBase (id, parent_id, element_type) VALUES (1, 1, 'b');
INSERT dbo.BData (id) VALUES (1);
INSERT dbo.ElementBase (id, parent_id, element_type) VALUES (2, 1, 'a');
INSERT dbo.AData (id, b_element) VALUES (2, 1);
INSERT dbo.ElementBase (id, parent_id, element_type) VALUES (3, 1, 'c');
INSERT dbo.CData (id, b_element) VALUES (3, 1);
INSERT dbo.S (s_id, b_element) VALUES (1, 1), (2, 1), (3, 1);
9 rows affected

SELECT * FROM dbo.ElementBase AS EB;
SELECT * FROM dbo.AData AS AD;
SELECT * FROM dbo.BData AS BD;
SELECT * FROM dbo.CData AS CD;
SELECT * FROM dbo.S AS S;


id
parent_id
element_type


1
1
b

2
1
a

3
1
c




id
element_type
b_element


2
a
1




id
element_type


1
b




id
element_type
b_element


3
c
1




s_id
b_element


1
1

2
1

3
1


SET STATISTICS XML ON;

DELETE dbo.ElementBase WHERE id = 1;

SET STATISTICS XML OFF;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="DELETE [dbo].[ElementBase]  WHERE [id]=@1" StatementId="1" StatementCompId="2" StatementType="DELETE" RetrievedFromCache="true" StatementSubTreeCost="0.0132842" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xDAF8C333357D7EAA" QueryPlanHash="0x10F014808CF9842F" CardinalityEstimationModelVersion="140"><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="160"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2578264"></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="Clustered Index Insert" LogicalOp="Insert" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0132842" 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="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[STREAM].[Act] = [Act1007],[STREAM].[id] = [fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[id],[STREAM].[parent_id] = [fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[parent_id],[STREAM].[element_type] = [fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[element_type]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[STREAM]" Column="Act"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Act1007"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[STREAM]" Column="id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[STREAM]" Column="parent_id"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="parent_id"></ColumnReference></Identifier></ScalarOperator></Assign><Assign><ColumnReference Table="[STREAM]" Column="element_type"></ColumnReference><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="element_type"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="parent_id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="element_type"></ColumnReference><ColumnReference Column="Act1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Act1007"></ColumnReference><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="parent_id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="element_type"></ColumnReference></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" ActualRowsRead="1" 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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="parent_id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="element_type"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Index="[PK dbo.ElementBase id]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[@1],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="@1"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></Update></RelOp><ParameterList><ColumnReference Column="@1" ParameterDataType="tinyint" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="IF NOT EXISTS (SELECT * FROM Deleted)" StatementId="2" StatementCompId="7" StatementType="COND WITH QUERY" RetrievedFromCache="true" StatementSubTreeCost="0.00328854" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x5D5A2A43236CDC85" QueryPlanHash="0x530C724180D60C32" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><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="256"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2578264"></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="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00328854" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [Expr1005] IS NULL THEN (1) ELSE (0) END"><IF><Condition><ScalarOperator><Compare CompareOp="IS"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00328844" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference></DefinedValue></DefinedValues><ProbeColumn><ColumnReference Column="Expr1005"></ColumnReference></ProbeColumn><RelOp NodeId="2" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="1.157e-006" 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><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="1" 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="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Table="[Deleted]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Table="[Deleted]" Column="Act"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH R AS&#xd;&#xa;    (&#xd;&#xa;        -- Anchor: parent ElementBase rows&#xd;&#xa;        SELECT D.id, D.element_type&#xd;&#xa;        FROM Deleted AS D&#xd;&#xa;&#xd;&#xa;        UNION ALL&#xd;&#xa;&#xd;&#xa;        -- Recursive: children&#xd;&#xa;        SELECT EB.id, EB.element_type&#xd;&#xa;        FROM R&#xd;&#xa;        JOIN dbo.ElementBase AS EB&#xd;&#xa;            ON EB.parent_id = R.id&#xd;&#xa;            AND EB.id &lt;&gt; R.id&#xd;&#xa;    )&#xd;&#xa;    INSERT #ToDelete&#xd;&#xa;        (id, element_type)&#xd;&#xa;    SELECT DISTINCT &#xd;&#xa;        R.id,&#xd;&#xa;        R.element_type&#xd;&#xa;    FROM R&#xd;&#xa;    OPTION (MAXRECURSION 0)" StatementId="3" StatementCompId="11" StatementType="INSERT" RetrievedFromCache="true" StatementSubTreeCost="0.0287521" StatementEstRows="5.23939" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x08066232275C21E0" QueryPlanHash="0x499BB76CFF108962" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><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="40" CompileTime="0" CompileCPU="0" CompileMemory="416"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="617168"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2578264"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[IX dbo.ElementBase parent_id]" Table="[ElementBase]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[PK dbo.ElementBase id]" Table="[ElementBase]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo></OptimizerStatsUsage><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="5.23939" EstimateIO="0.01" EstimateCPU="5.23939e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0287521" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[tempdb]" Schema="[dbo]" Table="[#ToDelete]" Index="[PK__#ToDelet__3213E83F43B31AC5]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[#ToDelete].[id] = RaiseIfNullInsert([Recr1007]),[#ToDelete].[element_type] = RaiseIfNullInsert([Expr1009])"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="[#ToDelete]" Column="id"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="Recr1007"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Table="[#ToDelete]" Column="element_type"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="5.23939" EstimateIO="0" EstimateCPU="5.23939e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0187468" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Recr1007"></ColumnReference><ColumnReference Column="Expr1009"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(1),[Recr1008],0)"><Convert DataType="varchar" Length="1" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Recr1008"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="5.23939" EstimateIO="0.0112613" EstimateCPU="0.000145239" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0187463" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Recr1007"></ColumnReference><ColumnReference Column="Recr1008"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" 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="16"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Recr1007"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Recr1008"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Index Spool" LogicalOp="Lazy Spool" EstimateRows="9.1" EstimateIO="0" EstimateCPU="1.85e-008" AvgRowSize="12" EstimatedTotalSubtreeCost="0.00733981" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1013"></ColumnReference><ColumnReference Column="Recr1007"></ColumnReference><ColumnReference Column="Recr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool Stack="1"><RelOp NodeId="4" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="9.1" EstimateIO="0" EstimateCPU="3.7e-009" AvgRowSize="12" EstimatedTotalSubtreeCost="0.00725536" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1013"></ColumnReference><ColumnReference Column="Recr1007"></ColumnReference><ColumnReference Column="Recr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Concat><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"></ColumnReference><ColumnReference Column="Expr1010"></ColumnReference><ColumnReference Column="Expr1012"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Recr1007"></ColumnReference><ColumnReference Table="Deleted" Alias="[D]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Recr1008"></ColumnReference><ColumnReference Table="Deleted" Alias="[D]" Column="element_type"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="3.7e-008" AvgRowSize="12" EstimatedTotalSubtreeCost="3.7e-008" Parallel="0" EstimateRebinds="9.1" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1010"></ColumnReference><ColumnReference Table="Deleted" Alias="[D]" Column="id"></ColumnReference><ColumnReference Table="Deleted" Alias="[D]" Column="element_type"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1010"></ColumnReference><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="Deleted" Alias="[D]" Column="id"></ColumnReference><ColumnReference Table="Deleted" Alias="[D]" Column="element_type"></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" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="Deleted" Alias="[D]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="Deleted" Alias="[D]" Column="element_type"></ColumnReference></DefinedValue></DefinedValues><Object Table="[Deleted]" Alias="[D]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Table="Deleted" Alias="[D]" Column="Act"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="7" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="9.1" EstimateIO="0" EstimateCPU="3.108e-007" AvgRowSize="12" EstimatedTotalSubtreeCost="0.00397225" Parallel="0" EstimateRebinds="9.1" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1012"></ColumnReference><ColumnReference Column="Recr1004"></ColumnReference><ColumnReference Column="Recr1005"></ColumnReference></OuterReferences><RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="3.7e-008" AvgRowSize="12" EstimatedTotalSubtreeCost="3.7e-008" Parallel="0" EstimateRebinds="9.1" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1012"></ColumnReference><ColumnReference Column="Recr1004"></ColumnReference><ColumnReference Column="Recr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1012"></ColumnReference><ScalarOperator ScalarString="[Expr1011]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1011"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="9" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="3.7e-008" AvgRowSize="12" EstimatedTotalSubtreeCost="3.7e-008" Parallel="0" EstimateRebinds="9.1" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1011"></ColumnReference><ColumnReference Column="Recr1004"></ColumnReference><ColumnReference Column="Recr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" 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="3" Stack="1"></Spool></RelOp></ComputeScalar></RelOp><RelOp NodeId="10" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="2.7" EstimatedRowsRead="3" EstimateIO="0.0032035" EstimateCPU="8.18e-005" AvgRowSize="16" EstimatedTotalSubtreeCost="0.00394788" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="8.1" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="7" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="9" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Index="[PK dbo.ElementBase id]" Alias="[EB]" IndexKind="Clustered" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[parent_id] as [EB].[parent_id]=[Recr1004] AND [fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[id] as [EB].[id]&lt;&gt;[Recr1004]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="parent_id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Recr1004"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="NE"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Recr1004"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Concat></RelOp></Spool></RelOp></Sort></RelOp></ComputeScalar></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="DELETE CD&#xd;&#xa;    FROM #ToDelete AS TD&#xd;&#xa;    JOIN dbo.CData AS CD&#xd;&#xa;        ON CD.id = TD.id&#xd;&#xa;    WHERE&#xd;&#xa;        TD.element_type = &apos;c&apos;" StatementId="4" StatementCompId="12" StatementType="DELETE" RetrievedFromCache="true" StatementSubTreeCost="0.0265729" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xD5ECA7C384C6FDA3" QueryPlanHash="0x1C3DFB53BCF6D623" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><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="15" CompileCPU="15" CompileMemory="240"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2578264"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[PK dbo.CData id]" Table="[CData]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_AEBAEF12]" Table="[#ToDelete___________________________________________________________________________________________________________000000000004]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[PK__#ToDelet__3213E83F43B31AC5]" Table="[#ToDelete___________________________________________________________________________________________________________000000000004]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo></OptimizerStatsUsage><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 Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.02" EstimateCPU="2e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0265729" 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" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Index="[PK dbo.CData id]" Alias="[CD]" IndexKind="Clustered" Storage="RowStore"></Object><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Index="[IDX dbo.CData b_element]" Alias="[CD]" IndexKind="NonClustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00657086" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Alias="[CD]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Alias="[CD]" Column="id"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Alias="[CD]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" 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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Alias="[CD]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Index="[IDX dbo.CData b_element]" Alias="[CD]" IndexKind="NonClustered" 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="12" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="3" 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" ActualRowsRead="1" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#ToDelete]" Index="[PK__#ToDelet__3213E83F43B31AC5]" Alias="[TD]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Table="#ToDelete" Alias="[TD]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[CData].[id] as [CD].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Alias="[CD]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="#ToDelete.[element_type] as [TD].[element_type]=&apos;c&apos;"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#ToDelete" Alias="[TD]" Column="element_type"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;c&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="DELETE AD&#xd;&#xa;    FROM #ToDelete AS TD&#xd;&#xa;    JOIN dbo.AData AS AD&#xd;&#xa;        ON AD.id = TD.id&#xd;&#xa;    WHERE&#xd;&#xa;        TD.element_type = &apos;a&apos;" StatementId="5" StatementCompId="13" StatementType="DELETE" RetrievedFromCache="true" StatementSubTreeCost="0.0265729" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xA37B3D316E0A0EDF" QueryPlanHash="0xC844179335F42AD6" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><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="240"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2578264"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_AEBAEF12]" Table="[#ToDelete___________________________________________________________________________________________________________000000000004]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[PK__#ToDelet__3213E83F43B31AC5]" Table="[#ToDelete___________________________________________________________________________________________________________000000000004]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.60" ModificationCount="0" SamplingPercent="100" Statistics="[PK dbo.AData id]" Table="[AData]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo></OptimizerStatsUsage><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 Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.02" EstimateCPU="2e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0265729" 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" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Index="[PK dbo.AData id]" Alias="[AD]" IndexKind="Clustered" Storage="RowStore"></Object><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Index="[IDX dbo.AData b_element]" Alias="[AD]" IndexKind="NonClustered" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00657086" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Alias="[AD]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Alias="[AD]" Column="id"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Alias="[AD]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="4" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" 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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Alias="[AD]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Index="[IDX dbo.AData b_element]" Alias="[AD]" IndexKind="NonClustered" 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="12" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="3" 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" ActualRowsRead="1" ActualEndOfScans="0" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#ToDelete]" Index="[PK__#ToDelet__3213E83F43B31AC5]" Alias="[TD]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Table="#ToDelete" Alias="[TD]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[AData].[id] as [AD].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Alias="[AD]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="#ToDelete.[element_type] as [TD].[element_type]=&apos;a&apos;"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Table="#ToDelete" Alias="[TD]" Column="element_type"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;a&apos;"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="DELETE EB&#xd;&#xa;    FROM #ToDelete AS TD&#xd;&#xa;    JOIN dbo.ElementBase AS EB&#xd;&#xa;        ON EB.id = TD.id" StatementId="6" StatementCompId="14" StatementType="DELETE" RetrievedFromCache="true" StatementSubTreeCost="0.145184" StatementEstRows="3" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xF31D52D949E625B3" QueryPlanHash="0x82A5D6439F736844" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><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="120" CompileTime="15" CompileCPU="15" CompileMemory="720"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2578264"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-07-08T20:18:14.60" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_34C8D9D1]" Table="[ElementBase]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.60" ModificationCount="0" SamplingPercent="100" Statistics="[PK dbo.BData id]" Table="[BData]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.60" ModificationCount="0" SamplingPercent="100" Statistics="[UQ dbo.ElementBase id, element_type]" Table="[ElementBase]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[IX dbo.ElementBase parent_id]" Table="[ElementBase]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[PK__#ToDelet__3213E83F43B31AC5]" Table="[#ToDelete___________________________________________________________________________________________________________000000000004]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.58" ModificationCount="0" SamplingPercent="100" Statistics="[PK dbo.ElementBase id]" Table="[ElementBase]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.60" ModificationCount="0" SamplingPercent="100" Statistics="[IDX dbo.S b_element]" Table="[S]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-07-08T20:18:14.60" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_398D8EEE]" Table="[BData]" Schema="[dbo]" Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]"></StatisticsInfo></OptimizerStatsUsage><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="3" EstimateIO="0" EstimateCPU="1.2e-005" AvgRowSize="9" EstimatedTotalSubtreeCost="0.145184" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" 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="3" EstimateIO="0" EstimateCPU="3.36146e-005" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0124023" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="9" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="3" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="3" EstimateIO="0.03" EstimateCPU="9e-006" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0369061" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Index="[PK dbo.ElementBase id]" Alias="[EB]" IndexKind="Clustered" Storage="RowStore"></Object><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Index="[IX dbo.ElementBase parent_id]" Alias="[EB]" IndexKind="NonClustered" Storage="RowStore"></Object><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Index="[UQ dbo.ElementBase id, element_type]" Alias="[EB]" IndexKind="NonClustered" Storage="RowStore"></Object><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="3" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00689714" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="#ToDelete" Alias="[TD]" Column="id"></ColumnReference></OuterReferences><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#ToDelete" Alias="[TD]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="#ToDelete" Alias="[TD]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#ToDelete]" Index="[PK__#ToDelet__3213E83F43B31AC5]" Alias="[TD]" Storage="RowStore"></Object></IndexScan></RelOp><RelOp NodeId="6" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0035993" TableCardinality="3" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="3" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="0" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Index="[UQ dbo.ElementBase id, element_type]" Alias="[EB]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="#ToDelete.[id] as [TD].[id]"><Identifier><ColumnReference Table="#ToDelete" Alias="[TD]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp></Spool></RelOp><RelOp NodeId="8" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.53127e-005" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00660426" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" 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="9" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-006" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0260166" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" 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" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Index="[PK dbo.BData id]" IndexKind="Clustered" Storage="RowStore"></Object><RelOp NodeId="10" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0160156" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OuterReferences><RelOp NodeId="11" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="3" EstimateIO="0" EstimateCPU="3.36146e-005" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0124023" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" 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="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0035993" TableCardinality="1" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="2" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Index="[PK dbo.BData id]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[id] as [EB].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[BData].[element_type]=[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[element_type] as [EB].[element_type]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="element_type" ComputedColumn="1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp></Spool></RelOp><RelOp NodeId="13" PhysicalOp="Clustered Index Update" LogicalOp="Update" EstimateRows="1" EstimateIO="0.02" EstimateCPU="2e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0431191" 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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Index="[PK dbo.AData id]" IndexKind="Clustered" Storage="RowStore"></Object><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Index="[IDX dbo.AData b_element]" IndexKind="NonClustered" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[AData].[b_element] = [NullOrDef1021]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="b_element"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="NullOrDef1021"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="14" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0231171" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="id"></ColumnReference><ColumnReference Column="NullOrDef1021"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="NullOrDef1021"></ColumnReference><ScalarOperator ScalarString="NULL"><Const ConstValue="NULL"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="15" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0.013125" EstimateCPU="0.00010046" AvgRowSize="11" EstimatedTotalSubtreeCost="0.023117" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="id"></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="16" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00989154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="id"></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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></OuterReferences><RelOp NodeId="18" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.53127e-005" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00660426" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" 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="8"></Spool></RelOp><RelOp NodeId="19" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" 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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Index="[IDX dbo.AData b_element]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="b_element"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[id] as [EB].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Spool></RelOp></ComputeScalar></RelOp></Update></RelOp><RelOp NodeId="20" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.02" EstimateCPU="2e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0298935" 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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Index="[PK dbo.CData id]" IndexKind="Clustered" Storage="RowStore"></Object><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Index="[IDX dbo.CData b_element]" IndexKind="NonClustered" Storage="RowStore"></Object><RelOp NodeId="21" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00989154" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Column="id"></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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference></OuterReferences><RelOp NodeId="23" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.53127e-005" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00660426" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" 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="8"></Spool></RelOp><RelOp NodeId="24" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Column="id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" 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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Column="id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Index="[IDX dbo.CData b_element]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Column="b_element"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[BData].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp><RelOp NodeId="25" PhysicalOp="Clustered Index Delete" LogicalOp="Delete" EstimateRows="3" EstimateIO="0.02" EstimateCPU="6e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0299081" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[S]" Index="[PK dbo.S s_id]" IndexKind="Clustered" Storage="RowStore"></Object><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[S]" Index="[IDX dbo.S b_element]" IndexKind="NonClustered" Storage="RowStore"></Object><RelOp NodeId="26" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="3" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0099021" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[S]" Column="s_id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference></OuterReferences><RelOp NodeId="28" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="1" EstimateIO="0" EstimateCPU="2.53127e-005" AvgRowSize="19" EstimatedTotalSubtreeCost="0.00660426" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" 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="8"></Spool></RelOp><RelOp NodeId="29" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[S]" Column="s_id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="8" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" 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_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[S]" Column="s_id"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[S]" Index="[IDX dbo.S b_element]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[S]" Column="b_element"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[BData].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[BData]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></Update></RelOp><RelOp NodeId="30" PhysicalOp="Assert" LogicalOp="Assert" EstimateRows="3" EstimateIO="0" EstimateCPU="3.54e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0232443" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Assert StartupExpression="0"><RelOp NodeId="31" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="3" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0232407" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1026"></ColumnReference><ColumnReference Column="Expr1027"></ColumnReference><ColumnReference Column="Expr1028"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><DefinedValues><DefinedValue><ColumnReference Column="Expr1028"></ColumnReference></DefinedValue></DefinedValues><OuterReferences><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OuterReferences><ProbeColumn><ColumnReference Column="Expr1028"></ColumnReference></ProbeColumn><RelOp NodeId="32" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="3" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="13" EstimatedTotalSubtreeCost="0.0196275" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference><ColumnReference Column="Expr1026"></ColumnReference><ColumnReference Column="Expr1027"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><DefinedValues><DefinedValue><ColumnReference Column="Expr1027"></ColumnReference></DefinedValue></DefinedValues><OuterReferences><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OuterReferences><ProbeColumn><ColumnReference Column="Expr1027"></ColumnReference></ProbeColumn><RelOp NodeId="33" PhysicalOp="Nested Loops" LogicalOp="Left Semi Join" EstimateRows="3" EstimateIO="0" EstimateCPU="1.254e-005" AvgRowSize="13" EstimatedTotalSubtreeCost="0.0160142" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference><ColumnReference Column="Expr1026"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><DefinedValues><DefinedValue><ColumnReference Column="Expr1026"></ColumnReference></DefinedValue></DefinedValues><OuterReferences><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></OuterReferences><ProbeColumn><ColumnReference Column="Expr1026"></ColumnReference></ProbeColumn><RelOp NodeId="34" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="3" EstimateIO="0" EstimateCPU="3.36146e-005" AvgRowSize="12" EstimatedTotalSubtreeCost="0.0124023" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" 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="35" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1" EstimateRowsWithoutRowGoal="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0035993" TableCardinality="3" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="3" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Index="[IX dbo.ElementBase parent_id]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Column="parent_id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[id] as [EB].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="36" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0035993" TableCardinality="0" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Index="[PK dbo.AData id]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[id] as [EB].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[AData].[element_type]=[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[element_type] as [EB].[element_type]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[AData]" Column="element_type" ComputedColumn="1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><RelOp NodeId="37" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0035993" TableCardinality="0" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="1" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Index="[PK dbo.CData id]" IndexKind="Clustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Column="id"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[id] as [EB].[id]"><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="id"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[CData].[element_type]=[fiddle_8def02225a6c46b4b5d50a57a7908a04].[dbo].[ElementBase].[element_type] as [EB].[element_type]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[CData]" Column="element_type" ComputedColumn="1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_8def02225a6c46b4b5d50a57a7908a04]" Schema="[dbo]" Table="[ElementBase]" Alias="[EB]" Column="element_type"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></IndexScan></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="CASE WHEN NOT [Expr1026] IS NULL THEN (0) ELSE CASE WHEN NOT [Expr1027] IS NULL THEN (1) ELSE CASE WHEN NOT [Expr1028] IS NULL THEN (2) ELSE NULL END END END"><IF><Condition><ScalarOperator><Logical Operation="NOT"><ScalarOperator><Logical Operation="IS NULL"><ScalarOperator><Identifier><ColumnReference Column="Expr1026"></ColumnReference></Identifier></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Logical Operation="NOT"><ScalarOperator><Logical Operation="IS NULL"><ScalarOperator><Identifier><ColumnReference Column="Expr1027"></ColumnReference></Identifier></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Logical Operation="NOT"><ScalarOperator><Logical Operation="IS NULL"><ScalarOperator><Identifier><ColumnReference Column="Expr1028"></ColumnReference></Identifier></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></Predicate></Assert></RelOp></Sequence></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


SELECT * FROM dbo.ElementBase AS EB;
SELECT * FROM dbo.AData AS AD;
SELECT * FROM dbo.BData AS BD;
SELECT * FROM dbo.CData AS CD;
SELECT * FROM dbo.S AS S;


id
parent_id
element_type




id
element_type
b_element




id
element_type




id
element_type
b_element




s_id
b_element