Forum Discussion

masrini's avatar
masrini
Copper Contributor
Oct 20, 2024

Why Slno Chnages

I was trying to solve a sql puzzle 

Using Microsoft SQL Server 2022 (RTM-CU15-GDR) (KB5046059) - 16.0.4150.1

 

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)

--Verify Data

SELECT charValue,ayValue FROM [MissingValue]

;with rowcte as
(
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

 

output  

----------

 

charvalue ayvalue slno
A                1         1
                   23       2
                   21       3
                   22       4
B                 34       5
                   31       6
                   89       7
C                  222    8
                    10      9
 
In the above Slno for A =1 , B = 5 and C = 8
 
If I enable  nextval  column 
 

;with rowcte as
(
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

 

output  changes 

 

charvalue ayvalue slno nextval
C               222       1     2
B                34         2    3
A                1          3    4
                  23         4   0
                  21         5   0
                  22         6   0
                  31         7   0
                 89         8   0
                10          9   0

 

 Why Orginal Slno Vlaue changed just enabling nextval column 

C= 1, B = 2, A = 3 . 

 

Is this a bug ?

 

 

2 Replies

  • rodgerkong's avatar
    rodgerkong
    Iron Contributor
    Oct 22, 2024

    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

     

Resources