clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228830 fiddles created (16655 in the last week).

DROP TABLE IF EXISTS dbo.Example; CREATE TABLE dbo.Example ( id integer PRIMARY KEY, val integer NULL );
 hidden batch(es)


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
 hidden batch(es)


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 136
3 650 650
4 650
5 650
6 650
7 954 954
8 954
9 104 104
10 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>
 hidden batch(es)