Sometimes the report needs a value where the sensor recorded no reading. IGNORE NULLS in SQL Server 2022 lets a window calculation carry that value forward. Define the sequence and frame explicitly so the fill follows the intended direction without borrowing a later observation accidentally.
Separate Missing Observations From Filled Display Values
Carrying a value forward creates a derived representation. It does not prove the sensor actually reported that value during the missing interval. Keep the original reading and the filled value as separate columns so the report remains honest about observed and inferred data.
I ask whether a gap should be filled indefinitely. A stale last-known value can become misleading after a long silence. The window function supplies the prior value, while a separate age rule decides whether the report should still use it. Those are different parts of the requirement.
The sample uses synthetic readings for separate devices and includes a leading NULL. Partition by DeviceID so a reading from one device never fills another device's gap. A stable ReadingID resolves equal timestamps. The absence of a measurement should not become a confident new measurement merely because the dashboard dislikes blank cells.
Carry the Last Value Forward With IGNORE NULLS
SQL Server 2022 adds the IGNORE NULLS option used here. LAST_VALUE with a frame from the beginning of the partition through the current row returns the last non-NULL value seen so far. Before the first known value, the result remains NULL.
The next query keeps the raw input and derived output together. ROWS explicitly defines row-by-row progress, and the full ordering makes that progress deterministic. Without an appropriate explicit frame, default RANGE behavior can include peers at the same ordering value and change the intended boundary.
I inspect repeated timestamps during validation. A report that works only because timestamps happened to be unique is fragile. Decide whether equal-time readings have an approved tie order or should be combined first. The window's ordering is part of the data contract. Also keep the final ORDER BY separate, because window order does not guarantee the query's presentation order.
CREATE TABLE #Readings(DeviceID int,ReadingID int,SampleTime datetime2,ReadingValue decimal(12,2) NULL);
INSERT #Readings VALUES
(1,1,'2026-09-24T08:00:00',NULL),(1,2,'2026-09-24T08:01:00',10),
(1,3,'2026-09-24T08:02:00',NULL),(1,4,'2026-09-24T08:03:00',12),
(2,5,'2026-09-24T08:01:00',20);
SELECT DeviceID,ReadingID,SampleTime,ReadingValue,
LAST_VALUE(ReadingValue) IGNORE NULLS OVER
(PARTITION BY DeviceID ORDER BY SampleTime,ReadingID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS LastKnownValue
FROM #Readings ORDER BY DeviceID,SampleTime,ReadingID;Find the Next Value With IGNORE NULLS and a Forward Frame
FIRST_VALUE with IGNORE NULLS and a frame from the current row to the end of the partition finds the next available value. That is a different fill direction. It uses future observations relative to the current row, which can be appropriate for a historical report but not for a real-time claim.
The next query names the result NextKnownValue to keep that distinction visible. A trailing gap with no later observation remains NULL. Do not replace it with zero unless zero is the explicit business policy and is clearly distinguished from an actual zero reading.
What should the report communicate during the gap: the previous observation, the next observation, or no value? Pick that rule before choosing LAST_VALUE or FIRST_VALUE. Combining both into an interpolation is another calculation requiring its own time and numeric assumptions. This example carries a known value; it does not calculate a new measurement between known endpoints.
SELECT DeviceID,ReadingID,SampleTime,ReadingValue,
FIRST_VALUE(ReadingValue) IGNORE NULLS OVER
(PARTITION BY DeviceID ORDER BY SampleTime,ReadingID
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS NextKnownValue
FROM #Readings ORDER BY DeviceID,SampleTime,ReadingID;
Use the Running-Count Method on Earlier Versions
On earlier supported versions, a running COUNT of non-NULL values creates a group number that changes whenever a new known value arrives. Each group contains that known value and its following NULL rows. MAX over the group retrieves the one known value without needing the newer option.
The leading group contains only NULL values and stays NULL. Partition by the device in both stages. Use the same deterministic ordering and explicit ROWS frame for the running count. Leaving those definitions inconsistent can make the older workaround disagree with the newer expression.
The next block demonstrates the two stages through a CTE. It is a query expression, not a command to store a temporary intermediate table. Compare the output by ReadingID with the SQL Server 2022 forward-fill result. Correctness should match before you compare plan cost. The older method can require additional window work, but its performance must be measured on the real input.
WITH Groups AS
(
SELECT *,COUNT(ReadingValue) OVER
(PARTITION BY DeviceID ORDER BY SampleTime,ReadingID ROWS UNBOUNDED PRECEDING) AS ValueGroup
FROM #Readings
)
SELECT DeviceID,ReadingID,SampleTime,ReadingValue,
MAX(ReadingValue) OVER(PARTITION BY DeviceID,ValueGroup) AS LastKnownValue
FROM Groups ORDER BY DeviceID,SampleTime,ReadingID;Keep Staleness and Provenance Available
If the report needs to reject old carried values, retain the timestamp of the last non-NULL reading alongside its value. The same window approach can carry a conditional observation timestamp. Compare that timestamp with the current row or report time under an explicit tolerance rule.
A real zero is a valid non-NULL reading and should be carried according to the same rule. Do not treat zero as missing unless the source contract says so. Likewise, a NULL created by failed conversion is a data-quality exception, not automatically a sensor gap.
Keep the fill provenance visible in exports and downstream calculations. Averaging a filled series weights missing intervals differently from averaging observed readings. That can change the metric's meaning even though every displayed row now has a value. Decide whether downstream analysis should use raw observations or the filled representation and label that choice clearly.
Validate Boundaries and Index the Sequence
Test leading gaps, trailing gaps, consecutive gaps, repeated timestamps, multiple devices, and real zero values. Compare the older and newer methods over identical input. Include the permitted update or late-arrival behavior, because a new historical reading can change later filled values.
An index supporting DeviceID, SampleTime, and the stable identifier can help the ordered calculation on a recurring workload. Include the reading value when coverage is justified. Inspect the actual plan for sorting, memory grants, and spills before adding permanent index work.
IGNORE NULLS makes the fill expression clear, but the report still needs direction, frame, staleness, and provenance rules. Preserve the raw readings, state ROWS explicitly, and test the sequence boundaries. The useful output explains which observation supplied each derived value instead of pretending every filled row was measured.
Related reading on this blog: Difference Between ISNULL and COALESCE and SQL SERVER 2022: GENERATE_SERIES Function.
A filled value is not a new observation, it is a prior or later reading carried under an explicit rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
