By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
26 rows affected
| count | BucketId |
|---|---|
| 2 | 1 |
| 6 | 2 |
| 6 | 3 |
| 2 | 4 |
| 2 | 5 |
| 2 | 6 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3192.2"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH filteredData as 
 (SELECT tv.*,COALESCE(tb1.Id,tb2.Id) as BucketId FROM #TempData tv
 LEFT JOIN #TempBucket tb1 --prio bucket join
 ON tb1.Filter IS NOT NULL AND tb1.Filter = tv.Value
 LEFT JOIN #TempBucket tb2 --range bucket join
 ON tb2.Fromval < tv.value AND tv.value <= tb2.ToVal
 )
SELECT Count(fd.value) as count ,BucketId FROM filteredData fd GROUP BY BucketId" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0258069" StatementEstRows="2" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xBD196D9A355B474C" QueryPlanHash="0x44DFB43FEFE22BE2" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="344"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="16" MaxQueryMemory="957128"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="3275296"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2019-11-15T15:54:09.08" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_ABFBDB4F]" Table="[#TempData___________________________________________________________________________________________________________000000000024]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-11-15T15:54:09.08" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000001_ACEFFF88]" Table="[#TempBucket_________________________________________________________________________________________________________000000000025]" Schema="[dbo]" Database="[tempdb]"></StatisticsInfo><StatisticsInfo LastUpdate="2019-11-15T15:54:09.08" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000004_ACEFFF88]" Table="[#TempBucket_________________________________________________________________________________________________________000000000025]" 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="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="2" EstimateIO="0" EstimateCPU="0" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0258069" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1007"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1008],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1008"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="2" EstimateIO="0" EstimateCPU="1.3e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0258069" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1006"></ColumnReference><ColumnReference Column="Expr1008"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="COUNT([tempdb].[dbo].[#TempData].[value] as [tv].[value])"><Aggregate Distinct="0" AggType="COUNT_BIG"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Column="Expr1006"></ColumnReference></GroupBy><RelOp NodeId="2" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="20" EstimateIO="0.0112613" EstimateCPU="0.000234859" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0257939" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="20" 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="Expr1006"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="20" EstimateIO="0" EstimateCPU="2e-006" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0142978" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="CASE WHEN [tempdb].[dbo].[#TempBucket].[Id] as [tb1].[Id] IS NOT NULL THEN [tempdb].[dbo].[#TempBucket].[Id] as [tb1].[Id] ELSE [tempdb].[dbo].[#TempBucket].[Id] as [tb2].[Id] END"><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Id"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Id"></ColumnReference></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="Id"></ColumnReference></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="20" EstimateIO="0" EstimateCPU="0.0005016" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0142958" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Id"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="Id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#TempBucket].[FromVal] as [tb2].[FromVal]<[tempdb].[dbo].[#TempData].[value] as [tv].[value] AND [tempdb].[dbo].[#TempData].[value] as [tv].[value]<=[tempdb].[dbo].[#TempBucket].[ToVal] as [tb2].[ToVal]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="LT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="FromVal"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="LE"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="ToVal"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Predicate><RelOp NodeId="5" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="20" EstimateIO="0" EstimateCPU="0.0005016" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0088047" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Id"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#TempBucket].[Filter] as [tb1].[Filter]=[tempdb].[dbo].[#TempData].[value] as [tv].[value]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Filter"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate><RelOp NodeId="6" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="20" EstimatedRowsRead="20" EstimateIO="0.003125" EstimateCPU="0.000179" AvgRowSize="11" EstimatedTotalSubtreeCost="0.003304" TableCardinality="20" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="20" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" Column="value"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#TempData]" Alias="[tv]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp><RelOp NodeId="7" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.0032035" EstimateCPU="8.51e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0049055" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="19" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Id"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Filter"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="120" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="20" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="120" ActualEndOfScans="20" ActualExecutions="20"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Filter"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" IndexKind="Heap" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[tempdb].[dbo].[#TempBucket].[Filter] as [tb1].[Filter] IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb1]" Column="Filter"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></NestedLoops></RelOp><RelOp NodeId="8" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.0032035" EstimateCPU="8.51e-005" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0049055" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="19" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="Id"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="FromVal"></ColumnReference><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="ToVal"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="120" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="20" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="120" ActualEndOfScans="20" ActualExecutions="20"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="Id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="FromVal"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" Column="ToVal"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Schema="[dbo]" Table="[#TempBucket]" Alias="[tb2]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Sort></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |