By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
3 rows affected
| ID | CODE | M_U | M_C | LINE |
|---|---|---|---|---|
| 123 | 1 | ABC | XYZ | 1 |
| 456 | G4762 | D01 | 398 | 1 |
| 456 | G4765 | E043 | null | 2 |
| 893 | H7832 | U73 | R12 | 1 |
| 893 | H9833 | null | F82 | 2 |
| 893 | H563 | U83 | null | 3 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT 
 ID, CODE, M_U, M_C , 1 as 'LINE' FROM #test WHERE COALESCE(CODE,M_U, M_C) IS NOT NULL
UNION ALL
 SELECT 
 ID, CODE1, M1_U, M1_C , 2 as 'LINE' FROM #test WHERE COALESCE(CODE1,M1_U,M1_C) IS NOT NULL
 UNION ALL
 SELECT 
 ID, CODE2, M2_U, M2_C , 3 as 'LINE' FROM #test WHERE COALESCE(CODE2, M2_U, M2_C) IS NOT NULL
 ORDER BY ID,Line" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0212549" StatementEstRows="7.06667" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xAA028DD377E68ECD" QueryPlanHash="0xAEDD7F3F89A05808" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="15" CompileCPU="15" CompileMemory="512"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="978968" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2584672"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.38" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_0000000A_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000006_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.38" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_0000000B_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000008_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.38" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_0000000C_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000007_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000004_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" 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="Sort" LogicalOp="Sort" EstimateRows="7.06667" EstimateIO="0.0112613" EstimateCPU="0.000131128" AvgRowSize="29" EstimatedTotalSubtreeCost="0.0212549" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1012"></ColumnReference><ColumnReference Column="Union1013"></ColumnReference><ColumnReference Column="Union1014"></ColumnReference><ColumnReference Column="Union1015"></ColumnReference><ColumnReference Column="Union1016"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" 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="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Union1012"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Union1016"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="7.06667" EstimateIO="0" EstimateCPU="7.06667e-07" AvgRowSize="29" EstimatedTotalSubtreeCost="0.00986253" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1012"></ColumnReference><ColumnReference Column="Union1013"></ColumnReference><ColumnReference Column="Union1014"></ColumnReference><ColumnReference Column="Union1015"></ColumnReference><ColumnReference Column="Union1016"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1012"></ColumnReference><ColumnReference Table="[#test]" Column="ID"></ColumnReference><ColumnReference Table="[#test]" Column="ID"></ColumnReference><ColumnReference Table="[#test]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1013"></ColumnReference><ColumnReference Table="[#test]" Column="CODE"></ColumnReference><ColumnReference Table="[#test]" Column="CODE1"></ColumnReference><ColumnReference Table="[#test]" Column="CODE2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1014"></ColumnReference><ColumnReference Table="[#test]" Column="M_U"></ColumnReference><ColumnReference Table="[#test]" Column="M1_U"></ColumnReference><ColumnReference Table="[#test]" Column="M2_U"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1015"></ColumnReference><ColumnReference Table="[#test]" Column="M_C"></ColumnReference><ColumnReference Table="[#test]" Column="M1_C"></ColumnReference><ColumnReference Table="[#test]" Column="M2_C"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1016"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference><ColumnReference Column="Expr1011"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="3" EstimateIO="0" EstimateCPU="3e-07" AvgRowSize="32" EstimatedTotalSubtreeCost="0.00328734" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#test]" Column="ID"></ColumnReference><ColumnReference Table="[#test]" Column="CODE"></ColumnReference><ColumnReference Table="[#test]" Column="M_U"></ColumnReference><ColumnReference Table="[#test]" Column="M_C"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="28" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#test]" Column="ID"></ColumnReference><ColumnReference Table="[#test]" Column="CODE"></ColumnReference><ColumnReference Table="[#test]" Column="M_U"></ColumnReference><ColumnReference Table="[#test]" Column="M_C"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="[#test]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="CODE"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="M_U"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="M_C"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#test]" TableReferenceId="1" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="CASE WHEN [#test].[CODE] IS NOT NULL THEN [#test].[CODE] ELSE CASE WHEN [#test].[M_U] IS NOT NULL THEN [#test].[M_U] ELSE [#test].[M_C] END END IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="CODE"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="CODE"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M_U"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M_U"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M_C"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2.4" EstimateIO="0" EstimateCPU="2.4e-07" AvgRowSize="28" EstimatedTotalSubtreeCost="0.00328728" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#test]" Column="ID"></ColumnReference><ColumnReference Table="[#test]" Column="CODE1"></ColumnReference><ColumnReference Table="[#test]" Column="M1_U"></ColumnReference><ColumnReference Table="[#test]" Column="M1_C"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="2.4" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="24" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#test]" Column="ID"></ColumnReference><ColumnReference Table="[#test]" Column="CODE1"></ColumnReference><ColumnReference Table="[#test]" Column="M1_U"></ColumnReference><ColumnReference Table="[#test]" Column="M1_C"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="[#test]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="CODE1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="M1_U"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="M1_C"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#test]" TableReferenceId="2" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="CASE WHEN [#test].[CODE1] IS NOT NULL THEN [#test].[CODE1] ELSE CASE WHEN [#test].[M1_U] IS NOT NULL THEN [#test].[M1_U] ELSE [#test].[M1_C] END END IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="CODE1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="CODE1"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M1_U"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M1_U"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M1_C"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></ComputeScalar></RelOp><RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1.66667" EstimateIO="0" EstimateCPU="1.66667e-07" AvgRowSize="27" EstimatedTotalSubtreeCost="0.00328721" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#test]" Column="ID"></ColumnReference><ColumnReference Table="[#test]" Column="CODE2"></ColumnReference><ColumnReference Table="[#test]" Column="M2_U"></ColumnReference><ColumnReference Table="[#test]" Column="M2_C"></ColumnReference><ColumnReference Column="Expr1011"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="10" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1.66667" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[#test]" Column="ID"></ColumnReference><ColumnReference Table="[#test]" Column="CODE2"></ColumnReference><ColumnReference Table="[#test]" Column="M2_U"></ColumnReference><ColumnReference Table="[#test]" Column="M2_C"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="[#test]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="CODE2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="M2_U"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[#test]" Column="M2_C"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#test]" TableReferenceId="3" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="CASE WHEN [#test].[CODE2] IS NOT NULL THEN [#test].[CODE2] ELSE CASE WHEN [#test].[M2_U] IS NOT NULL THEN [#test].[M2_U] ELSE [#test].[M2_C] END END IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="CODE2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="CODE2"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M2_U"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M2_U"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Table="[#test]" Column="M2_C"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></ComputeScalar></RelOp></Concat></RelOp></Sort></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
| ID | CODE | M_U | M_C | Line |
|---|---|---|---|---|
| 123 | 1 | ABC | XYZ | 1 |
| 456 | G4762 | D01 | 398 | 1 |
| 456 | G4765 | E043 | null | 2 |
| 893 | H7832 | U73 | R12 | 1 |
| 893 | H9833 | null | F82 | 2 |
| 893 | H563 | U83 | null | 3 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4053.23"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT 
 t.ID,
 v.*
FROM #test t
CROSS APPLY (
 SELECT t.CODE, t.M_U, t.M_C, 1
 WHERE t.CODE IS NOT NULL
 UNION ALL
 SELECT t.CODE1, t.M1_U, t.M1_C, 2
 WHERE t.CODE1 IS NOT NULL
 UNION ALL
 SELECT t.CODE2, t.M2_U, t.M2_C, 3
 WHERE t.CODE2 IS NOT NULL
) v(CODE, M_U, M_C, Line)
ORDER BY
 t.ID,
 v.Line" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0147443" StatementEstRows="9" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x50B988C310311698" QueryPlanHash="0xB1C278593AC67394" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="0" CompileCPU="0" CompileMemory="296"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="978968" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="2584672"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000003_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.38" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_0000000A_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000006_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.38" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_0000000B_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000008_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.38" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_0000000C_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000007_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2022-07-14T21:32:30.36" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000004_BD31967C]" Table="[#test_______________________________________________________________________________________________________________000000000516]" 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="Sort" LogicalOp="Sort" EstimateRows="9" EstimateIO="0.0112613" EstimateCPU="0.000144538" AvgRowSize="32" EstimatedTotalSubtreeCost="0.0147443" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#test" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" 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="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Table="#test" Alias="[t]" Column="ID"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Column="Union1008"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="9" EstimateIO="0" EstimateCPU="3.762e-05" AvgRowSize="32" EstimatedTotalSubtreeCost="0.00333851" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#test" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="#test" Alias="[t]" Column="CODE"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M_U"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M_C"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="CODE1"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M1_U"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M1_C"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="CODE2"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M2_U"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M2_C"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="3" EstimatedRowsRead="3" EstimateIO="0.003125" EstimateCPU="0.0001603" AvgRowSize="50" EstimatedTotalSubtreeCost="0.0032853" TableCardinality="3" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="#test" Alias="[t]" Column="ID"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="CODE"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M_U"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M_C"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="CODE1"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M1_U"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M1_C"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="CODE2"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M2_U"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M2_C"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="ID"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="CODE"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="M_U"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="M_C"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="CODE1"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="M1_U"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="M1_C"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="CODE2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="M2_U"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="#test" Alias="[t]" Column="M2_C"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#test]" Alias="[t]" Storage="RowStore"></Object></TableScan></RelOp><RelOp NodeId="3" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="3" EstimateIO="0" EstimateCPU="3e-07" AvgRowSize="28" EstimatedTotalSubtreeCost="1.5591e-05" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Column="Union1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><Concat><DefinedValues><DefinedValue><ColumnReference Column="Union1005"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="CODE"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="CODE1"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="CODE2"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1006"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M_U"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M1_U"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M2_U"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1007"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M_C"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M1_C"></ColumnReference><ColumnReference Table="#test" Alias="[t]" Column="M2_C"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="Union1008"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="4.897e-06" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-07" AvgRowSize="9" EstimatedTotalSubtreeCost="4.597e-06" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="3" ActualRewinds="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="6" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="3.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan></ConstantScan></RelOp><Predicate><ScalarOperator ScalarString="#test.[CODE] as [t].[CODE] IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="#test" Alias="[t]" Column="CODE"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="4.897e-06" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-07" AvgRowSize="9" EstimatedTotalSubtreeCost="4.597e-06" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="3" ActualRewinds="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="9" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="3.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="2" ActualExecutions="2"></RunTimeCountersPerThread></RunTimeInformation><ConstantScan></ConstantScan></RelOp><Predicate><ScalarOperator ScalarString="#test.[CODE1] as [t].[CODE1] IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="#test" Alias="[t]" Column="CODE1"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp><RelOp NodeId="10" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="11" EstimatedTotalSubtreeCost="4.897e-06" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-07" AvgRowSize="9" EstimatedTotalSubtreeCost="4.597e-06" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="2" ActualRewinds="1" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="12" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="3.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" 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><Predicate><ScalarOperator ScalarString="#test.[CODE2] as [t].[CODE2] IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Table="#test" Alias="[t]" Column="CODE2"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp></Concat></RelOp></NestedLoops></RelOp></Sort></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |