By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
| Value | id | value | Id |
|---|---|---|---|
| 1 | 84b1eb24-ea2d-4e63-842a-aee5df20fc59 | 1 | 84b1eb24-ea2d-4e63-842a-aee5df20fc59 |
| 1 | 84b1eb24-ea2d-4e63-842a-aee5df20fc59 | 2 | 84b1eb24-ea2d-4e63-842a-aee5df20fc59 |
| 2 | e2611a75-f035-426a-b0ba-c4f3a1477d5e | 1 | e2611a75-f035-426a-b0ba-c4f3a1477d5e |
| 2 | e2611a75-f035-426a-b0ba-c4f3a1477d5e | 2 | e2611a75-f035-426a-b0ba-c4f3a1477d5e |
| 3 | 5f9f8921-00c4-48c2-b5c1-6cc0d6743f3d | 1 | 5f9f8921-00c4-48c2-b5c1-6cc0d6743f3d |
| 3 | 5f9f8921-00c4-48c2-b5c1-6cc0d6743f3d | 2 | 5f9f8921-00c4-48c2-b5c1-6cc0d6743f3d |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT *
FROM (
 select max(Value) Value, id
 from(
 SELECT
 S1.value ,
 NEWID() AS Id
 FROM GENERATE_SERIES(1, 3) S1
 )x
 group by id
) S1
CROSS APPLY (
 SELECT
 S2.value,
 S1.Id 
 FROM GENERATE_SERIES(1, 2) S2
) S2
ORDER BY S1.Value, S2.Value" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x090016CAB2596CE99BBA0668B08525C9498A0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0227494" StatementEstRows="2" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x5450ECF8C587E59D" QueryPlanHash="0x48C7C32030731F6D" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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="32" CompileTime="15" CompileCPU="0" CompileMemory="240"><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="704" RequiredMemory="640" DesiredMemory="704" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="536064" GrantedMemory="1024" MaxUsedMemory="24"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1488256"></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="Sort" LogicalOp="Sort" EstimateRows="2" EstimateIO="0.0112613" EstimateCPU="0.000103151" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0227494" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1000"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><MemoryFractions Input="0.5" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" 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="704" OutputMemoryGrant="512" UsedMemoryGrant="16" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Expr1001"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2" EstimateIO="0" EstimateCPU="8.36e-06" AvgRowSize="31" EstimatedTotalSubtreeCost="0.011385" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1000"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="2" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="2.3e-06" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0113745" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1000"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1001"></ColumnReference><ScalarOperator ScalarString="MAX(GENERATE_SERIES.[value])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Column="Expr1000"></ColumnReference></GroupBy><RelOp NodeId="3" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="3" EstimateIO="0.0112613" EstimateCPU="0.000107445" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0113722" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Column="Expr1000"></ColumnReference></OutputList><MemoryFractions Input="1" Output="0.5"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" 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="896" OutputMemoryGrant="320" UsedMemoryGrant="16" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Expr1000"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="3" EstimateIO="0" EstimateCPU="3e-07" AvgRowSize="27" EstimatedTotalSubtreeCost="3.457e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Column="Expr1000"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1000"></ColumnReference><ScalarOperator ScalarString="newid()"><Intrinsic FunctionName="newid"></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="3" EstimateIO="0" EstimateCPU="3.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="3.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><MemoryFractions Input="0" Output="0"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></DefinedValue></DefinedValues><Object Table="[GENERATE_SERIES]"></Object><ParameterList><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp></ComputeScalar></RelOp></Sort></RelOp></StreamAggregate></RelOp><RelOp NodeId="6" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="2" EstimateIO="0" EstimateCPU="2.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="2.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><MemoryFractions Input="0" Output="0"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="3" ActualRewinds="0" ActualEndOfScans="3" ActualExecutions="3" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></DefinedValue></DefinedValues><Object Table="[GENERATE_SERIES]"></Object><ParameterList><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp></NestedLoops></RelOp></Sort></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
| sid | v1 | v2 | id |
|---|---|---|---|
| 5C34A9E5-FE60-47F6-B156-8E00550A1FE7 3982198B-4A16-4FEC-ABD8-4AFA1F44C56E | 1 | 1 | 5c34a9e5-fe60-47f6-b156-8e00550a1fe7 |
| 5C34A9E5-FE60-47F6-B156-8E00550A1FE7 3982198B-4A16-4FEC-ABD8-4AFA1F44C56E | 1 | 2 | 5c34a9e5-fe60-47f6-b156-8e00550a1fe7 |
| 5C34A9E5-FE60-47F6-B156-8E00550A1FE7 3982198B-4A16-4FEC-ABD8-4AFA1F44C56E | 1 | 3 | 5c34a9e5-fe60-47f6-b156-8e00550a1fe7 |
| 5C34A9E5-FE60-47F6-B156-8E00550A1FE7 3982198B-4A16-4FEC-ABD8-4AFA1F44C56E | 2 | 1 | 3982198b-4a16-4fec-abd8-4afa1f44c56e |
| 5C34A9E5-FE60-47F6-B156-8E00550A1FE7 3982198B-4A16-4FEC-ABD8-4AFA1F44C56E | 2 | 2 | 3982198b-4a16-4fec-abd8-4afa1f44c56e |
| 5C34A9E5-FE60-47F6-B156-8E00550A1FE7 3982198B-4A16-4FEC-ABD8-4AFA1F44C56E | 2 | 3 | 3982198b-4a16-4fec-abd8-4afa1f44c56e |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="select *
from (
select string_agg(cast(id as char(50)),'') sid
from (select s1.value v1,newid() id from generate_series(1,2) s1)t1
 )t2
