SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS dbo.Example;

CREATE TABLE dbo.Example
(
    id integer PRIMARY KEY,
    val integer NULL
);
INSERT dbo.Example
    (id, val)
VALUES
    (1, 136),
    (2, NULL),
    (3, 650),
    (4, NULL),
    (5, NULL),
    (6, NULL),
    (7, 954),
    (8, NULL),
    (9, 104),
    (10, NULL);
10 rows affected

set statistics xml on;
SELECT
    E.id,
    E.val,
    lastval = 
        LAST_VALUE(E.val) IGNORE NULLS OVER (
            ORDER BY E.id ROWS UNBOUNDED PRECEDING)
FROM dbo.Example AS E
ORDER BY
    E.id;


id
val
lastval


1
136
136

2
null
136

3
650
650

4
null
650

5
null
650

6
null
650

7
954
954

8
null
954

9
104
104

10
null
104




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.564" Build="16.0.1000.6"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa;    E.id,&#xa;    E.val,&#xa;    lastval = &#xa;        LAST_VALUE(E.val) IGNORE NULLS OVER (&#xa;            ORDER BY E.id ROWS UNBOUNDED PRECEDING)&#xa;FROM dbo.Example AS E&#xa;ORDER BY&#xa;    E.id" StatementId="1" StatementCompId="2" StatementType="SELECT" StatementSqlHandle="0x0900194EBA9A8BC60DEBE8177E266A5C204B0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="1" ParentObjectId="0" StatementParameterizationType="0" RetrievedFromCache="true" StatementSubTreeCost="0.0033254" StatementEstRows="10" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x62CF23EB88650EF8" QueryPlanHash="0x88B88CED7ED2DC83" CardinalityEstimationModelVersion="160"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="32" CompileTime="0" CompileCPU="0" CompileMemory="144"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1567984"></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="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="10" EstimateIO="0" EstimateCPU="6.28e-06" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0033254" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Expr1001"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1001"></ColumnReference><ScalarOperator ScalarString="LAST_VALUE_IGNORE_NULLS([fiddle_aead9ab329064324bb80cd012d29805d].[dbo].[Example].[val] as [E].[val])"><Aggregate Distinct="0" AggType="LAST_VALUE_IGNORE_NULLS"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ScalarOperator ScalarString="ANY([fiddle_aead9ab329064324bb80cd012d29805d].[dbo].[Example].[id] as [E].[id])"><Aggregate Distinct="0" AggType="ANY"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ScalarOperator ScalarString="ANY([fiddle_aead9ab329064324bb80cd012d29805d].[dbo].[Example].[val] as [E].[val])"><Aggregate Distinct="0" AggType="ANY"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Column="WindowCount1005"></ColumnReference></GroupBy><RelOp NodeId="1" PhysicalOp="Window Spool" LogicalOp="Window Spool" EstimateRows="20" EstimateIO="0" EstimateCPU="2.198e-05" AvgRowSize="31" EstimatedTotalSubtreeCost="0.00331912" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="RowNumber1002"></ColumnReference><ColumnReference Column="WindowCount1005"></ColumnReference><ColumnReference Column="Segment1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><WindowSpool><RelOp NodeId="2" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="10" EstimateIO="0" EstimateCPU="6.28e-07" AvgRowSize="31" EstimatedTotalSubtreeCost="0.00329463" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="RowNumber1002"></ColumnReference><ColumnReference Column="Segment1004"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1004"></ColumnReference></SegmentColumn><RelOp NodeId="3" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="8e-07" AvgRowSize="23" EstimatedTotalSubtreeCost="0.003294" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="RowNumber1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="RowNumber1002"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="4" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="10" EstimateIO="0" EstimateCPU="2e-07" AvgRowSize="23" EstimatedTotalSubtreeCost="0.0032932" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Segment1003"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1003"></ColumnReference></SegmentColumn><RelOp NodeId="5" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="10" EstimatedRowsRead="10" EstimateIO="0.003125" EstimateCPU="0.000168" AvgRowSize="15" EstimatedTotalSubtreeCost="0.003293" TableCardinality="10" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" 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 Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_aead9ab329064324bb80cd012d29805d]" Schema="[dbo]" Table="[Example]" Index="[PK__Example__3213E83F3660395D]" Alias="[E]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></Segment></RelOp></SequenceProject></RelOp></Segment></RelOp></WindowSpool></RelOp></StreamAggregate></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
id	val	lastval
1	136	136
2	null	136
3	650	650
4	null	650
5	null	650
6	null	650
7	954	954
8	null	954
9	104	104
10	null	104