Rrerku

I have to following input:

 id | value 
----+-------
 1 | 136
 2 | NULL
 3 | 650
 4 | NULL
 5 | NULL
 6 | NULL
 7 | 954
 8 | NULL
 9 | 104
 10 | NULL

I expect the following result:

 id | value 
----+-------
 1 | 136
 2 | 136
 3 | 650
 4 | 650
 5 | 650
 6 | 650
 7 | 954
 8 | 954
 9 | 104
 10 | 104

The trivial solution would be join the tables with a < relation, and then selecting the MAX value in a GROUP BY:

WITH tmp AS (
 SELECT t2.id, MAX(t1.id) AS lastKnownId
 FROM t t1, t t2
 WHERE
 t1.value IS NOT NULL
 AND
 t2.id >= t1.id
 GROUP BY t2.id
)
SELECT
 tmp.id, t.value
FROM t, tmp
WHERE t.id = tmp.lastKnownId;

However, the trivial execution of this code would create internally the square of the count of the rows of the input table ( O(n^2) ). I expected t-sql to optimize it out - on a block/record level, the task to do is very easy and linear, essentially a for loop ( O(n) ).

However, on my experiments, the latest MS SQL 2016 can't optimize this query correctly, making this query impossible to execute for a large input table.

Furthermore, the query has to run quickly, making a similarly easy (but very different) cursor-based solution infeasible.

Using some memory-backed temporary table could be a good compromise, but I am not sure if it can be run significantly quicker, considered that my example query using subqueries didn't work.

I am also thinking on to dig out some windowing function from the t-sql docs, what could be tricked to do what I want. For example, cumulative sum is doing some very similar, but I couldn't trick it to give the latest non-null element, and not the sum of the elements before.

The ideal solution would be a quick query without procedural code or temporary tables. Alternatively, also a solution with temporary tables is okay, but iterating the table procedurally is not.

One method, by using OVER() and MAX() and COUNT() based on this source could be:

SELECT ID, MAX(value) OVER (PARTITION BY Value2) as value
FROM
(
 SELECT ID, value
 ,COUNT(value) OVER (ORDER BY ID) AS Value2
 FROM dbo.HugeTable
) a
ORDER BY ID;

Result

Id UpdatedValue
1 136
2 136
3 650
4 650
5 650
6 650
7 954
8 954
9 104
10 104

Another method based on this source, closely related to the first example

;WITH CTE As 
( 
SELECT value,
 Id, 
 COUNT(value) 
 OVER(ORDER BY Id) As Value2 
FROM dbo.HugeTable
),

CTE2 AS ( 
SELECT Id,
 value,
 First_Value(value) 
 OVER( PARTITION BY Value2
 ORDER BY Id) As UpdatedValue 
FROM CTE 
 ) 
SELECT Id,UpdatedValue 
FROM CTE2;