SQL Server 2016

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

CREATE TABLE data
    (cola varchar(3), colb varchar(1))
;
    
INSERT INTO data
    (cola, colb)
VALUES
    ('id1', 'a'),
    ('id1', 'b'),
    ('id2', 'c'),
    ('id2', 'd'),
    ('id3', 'e'),
    ('id3', 'f'),
    ('id4', 'g'),
    ('id4', 'h')
;
8 rows affected

DECLARE @chunk INT;
SET @chunk = 2;
SELECT cola, colb, 
       (DENSE_RANK() OVER (ORDER BY cola) + @chunk - 1) / @chunk AS col_new_id
FROM data


cola
colb
col_new_id


id1
a
1

id1
b
1

id2
c
1

id2
d
1

id3
e
2

id3
f
2

id4
g
2

id4
h
2