By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE HashTable(
RecordID INT,
GroupIdentifier INT,
Hash VARCHAR(50),
GroupHashList VARCHAR(150)
);
INSERT INTO HashTable VALUES
(1,1, '73F294873462B2BA0E930FD16DCCB7', NULL),
(2,1, '90E749375DF806CB6E3F5CA48FFA38', NULL),
(3,1, 'E44256CE7CFCB971EB679BAC25A697', NULL),
(4,2, '73F294873462B2BA0E930FD16DCCB7', NULL),
(5,2, 'E44256CE7CFCB971EB679BAC25A697', NULL),
(6,2, '90E749375DF806CB6E3F5CA48FFA38', NULL);
SELECT * FROM HashTable;
| RecordID | GroupIdentifier | Hash | GroupHashList |
|---|---|---|---|
| 1 | 1 | 73F294873462B2BA0E930FD16DCCB7 | null |
| 2 | 1 | 90E749375DF806CB6E3F5CA48FFA38 | null |
| 3 | 1 | E44256CE7CFCB971EB679BAC25A697 | null |
| 4 | 2 | 73F294873462B2BA0E930FD16DCCB7 | null |
| 5 | 2 | E44256CE7CFCB971EB679BAC25A697 | null |
| 6 | 2 | 90E749375DF806CB6E3F5CA48FFA38 | null |
SET STATISTICS XML ON;
SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)
FROM HashTable
GROUP BY [GroupIdentifier]
| (No column name) |
|---|
| 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)
FROM HashTable
GROUP BY [GroupIdentifier]" StatementId="1" StatementCompId="1" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0146787" StatementEstRows="2" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xD12534EDAEAF4267" QueryPlanHash="0xAF979A39E61E5B04" 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="24" CompileTime="15" CompileCPU="15" CompileMemory="136"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="807136" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1472472"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-06-15T10:25:10.27" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_22AA2996]" Table="[HashTable]" Schema="[dbo]" Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]"></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="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="2" EstimateIO="0" EstimateCPU="4.6e-06" AvgRowSize="4011" EstimatedTotalSubtreeCost="0.0146787" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="STRING_AGG([fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[Hash],';')WITHIN GROUP (ORDER BY [fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[Hash])"><Aggregate Distinct="0" AggType="STRING_AGG"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="Hash"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="';'"></Const></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="GroupIdentifier"></ColumnReference></GroupBy><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="6" EstimateIO="0.0112613" EstimateCPU="0.000124235" AvgRowSize="40" EstimatedTotalSubtreeCost="0.0146741" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="Hash"></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 Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="GroupIdentifier"></ColumnReference></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="Hash"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="40" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="Hash"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="GroupIdentifier"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Column="Hash"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></Sort></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)
FROM HashTable
GROUP BY [GroupIdentifier]
SELECT
SELECT
| Cached plan size | 24 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0146787 |
| Degree of Parallelism | 0 |
| Memory Grant | 1024 |
| Estimated Number of Rows | 2 |
Statement
SELECT STRING_AGG([Hash],';') WITHIN GROUP (ORDER BY [Hash] ASC)
FROM HashTable
GROUP BY [GroupIdentifier]
Stream Aggregate
(Aggregate)
Cost: 0%
Stream Aggregate
Compute summary values for groups of rows in a suitably sorted stream.
| Physical Operation | Stream Aggregate |
|---|---|
| Logical Operation | Aggregate |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 2 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000046 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000046 |
| Estimated Subtree Cost | 0.0146787 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 2 |
| Estimated Row Size | 4011 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 0 |
Output List
Expr1003
Sort
Cost: 78%
Sort
Sort the input.
| Physical Operation | Sort |
|---|---|
| Logical Operation | Sort |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0113855 (78%) |
| Estimated I/O Cost | 0.0112613 |
| Estimated CPU Cost | 0.0001242 |
| Estimated Subtree Cost | 0.0146741 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 40 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 1 |
Output List
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].Hash
Order By
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].GroupIdentifier Ascending
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].Hash Ascending
Table Scan
[HashTable]
Cost: 22%
Table Scan
Scan rows from a table.
| Physical Operation | Table Scan |
|---|---|
| Logical Operation | Table Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 6 |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032886 (22%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001636 |
| Estimated Subtree Cost | 0.0032886 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 6 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 40 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 2 |
Output List
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].Hash
Object
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable]
UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
JOIN
(
SELECT
GroupIdentifier,
HashList =
STRING_AGG(HT2.[Hash], ';')
WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
FROM HashTable AS HT2
GROUP BY
HT2.GroupIdentifier
) AS C
ON C.GroupIdentifier = HT1.GroupIdentifier;
| RecordID | GroupIdentifier | Hash | GroupHashList |
|---|---|---|---|
| 1 | 1 | 73F294873462B2BA0E930FD16DCCB7 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 2 | 1 | 90E749375DF806CB6E3F5CA48FFA38 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 3 | 1 | E44256CE7CFCB971EB679BAC25A697 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 4 | 2 | 73F294873462B2BA0E930FD16DCCB7 | 73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38 |
| 5 | 2 | E44256CE7CFCB971EB679BAC25A697 | 73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38 |
| 6 | 2 | 90E749375DF806CB6E3F5CA48FFA38 | 73F294873462B2BA0E930FD16DCCB7;E44256CE7CFCB971EB679BAC25A697;90E749375DF806CB6E3F5CA48FFA38 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
JOIN
(
 SELECT
 GroupIdentifier,
 HashList =
 STRING_AGG(HT2.[Hash], ';')
 WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
 FROM HashTable AS HT2
 GROUP BY
 HT2.GroupIdentifier
) AS C
 ON C.GroupIdentifier = HT1.GroupIdentifier" StatementId="1" StatementCompId="1" StatementType="UPDATE" RetrievedFromCache="true" StatementSubTreeCost="0.024815" StatementEstRows="6" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0xCB8EF70E140D549F" QueryPlanHash="0x7E717C5F20DED669" 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="CouldNotGenerateValidParallelPlan" CachedPlanSize="48" CompileTime="16" CompileCPU="16" CompileMemory="360"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="807136" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1472472"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-06-15T10:25:10.27" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_22AA2996]" Table="[HashTable]" Schema="[dbo]" Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Table Update" LogicalOp="Update" EstimateRows="6" EstimateIO="0.01" EstimateCPU="6e-06" AvgRowSize="121" EstimatedTotalSubtreeCost="0.024815" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="RecordID"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupHashList"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" IndexKind="Heap" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupHashList] as [HT1].[GroupHashList] = [Expr1005]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupHashList"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="6e-07" AvgRowSize="94" EstimatedTotalSubtreeCost="0.014809" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(150),[Expr1004],0)"><Convert DataType="varchar" Length="150" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="6" EstimateIO="0" EstimateCPU="0.000101209" AvgRowSize="4019" EstimatedTotalSubtreeCost="0.0148084" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Column="Expr1004"></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"><RelOp NodeId="5" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="2" EstimateIO="0" EstimateCPU="0" AvgRowSize="48" EstimatedTotalSubtreeCost="0.0147033" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="13" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="6" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="6" EstimateIO="0" EstimateCPU="1.3144e-05" AvgRowSize="48" EstimatedTotalSubtreeCost="0.0146901" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference><ColumnReference Column="Segment1009"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference></GroupBy><SegmentColumn><ColumnReference Column="Segment1009"></ColumnReference></SegmentColumn><RelOp NodeId="7" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="6" EstimateIO="0.0112613" EstimateCPU="0.000124243" AvgRowSize="48" EstimatedTotalSubtreeCost="0.014677" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></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 Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="8" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="48" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Column="Bmk1000"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" IndexKind="Heap" Storage="RowStore"></Object><Predicate><ScalarOperator ScalarString="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupIdentifier] as [HT1].[GroupIdentifier] IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></TableScan></RelOp></Sort></RelOp></Segment></RelOp></Spool></RelOp><RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="3" EstimateIO="0" EstimateCPU="1.3144e-06" AvgRowSize="48" EstimatedTotalSubtreeCost="2.6288e-06" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><Predicate><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></Predicate><RelOp NodeId="10" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.3144e-06" AvgRowSize="48" EstimatedTotalSubtreeCost="1.3144e-06" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="STRING_AGG([fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[Hash] as [HT1].[Hash],';')WITHIN GROUP (ORDER BY [HT2].[Hash])"><Aggregate Distinct="0" AggType="STRING_AGG"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="';'"></Const></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="3" EstimateIO="0" EstimateCPU="0" AvgRowSize="48" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference></OutputList><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="2" ActualEndOfScans="3" ActualExecutions="3"></RunTimeCountersPerThread></RunTimeInformation><Spool PrimaryNodeId="5"></Spool></RelOp></StreamAggregate></RelOp><RelOp NodeId="12" PhysicalOp="Table Spool" LogicalOp="Lazy Spool" EstimateRows="3" EstimateIO="0" EstimateCPU="0" AvgRowSize="48" EstimatedTotalSubtreeCost="0" Parallel="0" EstimateRebinds="2" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference></OutputList><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="0" ActualRewinds="2" ActualEndOfScans="2" ActualExecutions="2"></RunTimeCountersPerThread></RunTimeInformation><Spool PrimaryNodeId="5"></Spool></RelOp></NestedLoops></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
JOIN
(
SELECT
GroupIdentifier,
HashList =
STRING_AGG(HT2.[Hash], ';')
WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
FROM HashTable AS HT2
GROUP BY
HT2.GroupIdentifier
) AS C
ON C.GroupIdentifier = HT1.GroupIdentifier
UPDATE
UPDATE
| Cached plan size | 48 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.024815 |
| Degree of Parallelism | 0 |
| Memory Grant | 1024 |
| Estimated Number of Rows | 6 |
Statement
UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
JOIN
(
SELECT
GroupIdentifier,
HashList =
STRING_AGG(HT2.[Hash], ';')
WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
FROM HashTable AS HT2
GROUP BY
HT2.GroupIdentifier
) AS C
ON C.GroupIdentifier = HT1.GroupIdentifier
Table Update
(Update)
Cost: 40%
Table Update
| Physical Operation | Table Update |
|---|---|
| Logical Operation | Update |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Number of Rows Read | 6 |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.010006 (40%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000006 |
| Estimated Subtree Cost | 0.024815 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 121 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 1 |
Output List
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].RecordID
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].Hash
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupHashList
Object
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1]
Predicate
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupHashList] as [HT1].[GroupHashList] = [Expr1005]
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000006 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000006 |
| Estimated Subtree Cost | 0.014809 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 94 B |
| Node ID | 2 |
Output List
Bmk1000
Expr1005
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
| Physical Operation | Nested Loops |
|---|---|
| Logical Operation | Inner Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0001025 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0001012 |
| Estimated Subtree Cost | 0.0148084 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 4019 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 4 |
Output List
Bmk1000
Expr1004
Table Spool
(Lazy Spool)
Cost: 0%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Lazy Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 3 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000132 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0 |
| Estimated Subtree Cost | 0.0147033 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 2 |
| Estimated Row Size | 48 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 5 |
Output List
Bmk1000
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].Hash
Segment
Cost: 0%
Segment
| Physical Operation | Segment |
|---|---|
| Logical Operation | Segment |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000131 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000131 |
| Estimated Subtree Cost | 0.0146901 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 48 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 6 |
Output List
Bmk1000
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].Hash
Segment1009
Sort
Cost: 46%
Sort
Sort the input.
| Physical Operation | Sort |
|---|---|
| Logical Operation | Sort |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0113884 (46%) |
| Estimated I/O Cost | 0.0112613 |
| Estimated CPU Cost | 0.0001242 |
| Estimated Subtree Cost | 0.014677 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 48 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 7 |
Output List
Bmk1000
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].Hash
Order By
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier Ascending
Table Scan
[HashTable].[HT1]
Cost: 13%
Table Scan
Scan rows from a table.
| Physical Operation | Table Scan |
|---|---|
| Logical Operation | Table Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 6 |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032886 (13%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001636 |
| Estimated Subtree Cost | 0.0032886 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 6 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 48 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 8 |
Output List
Bmk1000
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].Hash
Object
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1]
Predicate
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupIdentifier] as [HT1].[GroupIdentifier] IS NOT NULL
Nested Loops
(Inner Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
| Physical Operation | Nested Loops |
|---|---|
| Logical Operation | Inner Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000013 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000013 |
| Estimated Subtree Cost | 0.0000026 |
| Estimated Number of Executions | 3 |
| Number of Executions | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 48 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 9 |
Output List
Bmk1000
Expr1004
Predicate
(1)
Stream Aggregate
(Aggregate)
Cost: 0%
Stream Aggregate
Compute summary values for groups of rows in a suitably sorted stream.
| Physical Operation | Stream Aggregate |
|---|---|
| Logical Operation | Aggregate |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 2 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000013 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000013 |
| Estimated Subtree Cost | 0.0000013 |
| Estimated Number of Executions | 3 |
| Number of Executions | 3 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 48 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 10 |
Output List
Expr1004
Expr1004
Table Spool
(Lazy Spool)
Cost: 0%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Lazy Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0 |
| Estimated Subtree Cost | 0 |
| Estimated Number of Executions | 3 |
| Number of Executions | 3 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 48 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 2 |
| Node ID | 11 |
Output List
Bmk1000
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].Hash
Table Spool
(Lazy Spool)
Cost: 0%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Lazy Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0 |
| Estimated Subtree Cost | 0 |
| Estimated Number of Executions | 3 |
| Number of Executions | 2 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 48 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 2 |
| Node ID | 12 |
Output List
Bmk1000
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].Hash
UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
OUTER APPLY
(
SELECT
HashList =
STRING_AGG(HT2.[Hash], ';')
WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
FROM HashTable AS HT2
WHERE HT2.GroupIdentifier = HT1.GroupIdentifier
) C;
| RecordID | GroupIdentifier | Hash | GroupHashList |
|---|---|---|---|
| 1 | 1 | 73F294873462B2BA0E930FD16DCCB7 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 2 | 1 | 90E749375DF806CB6E3F5CA48FFA38 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 3 | 1 | E44256CE7CFCB971EB679BAC25A697 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 4 | 2 | 73F294873462B2BA0E930FD16DCCB7 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 5 | 2 | E44256CE7CFCB971EB679BAC25A697 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| 6 | 2 | 90E749375DF806CB6E3F5CA48FFA38 | 73F294873462B2BA0E930FD16DCCB7;90E749375DF806CB6E3F5CA48FFA38;E44256CE7CFCB971EB679BAC25A697 |
| Microsoft SQL Server 2005 XML Showplan |
|---|
| <ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
OUTER APPLY
(
 SELECT
 HashList =
 STRING_AGG(HT2.[Hash], ';')
 WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
 FROM HashTable AS HT2
 WHERE HT2.GroupIdentifier = HT1.GroupIdentifier
) C" StatementId="1" StatementCompId="1" StatementType="UPDATE" RetrievedFromCache="true" StatementSubTreeCost="0.0549862" StatementEstRows="6" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2370D15823297FE5" QueryPlanHash="0x4AE94329DD2CFAD1" 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="CouldNotGenerateValidParallelPlan" CachedPlanSize="48" CompileTime="14" CompileCPU="14" CompileMemory="360"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="807136" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1472472"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2024-06-15T10:25:10.27" ModificationCount="0" SamplingPercent="100" Statistics="[_WA_Sys_00000002_22AA2996]" Table="[HashTable]" Schema="[dbo]" Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="1" PhysicalOp="Table Update" LogicalOp="Update" EstimateRows="6" EstimateIO="0.01" EstimateCPU="6e-06" AvgRowSize="121" EstimatedTotalSubtreeCost="0.0549862" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="RecordID"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="Hash"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupHashList"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" IndexKind="Heap" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupHashList] as [HT1].[GroupHashList] = [Expr1006]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupHashList"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Expr1006"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="2" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="6" EstimateIO="0.013125" EstimateCPU="0.00010236" AvgRowSize="94" EstimatedTotalSubtreeCost="0.0449802" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="15" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="6" EstimateIO="0" EstimateCPU="6e-07" AvgRowSize="94" EstimatedTotalSubtreeCost="0.0317528" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Column="Expr1006"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(varchar(150),[Expr1005],0)"><Convert DataType="varchar" Length="150" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="6" EstimateIO="0" EstimateCPU="2.508e-05" AvgRowSize="4019" EstimatedTotalSubtreeCost="0.0317522" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Column="Expr1005"></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 Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference></OuterReferences><RelOp NodeId="5" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1000"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="1" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Column="Bmk1000"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="4011" EstimatedTotalSubtreeCost="0.0284385" Parallel="0" EstimateRebinds="3" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="[Expr1004]"><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="2.3e-06" AvgRowSize="4011" EstimatedTotalSubtreeCost="0.0284379" Parallel="0" EstimateRebinds="3" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="STRING_AGG([fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[Hash] as [HT2].[Hash],';')WITHIN GROUP (ORDER BY [HT2].[Hash])"><Aggregate Distinct="0" AggType="STRING_AGG"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="Hash"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="';'"></Const></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="3" EstimateIO="0" EstimateCPU="2.88e-06" AvgRowSize="36" EstimatedTotalSubtreeCost="0.0284241" Parallel="0" EstimateRebinds="3" EstimateRewinds="2" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="Hash"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="18" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="6" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="9" PhysicalOp="Table Spool" LogicalOp="Eager Spool" EstimateRows="6" EstimateIO="0.013125" EstimateCPU="0.00010236" AvgRowSize="40" EstimatedTotalSubtreeCost="0.0284069" Parallel="0" EstimateRebinds="0" EstimateRewinds="5" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="Hash"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="36" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="25" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="36" ActualRebinds="1" ActualRewinds="5" ActualEndOfScans="6" ActualExecutions="6"></RunTimeCountersPerThread></RunTimeInformation><Spool><RelOp NodeId="10" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="6" EstimateIO="0.0112613" EstimateCPU="0.000124235" AvgRowSize="40" EstimatedTotalSubtreeCost="0.0146741" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="Hash"></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 Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="Hash"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="11" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="6" EstimatedRowsRead="6" EstimateIO="0.003125" EstimateCPU="0.0001636" AvgRowSize="40" EstimatedTotalSubtreeCost="0.0032886" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="GroupIdentifier"></ColumnReference><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="Hash"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="GroupIdentifier"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="Hash"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" IndexKind="Heap" Storage="RowStore"></Object></TableScan></RelOp></Sort></RelOp></Spool></RelOp><Predicate><ScalarOperator ScalarString="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupIdentifier] as [HT2].[GroupIdentifier]=[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupIdentifier] as [HT1].[GroupIdentifier]"><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT2]" Column="GroupIdentifier"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_3cd86de0ef9d4953aa986b596ede1bc5]" Schema="[dbo]" Table="[HashTable]" Alias="[HT1]" Column="GroupIdentifier"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Spool></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML> |
UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
OUTER APPLY
(
SELECT
HashList =
STRING_AGG(HT2.[Hash], ';')
WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
FROM HashTable AS HT2
WHERE HT2.GroupIdentifier = HT1.GroupIdentifier
) C
UPDATE
UPDATE
| Cached plan size | 48 KB |
|---|---|
| Estimated Operator Cost | 0 (0%) |
| Estimated Subtree Cost | 0.0549862 |
| Degree of Parallelism | 0 |
| Memory Grant | 1024 |
| Estimated Number of Rows | 6 |
Statement
UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
OUTER APPLY
(
SELECT
HashList =
STRING_AGG(HT2.[Hash], ';')
WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
FROM HashTable AS HT2
WHERE HT2.GroupIdentifier = HT1.GroupIdentifier
) C
Table Update
(Update)
Cost: 18%
Table Update
| Physical Operation | Table Update |
|---|---|
| Logical Operation | Update |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Number of Rows Read | 6 |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.010006 (18%) |
| Estimated I/O Cost | 0.01 |
| Estimated CPU Cost | 0.000006 |
| Estimated Subtree Cost | 0.0549862 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 121 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 1 |
Output List
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].RecordID
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].Hash
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupHashList
Object
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1]
Predicate
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupHashList] as [HT1].[GroupHashList] = [Expr1006]
Table Spool
(Eager Spool)
Cost: 24%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Eager Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Number of Rows Read | 6 |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0132274 (24%) |
| Estimated I/O Cost | 0.013125 |
| Estimated CPU Cost | 0.0001024 |
| Estimated Subtree Cost | 0.0449802 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 94 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 2 |
Output List
Bmk1000
Expr1006
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000006 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000006 |
| Estimated Subtree Cost | 0.0317528 |
| Estimated Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 94 B |
| Node ID | 3 |
Output List
Bmk1000
Expr1006
Nested Loops
(Left Outer Join)
Cost: 0%
Nested Loops
For each row in the top (outer) input, scan the bottom (inner) input, and output matching rows.
| Physical Operation | Nested Loops |
|---|---|
| Logical Operation | Left Outer Join |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000251 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000251 |
| Estimated Subtree Cost | 0.0317522 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 4019 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 4 |
Output List
Bmk1000
Expr1005
Outer References
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
Table Scan
[HashTable].[HT1]
Cost: 6%
Table Scan
Scan rows from a table.
| Physical Operation | Table Scan |
|---|---|
| Logical Operation | Table Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 6 |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032886 (6%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001636 |
| Estimated Subtree Cost | 0.0032886 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 6 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 19 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 5 |
Output List
Bmk1000
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1].GroupIdentifier
Object
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT1]
Compute Scalar
Cost: 0%
Compute Scalar
Compute new values from existing values in a row.
| Physical Operation | Compute Scalar |
|---|---|
| Logical Operation | Compute Scalar |
| Estimated Execution Mode | Row |
| Estimated Operator Cost | 0.0000006 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000001 |
| Estimated Subtree Cost | 0.0284385 |
| Estimated Number of Executions | 4 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 4011 B |
| Node ID | 6 |
Output List
Expr1005
Stream Aggregate
(Aggregate)
Cost: 0%
Stream Aggregate
Compute summary values for groups of rows in a suitably sorted stream.
| Physical Operation | Stream Aggregate |
|---|---|
| Logical Operation | Aggregate |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000138 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000023 |
| Estimated Subtree Cost | 0.0284379 |
| Estimated Number of Executions | 4 |
| Number of Executions | 6 |
| Estimated Number of Rows | 1 |
| Estimated Row Size | 4011 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 7 |
Output List
Expr1004
Filter
Cost: 0%
Filter
| Physical Operation | Filter |
|---|---|
| Logical Operation | Filter |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 18 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0000172 (0%) |
| Estimated I/O Cost | 0 |
| Estimated CPU Cost | 0.0000029 |
| Estimated Subtree Cost | 0.0284241 |
| Estimated Number of Executions | 4 |
| Number of Executions | 6 |
| Estimated Number of Rows | 3 |
| Estimated Row Size | 36 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 8 |
Output List
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2].Hash
Predicate
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupIdentifier] as [HT2].[GroupIdentifier]=[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[GroupIdentifier] as [HT1].[GroupIdentifier]
Table Spool
(Eager Spool)
Cost: 25%
Table Spool
| Physical Operation | Table Spool |
|---|---|
| Logical Operation | Eager Spool |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Number of Rows Read | 36 |
| Actual Number of Rows | 36 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0137328 (25%) |
| Estimated I/O Cost | 0.013125 |
| Estimated CPU Cost | 0.0001024 |
| Estimated Subtree Cost | 0.0284069 |
| Estimated Number of Executions | 1 |
| Number of Executions | 6 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 40 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 5 |
| Node ID | 9 |
Output List
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2].Hash
Sort
Cost: 21%
Sort
Sort the input.
| Physical Operation | Sort |
|---|---|
| Logical Operation | Sort |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0113855 (21%) |
| Estimated I/O Cost | 0.0112613 |
| Estimated CPU Cost | 0.0001242 |
| Estimated Subtree Cost | 0.0146741 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 40 B |
| Actual Rebinds | 1 |
| Actual Rewinds | 0 |
| Node ID | 10 |
Output List
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2].Hash
Order By
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2].Hash Ascending
Table Scan
[HashTable].[HT2]
Cost: 6%
Table Scan
Scan rows from a table.
| Physical Operation | Table Scan |
|---|---|
| Logical Operation | Table Scan |
| Actual Execution Mode | Row |
| Estimated Execution Mode | Row |
| Storage | RowStore |
| Number of Rows Read | 6 |
| Actual Number of Rows | 6 |
| Actual Number of Batches | 0 |
| Estimated Operator Cost | 0.0032886 (6%) |
| Estimated I/O Cost | 0.003125 |
| Estimated CPU Cost | 0.0001636 |
| Estimated Subtree Cost | 0.0032886 |
| Estimated Number of Executions | 1 |
| Number of Executions | 1 |
| Estimated Number of Rows to be Read | 6 |
| Estimated Number of Rows | 6 |
| Estimated Row Size | 40 B |
| Actual Rebinds | 0 |
| Actual Rewinds | 0 |
| Node ID | 11 |
Output List
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2].GroupIdentifier
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2].Hash
Object
[fiddle_3cd86de0ef9d4953aa986b596ede1bc5].[dbo].[HashTable].[HT2]