After the second coffee I realised that the solution was in fact "current row and X following", here is a "X preceding and current row" improvement.

😎

use master

go

set nocount on

go

/**********************

** BUILD SAMPLE DATA **

**********************/

if object_id('tempdb.dbo.#dat') is not null drop table #dat

create table #dat

(

RID int identity(1,1) primary key clustered,

Value float,

MaxRollingValue float

)

insert into #dat(Value)

select top 10000 (abs(checksum(newid())) % 1000) * rand()

from sys.objects a, sys.objects b

DECLARE @SET_SIZE INT = 10;

SET STATISTICS IO ON;

;WITH T(N) AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))

,NUMS(N) AS (SELECT TOP(SELECT COUNT(*) FROM #dat) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM T T1,T T2,T T3,T T4,T T5)

,NM10(N) AS (SELECT TOP (@SET_SIZE) N - 1 FROM NUMS)

,PRENUM(RID,Value) AS (SELECT -N AS RID,0 AS Value FROM NM10)

,CALC_SET AS

(

SELECT

NM.N

,NM.N + N10.N AS NX

FROM NUMS NM

OUTER APPLY NM10 N10

)

,EXTENDED_DAT AS

(

SELECT

RID

,Value

FROM PRENUM

UNION ALL

SELECT

RID

,Value

FROM #dat

)

SELECT

CS.N

,MAX(DT.Value) AS MAX_VAL

FROM CALC_SET CS

INNER JOIN EXTENDED_DAT DT

ON CS.NX = DT.RID

GROUP BY CS.N

ORDER BY CS.N

SET STATISTICS IO OFF;