SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Heap (h integer NULL);
INSERT dbo.Heap (h) VALUES (1), (2), (3), (4), (5);
5 rows affected

DECLARE @cursor integer;

INSERT TOP (0) dbo.Heap
EXECUTE sys.sp_cursoropen
    @cursor = @cursor OUTPUT,
    @stmt = 'SELECT * FROM dbo.Heap;',
    @scrollopt = 2, -- DYNAMIC
    @ccopt = 2;     -- SCROLL_LOCKS

INSERT TOP (0) dbo.Heap
EXECUTE sys.sp_cursorfetch
    @cursor = @cursor,
    @fetchtype = 32,    -- RELATIVE
    @rownum = 2,        -- Start row
    @nrows = 3;         -- Buffer size

SET STATISTICS XML ON;

EXECUTE sys.sp_cursor
    @cursor = @cursor,
    @optype = 2,    -- DELETE
    @rownum = 0;    -- All in buffer

SET STATISTICS XML OFF;

EXECUTE sys.sp_cursorclose @cursor;


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="DELETE [fiddle_fa476f770c82432e896b52158a58e807].[dbo].[Heap]" StatementId="1" StatementCompId="9" StatementType="DELETE CURSOR" StatementSqlHandle="0x09004CB627237440182A102E200D1885F71C0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="3" ParentObjectId="0" StatementParameterizationType="1" RetrievedFromCache="true" StatementSubTreeCost="0.0279339" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x5109440955ABDC96" QueryPlanHash="0x03505C19E9CDDBD1" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" MemoryGrant="1024" NonParallelPlanReason="NonParallelizableIntrinsicFunction" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="408"><MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" MaxQueryMemory="758568" GrantedMemory="1024" MaxUsedMemory="16"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1285096"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Table Delete" LogicalOp="Delete" EstimateRows="1" EstimateIO="0.01" EstimateCPU="1e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0279339" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Update DMLRequestSort="0"><Object Database="[fiddle_fa476f770c82432e896b52158a58e807]" Schema="[dbo]" Table="[Heap]" IndexKind="Heap" Storage="RowStore"></Object><RelOp NodeId="1" PhysicalOp="Sort" LogicalOp="Distinct Sort" EstimateRows="1" EstimateIO="0.0112613" EstimateCPU="0.000100015" AvgRowSize="15" EstimatedTotalSubtreeCost="0.0179329" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1002"></ColumnReference></OutputList><MemoryFractions Input="1" Output="1"></MemoryFractions><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="16" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Sort Distinct="1"><OrderBy><OrderByColumn Ascending="1"><ColumnReference Column="Bmk1002"></ColumnReference></OrderByColumn></OrderBy><RelOp NodeId="2" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="1.18e-06" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00657166" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="0"><RelOp NodeId="3" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-06" AvgRowSize="24" EstimatedTotalSubtreeCost="0.00657048" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[CWT]" Column="ROWID"></ColumnReference><ColumnReference Column="Bmk1002"></ColumnReference><ColumnReference Column="IsBaseRow1003"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="[CWT]" Column="BMK0"></ColumnReference></OuterReferences><RelOp NodeId="4" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="[CWT]" Column="BMK0"></ColumnReference><ColumnReference Table="[CWT]" Column="ROWID"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="[CWT]" Column="BMK0"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Table="[CWT]" Column="ROWID"></ColumnReference></DefinedValue></DefinedValues><Object Database="[tempdb]" Index="[CWT_PrimaryKey]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Table="[CWT]" Column="ROWID"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="FETCH_RANGE((0))"><Identifier><ColumnReference Column="ConstExpr1009"><ScalarOperator><Intrinsic FunctionName="FETCH_RANGE"><ScalarOperator><Const ConstValue="(0)"></Const></ScalarOperator></Intrinsic></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LT"><RangeColumns><ColumnReference Table="[CWT]" Column="ROWID"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="FETCH_RANGE((1))"><Identifier><ColumnReference Column="ConstExpr1010"><ScalarOperator><Intrinsic FunctionName="FETCH_RANGE"><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Intrinsic></ScalarOperator></ColumnReference></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="5" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-07" AvgRowSize="20" EstimatedTotalSubtreeCost="0.0032832" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1002"></ColumnReference><ColumnReference Column="IsBaseRow1003"></ColumnReference><ColumnReference Column="Expr1005"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1005"></ColumnReference><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="6" PhysicalOp="RID Lookup" LogicalOp="RID Lookup" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="16" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="5" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Bmk1002"></ColumnReference><ColumnReference Column="IsBaseRow1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="3" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="3" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="3" ActualEndOfScans="0" ActualExecutions="3" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Column="Bmk1002"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Column="IsBaseRow1003"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_fa476f770c82432e896b52158a58e807]" Schema="[dbo]" Table="[Heap]" IndexKind="Heap" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Column="Bmk1002"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[CWT].[BMK0]"><Identifier><ColumnReference Table="[CWT]" Column="BMK0"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></ComputeScalar></RelOp></NestedLoops></RelOp><Predicate><ScalarOperator ScalarString="[IsBaseRow1003] IS NOT NULL AND CURSOR_SEND_DONECOUNT(CASE WHEN [Expr1005] IS NOT NULL THEN (1) ELSE NULL END)"><Logical Operation="AND"><ScalarOperator><Logical Operation="IS NOT NULL"><ScalarOperator><Identifier><ColumnReference Column="IsBaseRow1003"></ColumnReference></Identifier></ScalarOperator></Logical></ScalarOperator><ScalarOperator><Intrinsic FunctionName="CURSOR_SEND_DONECOUNT"><ScalarOperator><IF><Condition><ScalarOperator><Compare CompareOp="IS NOT"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Compare></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="NULL"></Const></ScalarOperator></Else></IF></ScalarOperator></Intrinsic></ScalarOperator></Logical></ScalarOperator></Predicate></Filter></RelOp></Sort></RelOp></Update></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>


SELECT H.h 
FROM dbo.Heap AS H;


h


1

5