cross apply (select s1.value v1 from generate_series(1,2) s1)t3
cross apply(
 select s2.value v2
-- ,t3.v1, (t3.v1-1)*50+1 i
 ,cast( substring(t2.sid,(t3.v1-1)*50+1,50) as uniqueidentifier) id
 from generate_series(1,3)s2
 )t4" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x090070E71061D7C17F1B70AF480800B39EA20000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="4.6611e-05" StatementEstRows="6" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x07EC96EC70FF2142" QueryPlanHash="0xC14FC1E63C6727C0" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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="32" CompileTime="0" CompileCPU="0" CompileMemory="296"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(char(50),[Expr1000],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1488256"></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="6" EstimateIO="0" EstimateCPU="6e-07" AvgRowSize="4035" EstimatedTotalSubtreeCost="4.6611e-05" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1001"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="CONVERT(uniqueidentifier,substring([Expr1001],[Expr1003],(50)),0)"><Convert DataType="uniqueidentifier" Style="0" Implicit="0"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1001"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1003"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(50)"></Const></ScalarOperator></Intrinsic></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="6" EstimateIO="0" EstimateCPU="2.508e-05" AvgRowSize="4023" EstimatedTotalSubtreeCost="4.6011e-05" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1001"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><Warnings NoJoinPredicate="1"></Warnings><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="2" EstimateIO="0" EstimateCPU="8.36e-06" AvgRowSize="4019" EstimatedTotalSubtreeCost="1.4774e-05" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1001"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="3" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.7e-06" AvgRowSize="4011" EstimatedTotalSubtreeCost="4.057e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1001"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1001"></ColumnReference><ScalarOperator ScalarString="STRING_AGG(CONVERT(char(50),[Expr1000],0),'')"><Aggregate Distinct="0" AggType="STRING_AGG"><ScalarOperator><Convert DataType="char" Length="50" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Column="Expr1000"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="''"></Const></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2" EstimateIO="0" EstimateCPU="2e-07" AvgRowSize="23" EstimatedTotalSubtreeCost="2.357e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1000"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1000"></ColumnReference><ScalarOperator ScalarString="newid()"><Intrinsic FunctionName="newid"></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="2" EstimateIO="0" EstimateCPU="2.157e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="2.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues></DefinedValues><Object Table="[GENERATE_SERIES]"></Object><ParameterList><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2" EstimateIO="0" EstimateCPU="2e-07" AvgRowSize="15" EstimatedTotalSubtreeCost="2.357e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="(GENERATE_SERIES.[value]-(1))*(50)+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Arithmetic Operation="MULT"><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Identifier><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(50)"></Const></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="2" EstimateIO="0" EstimateCPU="2.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="2.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><MemoryFractions Input="0" Output="0"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></DefinedValue></DefinedValues><Object Table="[GENERATE_SERIES]"></Object><ParameterList><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp></ComputeScalar></RelOp></NestedLoops></RelOp><RelOp NodeId="8" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="3" EstimateIO="0" EstimateCPU="3.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="6.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="1" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="2" ActualRewinds="0" ActualEndOfScans="2" ActualExecutions="2" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></DefinedValue></DefinedValues><Object Table="[GENERATE_SERIES]"></Object><ParameterList><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
| value | Id | value | Id |
|---|---|---|---|
| 1 | 9dba729d-c7d5-461f-8fc4-229dd361a5ec | 1 | 9dba729d-c7d5-461f-8fc4-229dd361a5ec |
| 1 | 39f89415-b3fa-494f-a2fb-69dff9ac6fb3 | 2 | 39f89415-b3fa-494f-a2fb-69dff9ac6fb3 |
| 2 | a40f2406-bcf9-4aba-ba9b-4fc2b2dc9de2 | 1 | a40f2406-bcf9-4aba-ba9b-4fc2b2dc9de2 |
| 2 | 19abdb25-b683-4afb-b0a9-f89fb6365def | 2 | 19abdb25-b683-4afb-b0a9-f89fb6365def |
| 3 | 51b6d6a6-270d-41c7-9574-6585dffca427 | 1 | 51b6d6a6-270d-41c7-9574-6585dffca427 |
| 3 | 1313a9dc-7b57-4142-a82f-4d8c5a88c75c | 2 | 1313a9dc-7b57-4142-a82f-4d8c5a88c75c |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT *
FROM (
 SELECT
 S1.value,
 NEWID() AS Id
 FROM GENERATE_SERIES(1, 3) S1
) S1
CROSS APPLY (
 SELECT
 S2.value,
 S1.Id -- Trying to repeat the same value multiple times
 FROM GENERATE_SERIES(1, 2) S2
) S2
ORDER BY S1.value, S2.value" StatementId="1" StatementCompId="1" StatementType="SELECT" StatementSqlHandle="0x0900934AF23353471EAE6C33B1F6195FB7A20000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="3.4994e-05" StatementEstRows="6" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x3F168B401F0FBC1D" QueryPlanHash="0x3DDC3B8124B14601" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><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" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1488256"></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="6" EstimateIO="0" EstimateCPU="6e-07" AvgRowSize="31" EstimatedTotalSubtreeCost="3.4994e-05" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Column="Expr1000"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1000"></ColumnReference><ScalarOperator ScalarString="newid()"><Intrinsic FunctionName="newid"></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="6" EstimateIO="0" EstimateCPU="2.508e-05" AvgRowSize="15" EstimatedTotalSubtreeCost="3.4394e-05" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><Warnings NoJoinPredicate="1"></Warnings><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><RelOp NodeId="2" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="3" EstimateIO="0" EstimateCPU="3.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="3.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><MemoryFractions Input="0" Output="0"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></DefinedValue></DefinedValues><Object Table="[GENERATE_SERIES]"></Object><ParameterList><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp><RelOp NodeId="3" PhysicalOp="Table-valued function" LogicalOp="Table-valued function" EstimateRows="2" EstimateIO="0" EstimateCPU="2.157e-06" AvgRowSize="11" EstimatedTotalSubtreeCost="6.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="3" ActualRewinds="0" ActualEndOfScans="3" ActualExecutions="3" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><TableValuedFunction><DefinedValues><DefinedValue><ColumnReference Table="[GENERATE_SERIES]" Column="value"></ColumnReference></DefinedValue></DefinedValues><Object Table="[GENERATE_SERIES]"></Object><ParameterList><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></ParameterList></TableValuedFunction></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |