SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @T table
(
    Id integer IDENTITY NOT NULL PRIMARY KEY, 
    [Hash] binary(16) NOT NULL INDEX h
);

INSERT @T ([Hash]) VALUES (0x31F777F0804D301936411E3ECD760859);
INSERT @T ([Hash]) VALUES (0xD64A593F3E9ACC972158D522A4289EA0);

DECLARE @S table 
(
    [Hash] binary(16) NOT NULL
);

INSERT @S
    ([Hash])
VALUES
    (0x31F777F0804D301936411E3ECD760859),
    (0x31F777F0804D301936411E3ECD760859),
    (0x0C5A65264F92A543E7AAA06375349C06);

SET STATISTICS XML ON;

-- MERGE SOLUTION
WITH
    T AS
    (
        SELECT
            T.[Hash], 
            rn = ROW_NUMBER() OVER (
                PARTITION BY T.[Hash] 
                ORDER BY T.[Hash], T.Id)
        FROM @T AS T
    ),
    S AS
    (
        SELECT DISTINCT
            S.[Hash],
            rn = ROW_NUMBER() OVER (
                PARTITION BY S.[Hash] 
                ORDER BY S.[Hash])
        FROM @S AS S
    )
MERGE T
USING S
    ON S.[Hash] = T.[Hash]
    AND S.rn = T.rn
WHEN NOT MATCHED BY TARGET THEN INSERT ([Hash]) VALUES (S.[Hash])
WHEN NOT MATCHED BY SOURCE THEN DELETE;

SET STATISTICS XML OFF;

SELECT T.* FROM @T AS T ORDER BY T.Id;

Microsoft SQL Server 2005 XML Showplan
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.3.1" Build="12.0.5589.7"><BatchSequence><Batch><Statements><StmtSimple StatementText="WITH T AS ( SELECT T.[Hash], rn = ROW_NUMBER() OVER ( PARTITION BY T.[Hash] ORDER BY T.[Hash], T.Id) FROM @T AS T ), S AS ( SELECT DISTINCT S.[Hash], rn = ROW_NUMBER() OVER ( PARTITION BY S.[Hash] ORDER BY S.[Hash]) FROM @S AS S ) MERGE T USING S ON S.[Hash] = T.[Hash] AND S.rn = T.rn WHEN NOT MATCHED BY TARGET THEN INSERT ([Hash]) VALUES (S.[Hash]) WHEN NOT MATCHED BY SOURCE THEN DELETE;" StatementId="1" StatementCompId="7" StatementType="MERGE" RetrievedFromCache="true" StatementSubTreeCost="0.0549023" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0x74E891C9C8451E6D" QueryPlanHash="0x486DAD02B44696A0" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="56" CompileTime="0" CompileCPU="0" CompileMemory="704"><MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="704" RequiredMemory="640" DesiredMemory="704" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="32" MaxQueryMemory="291576"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="209662" EstimatedPagesCached="13103" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="377096"/><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><RelOp NodeId="1" PhysicalOp="Clustered Index Merge" LogicalOp="Merge" EstimateRows="1" EstimateIO="0.02" EstimateCPU="2e-006" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0549023" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="6" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="1" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Update DMLRequestSort="0"><Object Table="[@T]" Index="[PK__#AE01619__3214EC07B26D93CB]" Alias="[T]" Storage="RowStore"/><Object Table="[@T]" Index="[h]" Alias="[T]" Storage="RowStore"/><SetPredicate><ScalarOperator ScalarString="@T.[Hash] as [T].[Hash] = RaiseIfNullUpdate([Expr1014]),@T.[Id] as [T].[Id] = RaiseIfNullUpdate([Expr1015])"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Column="Expr1014"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ScalarOperator><Intrinsic FunctionName="RaiseIfNullUpdate"><ScalarOperator><Identifier><ColumnReference Column="Expr1015"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><ActionColumn><ColumnReference Column="Action1012"/></ActionColumn><RelOp NodeId="2" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0349003" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Column="Action1012"/><ColumnReference Column="Expr1014"/><ColumnReference Column="Expr1015"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1014"/><ScalarOperator ScalarString="CASE WHEN [Action1012]=(4) THEN [Expr1003] ELSE @T.[Hash] as [T].[Hash] END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Action1012"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(4)"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Column="Expr1003"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="Hash"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1015"/><ScalarOperator ScalarString="CASE WHEN [Action1012]=(4) THEN [Expr1013] ELSE @T.[Id] as [T].[Id] END"><IF><Condition><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Action1012"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(4)"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Column="Expr1013"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="Id"/></Identifier></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="3" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="51" EstimatedTotalSubtreeCost="0.0349002" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"/><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ColumnReference Column="Action1012"/><ColumnReference Column="Expr1013"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Expr1013"/><ScalarOperator ScalarString="getconditionalidentity((-1375641185),(2),N'@T',[Action1012]=(4))"><Intrinsic FunctionName="getconditionalidentity"><ScalarOperator><Const ConstValue="(-1375641185)"/></ScalarOperator><ScalarOperator><Const ConstValue="(2)"/></ScalarOperator><ScalarOperator><Const ConstValue="N'@T'"/></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Action1012"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(4)"/></ScalarOperator></Compare></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-007" AvgRowSize="47" EstimatedTotalSubtreeCost="0.0349001" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"/><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ColumnReference Column="Action1012"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="47" EstimatedTotalSubtreeCost="0.0348996" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"/><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ColumnReference Column="Action1012"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><ComputeScalar ComputeSequence="1"><DefinedValues><DefinedValue><ColumnReference Column="Action1012"/><ScalarOperator ScalarString="ForceOrder(CASE WHEN [SrcPrb1005] IS NOT NULL THEN CASE WHEN [TrgPrb1010] IS NOT NULL THEN NULL ELSE (4) END ELSE (3) END)"><Intrinsic FunctionName="ForceOrder"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="SrcPrb1005"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="TrgPrb1010"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(4)"/></ScalarOperator></Else></IF></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="(3)"/></ScalarOperator></Else></IF></ScalarOperator></Intrinsic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="Merge Join" LogicalOp="Full Outer Join" EstimateRows="1" EstimateIO="0" EstimateCPU="0.0056022" AvgRowSize="51" EstimatedTotalSubtreeCost="0.0348995" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"/><ColumnReference Column="SrcPrb1005"/><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ColumnReference Column="TrgPrb1010"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="4" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Merge ManyToMany="0"><InnerSideJoinColumns><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ColumnReference Column="Expr1007"/></InnerSideJoinColumns><OuterSideJoinColumns><ColumnReference Column="Expr1003"/><ColumnReference Column="Expr1004"/></OuterSideJoinColumns><Residual><ScalarOperator ScalarString="[Expr1003]=@T.[Hash] as [T].[Hash] AND [Expr1004]=[Expr1007]"><Logical Operation="AND"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="Hash"/></Identifier></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="Expr1007"/></Identifier></ScalarOperator></Compare></ScalarOperator></Logical></ScalarOperator></Residual><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0146457" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"/><ColumnReference Column="Expr1004"/><ColumnReference Column="SrcPrb1005"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="SrcPrb1005"/><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"/></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0146456" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1003"/><ColumnReference Column="Expr1004"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><StreamAggregate><DefinedValues/><GroupBy><ColumnReference Column="Expr1003"/><ColumnReference Column="Expr1004"/></GroupBy><RelOp NodeId="9" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="55" EstimatedTotalSubtreeCost="0.0146446" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@S" Alias="[S]" Column="Hash"/><ColumnReference Column="Expr1002"/><ColumnReference Column="Expr1003"/><ColumnReference Column="Expr1004"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"/><ScalarOperator ScalarString="@S.[Hash] as [S].[Hash]"><Identifier><ColumnReference Table="@S" Alias="[S]" Column="Hash"/></Identifier></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Column="Expr1004"/><ScalarOperator ScalarString="[Expr1002]"><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="10" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="8e-008" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0146445" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@S" Alias="[S]" Column="Hash"/><ColumnReference Column="Expr1002"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"/><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"/></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="11" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-008" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0146444" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@S" Alias="[S]" Column="Hash"/><ColumnReference Column="Segment1020"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Segment><GroupBy><ColumnReference Table="@S" Alias="[S]" Column="Hash"/></GroupBy><SegmentColumn><ColumnReference Column="Segment1020"/></SegmentColumn><RelOp NodeId="12" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100023" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0146444" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@S" Alias="[S]" Column="Hash"/></OutputList><MemoryFractions Input="1" Output="0.5"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" 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"/></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Table="@S" Alias="[S]" Column="Hash"/></OrderByColumn></OrderBy><RelOp NodeId="13" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@S" Alias="[S]" Column="Hash"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="@S" Alias="[S]" Column="Hash"/></DefinedValue></DefinedValues><Object Table="[@S]" Alias="[S]" Storage="RowStore"/></TableScan></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><RelOp NodeId="26" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="39" EstimatedTotalSubtreeCost="0.0146446" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ColumnReference Column="Expr1007"/><ColumnReference Column="TrgPrb1010"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="TrgPrb1010"/><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"/></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="27" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="8e-008" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0146445" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ColumnReference Column="Expr1007"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"/><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"/></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="28" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="1" EstimateIO="0" EstimateCPU="2e-008" AvgRowSize="35" EstimatedTotalSubtreeCost="0.0146444" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/><ColumnReference Column="Segment1021"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><Segment><GroupBy><ColumnReference Table="@T" Alias="[T]" Column="Hash"/></GroupBy><SegmentColumn><ColumnReference Column="Segment1021"/></SegmentColumn><RelOp NodeId="29" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100027" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0146444" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/></OutputList><MemoryFractions Input="0.5" Output="0.5"/><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" 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"/></RunTimeInformation><Sort Distinct="0"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Table="@T" Alias="[T]" Column="Hash"/></OrderByColumn><OrderByColumn Ascending="1"><ColumnReference Table="@T" Alias="[T]" Column="Id"/></OrderByColumn></OrderBy><RelOp NodeId="30" PhysicalOp="Index Scan" LogicalOp="Index Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="27" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="Id"/><ColumnReference Table="@T" Alias="[T]" Column="Hash"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="2" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="2" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="@T" Alias="[T]" Column="Id"/></DefinedValue><DefinedValue><ColumnReference Table="@T" Alias="[T]" Column="Hash"/></DefinedValue></DefinedValues><Object Table="[@T]" Index="[h]" Alias="[T]" Storage="RowStore"/></IndexScan></RelOp></Sort></RelOp></Segment></RelOp></SequenceProject></RelOp></ComputeScalar></RelOp></Merge></RelOp></ComputeScalar></RelOp><Predicate><ScalarOperator ScalarString="[Action1012] IS NOT NULL"><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="Action1012"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"/></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></ComputeScalar></RelOp></ComputeScalar></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

Microsoft SQL Server 2005 XML Showplan

Id	Hash
1	0x31F777F0804D301936411E3ECD760859
3	0x0C5A65264F92A543E7AAA06375349C06
4	0x31F777F0804D301936411E3ECD760859