Skip to content

Instantly share code, notes, and snippets.

@DavidBoike

DavidBoike/MergePlan.sqlplan

Created September 14, 2011 17:46
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save DavidBoike/1217226 to your computer and use it in GitHub Desktop.
Save DavidBoike/1217226 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
MergePlan.sqlplan
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.1" Build="10.0.2531.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="6" StatementEstRows="1860310" StatementId="2" StatementOptmLevel="FULL" StatementSubTreeCost="1214.54" StatementText="MERGE TargetTable as T&#xD;&#xA;USING @tSource S&#xD;&#xA; on S.Key1 = T.Key1 and S.Key2 = T.Key2&#xD;&#xA; &#xD;&#xA;WHEN MATCHED AND T.Data1 &lt;&gt; S.Data1 OR T.Data2 &lt;&gt; S.Data2 OR T.Data3 &lt;&gt; S.Data3 THEN&#xD;&#xA; UPDATE SET&#xD;&#xA; T.Data1 = S.Data1,&#xD;&#xA; T.Data2 = S.Data2,&#xD;&#xA; T.Data3 = S.Data3&#xD;&#xA;&#xD;&#xA;WHEN NOT MATCHED BY TARGET THEN&#xD;&#xA; INSERT (Key1, Key2, Data1, Data2, Data3)&#xD;&#xA; VALUES (Key1, Key2, Data1, Data2, Data3)&#xD;&#xA;&#xD;&#xA;WHEN NOT MATCHED BY SOURCE AND T.Key1 = @id THEN&#xD;&#xA; DELETE&#xD;&#xA;&#xD;&#xA;" StatementType="MERGE" QueryHash="0x5A795F5D41E98C7F" QueryPlanHash="0x4BA93F8F131BCDD7">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" MemoryGrant="892256" CachedPlanSize="96" CompileTime="12" CompileCPU="12" CompileMemory="1040">
<RelOp AvgRowSize="9" EstimateCPU="3.34856" EstimateIO="137.207" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3348560" LogicalOp="Update" NodeId="1" Parallel="false" PhysicalOp="Index Update" EstimatedTotalSubtreeCost="1214.54">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update WithOrderedPrefetch="true" DMLRequestSort="true">
<Object Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Index="[IX_TargetTable]" Alias="[T]" IndexKind="NonClustered" />
<SetPredicate>
<ScalarOperator ScalarString="[Key11041] = [DatabaseName].[dbo].[TargetTable].[Key1] as [T].[Key1],[Key21042] = [DatabaseName].[dbo].[TargetTable].[Key2] as [T].[Key2],[Data11043] = [DatabaseName].[dbo].[TargetTable].[Data1] as [T].[Data1],[Data21044] = [DatabaseName].[dbo].[TargetTable].[Data2] as [T].[Data2],[Data31045] = [DatabaseName].[dbo].[TargetTable].[Data3] as [T].[Data3]">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Column="Key11041" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Column="Key21042" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Column="Data11043" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Column="Data21044" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Column="Data31045" />
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</Identifier>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<ActionColumn>
<ColumnReference Column="Action1007" />
</ActionColumn>
<RelOp AvgRowSize="178" EstimateCPU="89.5632" EstimateIO="966.903" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3348560" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="1073.98">
<OutputList>
<ColumnReference Column="Action1007" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
</OrderByColumn>
<OrderByColumn Ascending="true">
<ColumnReference Column="Action1007" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="178" EstimateCPU="1.41383" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3348560" LogicalOp="Filter" NodeId="4" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="17.5142">
<OutputList>
<ColumnReference Column="Action1007" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="179" EstimateCPU="2.79046" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="3720620" LogicalOp="Split" NodeId="5" Parallel="false" PhysicalOp="Split" EstimatedTotalSubtreeCost="16.1003">
<OutputList>
<ColumnReference Column="Action1007" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ColumnReference Column="Expr1038" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Split>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Action1007" />
</DefinedValue>
</DefinedValues>
<ActionColumn>
<ColumnReference Column="Action1007" />
</ActionColumn>
<RelOp AvgRowSize="345" EstimateCPU="1E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1860310" LogicalOp="Merge" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Merge" EstimatedTotalSubtreeCost="13.3099">
<OutputList>
<ColumnReference Column="Action1007" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ColumnReference Column="Data3_OLD" />
<ColumnReference Column="Key1_OLD" />
<ColumnReference Column="Key2_OLD" />
<ColumnReference Column="Data1_OLD" />
<ColumnReference Column="Data2_OLD" />
<ColumnReference Column="Expr1038" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update DMLRequestSort="false">
<Object Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Index="[PK_TargetTable]" Alias="[T]" IndexKind="Clustered" />
<SetPredicate SetPredicateType="Insert">
<ScalarOperator ScalarString="[DatabaseName].[dbo].[TargetTable].[Data3] as [T].[Data3] = RaiseIfNullUpdate([Expr1008]),[DatabaseName].[dbo].[TargetTable].[Key1] as [T].[Key1] = RaiseIfNullUpdate([Expr1009]),[DatabaseName].[dbo].[TargetTable].[Data2] as [T].[Data2] = [Expr1010],[DatabaseName].[dbo].[TargetTable].[Data1] as [T].[Data1] = RaiseIfNullUpdate([Expr1011]),[DatabaseName].[dbo].[TargetTable].[Key2] as [T].[Key2] = RaiseIfNullUpdate([Expr1012])">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1008" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1009" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1010" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1011" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1012" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<SetPredicate SetPredicateType="Update">
<ScalarOperator ScalarString="[DatabaseName].[dbo].[TargetTable].[Data3] as [T].[Data3] = RaiseIfNullUpdate([Expr1008]),[DatabaseName].[dbo].[TargetTable].[Data2] as [T].[Data2] = [Expr1010],[DatabaseName].[dbo].[TargetTable].[Data1] as [T].[Data1] = RaiseIfNullUpdate([Expr1011])">
<ScalarExpressionList>
<ScalarOperator>
<MultipleAssign>
<Assign>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1008" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1010" />
</Identifier>
</ScalarOperator>
</Assign>
<Assign>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ScalarOperator>
<Intrinsic FunctionName="RaiseIfNullUpdate">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1011" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Assign>
</MultipleAssign>
</ScalarOperator>
</ScalarExpressionList>
</ScalarOperator>
</SetPredicate>
<ActionColumn>
<ColumnReference Column="Action1007" />
</ActionColumn>
<RelOp AvgRowSize="200" EstimateCPU="0.186031" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1860310" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="13.2999">
<OutputList>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Column="Action1007" />
<ColumnReference Column="Expr1008" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1038" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Action1007" />
<ScalarOperator ScalarString="[Action1007]">
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1038" />
<ScalarOperator ScalarString="[Expr1038]">
<Identifier>
<ColumnReference Column="Expr1038" />
</Identifier>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="200" EstimateCPU="0.186031" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1860310" LogicalOp="Compute Scalar" NodeId="8" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="13.2999">
<OutputList>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Column="Action1007" />
<ColumnReference Column="Expr1008" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1038" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1038" />
<ScalarOperator ScalarString="CASE WHEN [Expr1018] AND [Expr1017] AND [Expr1015] THEN (1) ELSE (0) END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="AND">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1018" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1017" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1015" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="200" EstimateCPU="0.186031" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1860310" LogicalOp="Compute Scalar" NodeId="9" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="13.1138">
<OutputList>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Column="Action1007" />
<ColumnReference Column="Expr1008" />
<ColumnReference Column="Expr1009" />
<ColumnReference Column="Expr1010" />
<ColumnReference Column="Expr1011" />
<ColumnReference Column="Expr1012" />
<ColumnReference Column="Expr1015" />
<ColumnReference Column="Expr1017" />
<ColumnReference Column="Expr1018" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1008" />
<ScalarOperator ScalarString="CASE WHEN [Action1007]=(1) THEN @tSource.[Data3] as [S].[Data3] ELSE CASE WHEN [Action1007]=(4) THEN @tSource.[Data3] as [S].[Data3] ELSE [DatabaseName].[dbo].[TargetTable].[Data3] as [T].[Data3] END END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1009" />
<ScalarOperator ScalarString="CASE WHEN [Action1007]=(4) THEN @tSource.[Key1] as [S].[Key1] ELSE [DatabaseName].[dbo].[TargetTable].[Key1] as [T].[Key1] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1010" />
<ScalarOperator ScalarString="CASE WHEN [Action1007]=(1) THEN @tSource.[Data2] as [S].[Data2] ELSE CASE WHEN [Action1007]=(4) THEN @tSource.[Data2] as [S].[Data2] ELSE [DatabaseName].[dbo].[TargetTable].[Data2] as [T].[Data2] END END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1011" />
<ScalarOperator ScalarString="CASE WHEN [Action1007]=(1) THEN @tSource.[Data1] as [S].[Data1] ELSE CASE WHEN [Action1007]=(4) THEN @tSource.[Data1] as [S].[Data1] ELSE [DatabaseName].[dbo].[TargetTable].[Data1] as [T].[Data1] END END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1012" />
<ScalarOperator ScalarString="CASE WHEN [Action1007]=(4) THEN @tSource.[Key2] as [S].[Key2] ELSE [DatabaseName].[dbo].[TargetTable].[Key2] as [T].[Key2] END">
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1015" />
<ScalarOperator ScalarString="[Action1007]=(1) AND CASE WHEN [DatabaseName].[dbo].[TargetTable].[Data3] as [T].[Data3] = CASE WHEN [Action1007]=(1) THEN @tSource.[Data3] as [S].[Data3] ELSE CASE WHEN [Action1007]=(4) THEN @tSource.[Data3] as [S].[Data3] ELSE [DatabaseName].[dbo].[TargetTable].[Data3] as [T].[Data3] END END THEN (1) ELSE (0) END">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Logical>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1017" />
<ScalarOperator ScalarString="[Action1007]=(1) AND CASE WHEN [DatabaseName].[dbo].[TargetTable].[Data2] as [T].[Data2] = CASE WHEN [Action1007]=(1) THEN @tSource.[Data2] as [S].[Data2] ELSE CASE WHEN [Action1007]=(4) THEN @tSource.[Data2] as [S].[Data2] ELSE [DatabaseName].[dbo].[TargetTable].[Data2] as [T].[Data2] END END THEN (1) ELSE (0) END">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Logical>
</ScalarOperator>
</DefinedValue>
<DefinedValue>
<ColumnReference Column="Expr1018" />
<ScalarOperator ScalarString="[Action1007]=(1) AND CASE WHEN [DatabaseName].[dbo].[TargetTable].[Data1] as [T].[Data1] = CASE WHEN [Action1007]=(1) THEN @tSource.[Data1] as [S].[Data1] ELSE CASE WHEN [Action1007]=(4) THEN @tSource.[Data1] as [S].[Data1] ELSE [DatabaseName].[dbo].[TargetTable].[Data1] as [T].[Data1] END END THEN (1) ELSE (0) END">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="BINARY IS">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
</Identifier>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Logical>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="220" EstimateCPU="0.186031" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1860310" LogicalOp="Top" NodeId="10" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="12.9278">
<OutputList>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ColumnReference Column="Action1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="220" EstimateCPU="0.917626" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1860310" LogicalOp="Filter" NodeId="11" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="12.7418">
<OutputList>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ColumnReference Column="Action1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="0" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="220" EstimateCPU="0.191172" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1911720" LogicalOp="Compute Scalar" NodeId="12" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="11.8241">
<OutputList>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ColumnReference Column="Action1007" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1911721" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Action1007" />
<ScalarOperator ScalarString="ForceOrder(CASE WHEN [SrcPrb1002] IS NOT NULL THEN CASE WHEN [TrgPrb1005] IS NOT NULL THEN CASE WHEN [DatabaseName].[dbo].[TargetTable].[Data1] as [T].[Data1]&lt;&gt;@tSource.[Data1] as [S].[Data1] OR [DatabaseName].[dbo].[TargetTable].[Data2] as [T].[Data2]&lt;&gt;@tSource.[Data2] as [S].[Data2] OR [DatabaseName].[dbo].[TargetTable].[Data3] as [T].[Data3]&lt;&gt;@tSource.[Data3] as [S].[Data3] THEN (1) ELSE NULL END ELSE (4) END ELSE CASE WHEN [DatabaseName].[dbo].[TargetTable].[Key1] as [T].[Key1]=[@id] THEN (3) ELSE NULL END END)">
<Intrinsic FunctionName="ForceOrder">
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="SrcPrb1002" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="TrgPrb1005" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="NE">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(1)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="(4)" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<IF>
<Condition>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@id" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(3)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="224" EstimateCPU="0.196776" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1911720" LogicalOp="Full Outer Join" NodeId="13" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="11.633">
<OutputList>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
<ColumnReference Column="SrcPrb1002" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ColumnReference Column="TrgPrb1005" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1911721" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="@tSource.[Key1] as [S].[Key1]=[DatabaseName].[dbo].[TargetTable].[Key1] as [T].[Key1] AND @tSource.[Key2] as [S].[Key2]=[DatabaseName].[dbo].[TargetTable].[Key2] as [T].[Key2]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="178" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="14" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0032832">
<OutputList>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
<ColumnReference Column="SrcPrb1002" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="SrcPrb1002" />
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="174" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Scan" NodeId="15" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0">
<OutputList>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Table="@tSource" Alias="[S]" Column="Key2" />
</DefinedValue>
<DefinedValue>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data2" />
</DefinedValue>
<DefinedValue>
<ColumnReference Table="@tSource" Alias="[S]" Column="Data3" />
</DefinedValue>
</DefinedValues>
<Object Table="[@tSource]" Index="[PK__#7C27BB5__DE7D18AC7E1003C9]" Alias="[S]" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
<RelOp AvgRowSize="54" EstimateCPU="0.191172" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1911720" LogicalOp="Compute Scalar" NodeId="19" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="11.4329">
<OutputList>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
<ColumnReference Column="TrgPrb1005" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="TrgPrb1005" />
<ScalarOperator ScalarString="(1)">
<Const ConstValue="(1)" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="50" EstimateCPU="2.10305" EstimateIO="9.13868" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1911720" LogicalOp="Clustered Index Scan" NodeId="20" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="11.2417" TableCardinality="1911720">
<OutputList>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1911721" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Key2" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data1" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data2" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Alias="[T]" Column="Data3" />
</DefinedValue>
</DefinedValues>
<Object Database="[DatabaseName]" Schema="[dbo]" Table="[TargetTable]" Index="[PK_TargetTable]" Alias="[T]" IndexKind="Clustered" />
</IndexScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Merge>
</RelOp>
</ComputeScalar>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="[Action1007] IS NOT NULL">
<Compare CompareOp="IS NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Action1007" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Top>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</ComputeScalar>
</RelOp>
</Update>
</RelOp>
</Split>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="NOT [Expr1038]">
<Logical Operation="NOT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1038" />
</Identifier>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Sort>
</RelOp>
</Update>
</RelOp>
<ParameterList>
<ColumnReference Column="@id" ParameterCompiledValue="{guid'ACD11F47-8162-4EC6-B80B-9D8CC0B0B233'}" ParameterRuntimeValue="{guid'ACD11F47-8162-4EC6-B80B-9D8CC0B0B233'}" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment