SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- With nulls
CREATE TABLE T (Id INT IDENTITY(1,1), Value INT)
INSERT T
VALUES
    (NULL), (11), (22), (33),
    (NULL), (44), (55), (66),
    (NULL), (77), (88), (99),
    (NULL)

-- No nulls
CREATE TABLE T2 (Id INT IDENTITY(1,1), Value INT)
INSERT T2
VALUES
    (11), (22), (33),
    (44), (55), (66),
    (77), (88), (99)

-- Naming key for following queries:
--   I = Ignore nulls
--   SP = Strictly preceding - Unbounded preceding through prior row
--   P = Unbounded preceding through current row
--   R = Range +/- 3 rows preceding/following
--   F = Current row through unbounded following
--   SF = Strictly following - next  row through unbounded following
--   U = Unbounded Unbounded preceding through unbounded following
22 rows affected

SET SHOWPLAN_TEXT ON
SELECT  LAG(Value) IGNORE NULLS OVER(ORDER BY Id) AS LagI,
        LEAD(Value) IGNORE NULLS OVER(ORDER BY Id) AS [LeadI ???]
FROM T
ORDER BY T.Id


StmtText


SELECT  LAG(Value) IGNORE NULLS OVER(ORDER BY Id) AS LagI,
        LEAD(Value) IGNORE NULLS OVER(ORDER BY Id) AS [LeadI ???]
FROM T
ORDER BY T.Id




StmtText


  |--Stream Aggregate(GROUP BY:([WindowCount1009]) DEFINE:([Expr1003]=LAST_VALUE_IGNORE_NULLS([fiddle_8468be121a284adc95ba24a559e3981c].[dbo].[T].[Value]), [Expr1004]=LAST_VALUE_IGNORE_NULLS([fiddle_8468be121a284adc95ba24a559e3981c].[dbo].[T].[Value]), [fiddle_8468be121a284adc95ba24a559e3981c].[dbo].[T].[Id]=ANY([fiddle_8468be121a284adc95ba24a559e3981c].[dbo].[T].[Id])))

       |--Window Spool(ROWS BETWEEN:(UNBOUNDED, [BottomRowNumber1006]))

            |--Segment

                 |--Compute Scalar(DEFINE:([BottomRowNumber1006]=[RowNumber1005]-(1)))

                      |--Sequence Project(DEFINE:([RowNumber1005]=row_number))

                           |--Segment

                                |--Sort(ORDER BY:([fiddle_8468be121a284adc95ba24a559e3981c].[dbo].[T].[Id] ASC))

                                     |--Table Scan(OBJECT:([fiddle_8468be121a284adc95ba24a559e3981c].[dbo].[T]))