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_8e472ab7dab043448835d6f9f093e30e].[dbo].[T].[Value]), [Expr1004]=LAST_VALUE_IGNORE_NULLS([fiddle_8e472ab7dab043448835d6f9f093e30e].[dbo].[T].[Value]), [fiddle_8e472ab7dab043448835d6f9f093e30e].[dbo].[T].[Id]=ANY([fiddle_8e472ab7dab043448835d6f9f093e30e].[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_8e472ab7dab043448835d6f9f093e30e].[dbo].[T].[Id] ASC)) |
| |--Table Scan(OBJECT:([fiddle_8e472ab7dab043448835d6f9f093e30e].[dbo].[T])) |