SQL Server 2017

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 =
        CAST(
            SUBSTRING(
                MAX(CAST(E.id AS binary(4)) + CAST(E.val AS binary(4))) OVER (
                    ORDER BY E.id
                    ROWS UNBOUNDED PRECEDING),
            5, 4)
        AS integer)
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


Warning: Null value is eliminated by an aggregate or other SET operation.




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3045.24"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xd;&#xa;    E.id,&#xd;&#xa;    E.val,&#xd;&#xa;    lastval =&#xd;&#xa;        CAST(&#xd;&#xa;            SUBSTRING(&#xd;&#xa;                MAX(CAST(E.id AS binary(4)) + CAST(E.val AS binary(4))) OVER (&#xd;&#xa;                    ORDER BY E.id&#xd;&#xa;                    ROWS UNBOUNDED PRECEDING),&#xd;&#xa;            5, 4)&#xd;&#xa;        AS integer)&#xd;&#xa;FROM dbo.Example AS E&#xd;&#xa;ORDER BY&#xd;&#xa;    E.id" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0033274" StatementEstRows="10" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x298EB991F7DFB3FD" QueryPlanHash="0xC11B8E49EAA78CF9" CardinalityEstimationModelVersion="140"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="40" CompileTime="0" CompileCPU="0" CompileMemory="184"><Warnings><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(binary(4),[E].[id],0)"></PlanAffectingConvert><PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT(binary(4),[E].[val],0)"></PlanAffectingConvert></Warnings><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419378" EstimatedPagesCached="26211" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="882128"></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="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="19" EstimatedTotalSubtreeCost="0.0033274" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Expr1003"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1003"></ColumnReference><ScalarOperator ScalarString="CONVERT(int,substring([Expr1002],(5),(4)),0)"><Convert DataType="int" Style="0" Implicit="0"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"></ColumnReference></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(5)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(4)"></Const></ScalarOperator></Intrinsic></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="10" EstimateIO="0" EstimateCPU="6.28e-006" AvgRowSize="39" EstimatedTotalSubtreeCost="0.0033264" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Expr1002"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1002"></ColumnReference><ScalarOperator ScalarString="MAX([Expr1004])"><Aggregate Distinct="0" AggType="MAX"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ScalarOperator ScalarString="ANY([fiddle_7b4d9e0e85724f7e816ad79ca84b333a].[dbo].[Example].[id] as [E].[id])"><Aggregate Distinct="0" AggType="ANY"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ScalarOperator ScalarString="ANY([fiddle_7b4d9e0e85724f7e816ad79ca84b333a].[dbo].[Example].[val] as [E].[val])"><Aggregate Distinct="0" AggType="ANY"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference></Identifier></ScalarOperator></Aggregate></ScalarOperator></DefinedValue></DefinedValues><GroupBy><ColumnReference Column="WindowCount1008"></ColumnReference></GroupBy><RelOp NodeId="2" PhysicalOp="Window Spool" LogicalOp="Window Spool" EstimateRows="20" EstimateIO="0" EstimateCPU="2.198e-005" AvgRowSize="39" EstimatedTotalSubtreeCost="0.00332012" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="RowNumber1005"></ColumnReference><ColumnReference Column="WindowCount1008"></ColumnReference><ColumnReference Column="Segment1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="20" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><WindowSpool><RelOp NodeId="3" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="10" EstimateIO="0" EstimateCPU="6.28e-007" AvgRowSize="39" EstimatedTotalSubtreeCost="0.00329563" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="RowNumber1005"></ColumnReference><ColumnReference Column="Segment1007"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1007"></ColumnReference></SegmentColumn><RelOp NodeId="4" PhysicalOp="Sequence Project" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="8e-007" AvgRowSize="31" EstimatedTotalSubtreeCost="0.003295" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="RowNumber1005"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><SequenceProject><DefinedValues><DefinedValue><ColumnReference Column="RowNumber1005"></ColumnReference><ScalarOperator ScalarString="row_number"><Sequence FunctionName="row_number"></Sequence></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="5" PhysicalOp="Segment" LogicalOp="Segment" EstimateRows="10" EstimateIO="0" EstimateCPU="2e-007" AvgRowSize="31" EstimatedTotalSubtreeCost="0.0032942" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference><ColumnReference Column="Segment1006"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Segment><GroupBy></GroupBy><SegmentColumn><ColumnReference Column="Segment1006"></ColumnReference></SegmentColumn><RelOp NodeId="6" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="10" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="23" EstimatedTotalSubtreeCost="0.003294" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference><ColumnReference Column="Expr1004"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1004"></ColumnReference><ScalarOperator ScalarString="CONVERT(binary(4),[fiddle_7b4d9e0e85724f7e816ad79ca84b333a].[dbo].[Example].[id] as [E].[id],0)+CONVERT(binary(4),[fiddle_7b4d9e0e85724f7e816ad79ca84b333a].[dbo].[Example].[val] as [E].[val],0)"><Arithmetic Operation="ADD"><ScalarOperator><Convert DataType="binary" Length="4" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Convert DataType="binary" Length="4" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="7" 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_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="10" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="10" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Alias="[E]" Column="val"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_7b4d9e0e85724f7e816ad79ca84b333a]" Schema="[dbo]" Table="[Example]" Index="[PK__Example__3213E83F536B8870]" Alias="[E]" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp></ComputeScalar></RelOp></Segment></RelOp></SequenceProject></RelOp></Segment></RelOp></WindowSpool></RelOp></StreamAggregate></RelOp></ComputeScalar></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