Why Slno Chnages

masrini 

Check the xml of execute plan, it looks like function lead() cause row_number() in cte runs delay. If you assign cte result to a table variant, then getting next value from table variant will gain correct result.

drop table if exists [dbo].[MissingValue]
CREATE TABLE [dbo].[MissingValue]
(
[charValue] [varchar](1) NULL,
[ayValue] [int] NULL,
 )
GO

--Insert Data
truncate table [dbo].[MissingValue]
INSERT INTO [dbo].[MissingValue](charValue,ayValue)
VALUES
('A', 1),
('', 23),
('', 21),
('', 22),
('B', 34),
('', 31),
('', 89),
('C', 222),
('', 10)

declare @rowcte table([charValue] [varchar](1), [ayValue] [int], slno int)

insert into @rowcte
select charvalue,ayvalue ,
row_number() over (order by (select 1)) as slno
from [missingvalue]

select a.charvalue,a.ayvalue,a.slno,
case when a.charvalue >'' then lead(a.slno) over( order by a.charvalue desc)
else 0 end as nextval
from @rowcte a