SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE X
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    v INT
);

INSERT INTO X
    SELECT 121 UNION ALL SELECT NULL UNION ALL SELECT NULL 
    UNION ALL SELECT 312 UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
    UNION ALL SELECT 123 UNION ALL SELECT NULL UNION ALL SELECT NULL 
    UNION ALL SELECT 415 UNION ALL SELECT 416 UNION ALL SELECT NULL UNION ALL SELECT NULL UNION ALL SELECT NULL
    UNION ALL SELECT 200;
16 rows affected

select *
from x
order by id


ID
v


1
121

2
null

3
null

4
312

5
null

6
null

7
null

8
123

9
null

10
null

11
415

12
416

13
null

14
null

15
null

16
200


select x.*,
       (case when v is null then dense_rank() over (partition by v order by null_grp)
        end) as newcolumn
from (select x.*,
             count(v) over (order by id) as null_grp
      from x
     ) x;


ID
v
null_grp
newcolumn


2
null
1
1

3
null
1
1

5
null
2
2

6
null
2
2

7
null
2
2

9
null
3
3

10
null
3
3

13
null
5
4

14
null
5
4

15
null
5
4

1
121
1
null

8
123
3
null

16
200
6
null

4
312
2
null

11
415
4
null

12
416
5
null


Warning: Null value is eliminated by an aggregate or other SET operation.

ID	v
1	121
2	null
3	null
4	312
5	null
6	null
7	null
8	123
9	null
10	null
11	415
12	416
13	null
14	null
15	null
16	200