SQL Server – Creating Sequence Based on Another Column’s Value

This will produce the desired result for a single query.

drop table if exists T1;
go

create table T1(RowId int, UserId int, Desired_SequenceNumber int);
go
insert T1
values
(1, 1, 9997),
(2, 1, 9998),
(3, 2, 1),
(4, 2, 2),
(5, 3, 1),
(6, 1, 9999);   -- Have increased User 1's values to test wrap-around.
go

select
    T1.*,
    SequenceNumber = ((ROW_NUMBER() over (partition by UserId order by RowId) - 1) % 9999) + 1
from t1
order by RowId;

The ROW_NUMBER built-in function creates a sequential integer for each row in the result. partition by UserId ensures each value of UserId starts counting again at 1. Order by RowId ensures the ordering of SequenceNumber matches the ordering of RowId. The - 1) % 9999) + 1 bit ensures wrap-around at 9999 and starting again at 1, rather than at zero.

If the SequenceNumbers must be written once and consistent for all subsequent reads the above will not work. Then you will need something like

;with NewData as
(
    select *
    from
    (
        values
            (7, 1),
            (8, 4)
    ) as T(RowId, UserId)
),
LatestRow as
(
    select
        UserId,
        RowId = MAX(RowId)
    from T1
    group by UserId
),
NextSequence as
(
    select
        T1.UserId,
        Number = (T1.Desired_SequenceNumber + 1) % 9999
    from T1
    inner join LatestRow
        on LatestRow.RowId = T1.RowId
)
-- insert T1(RowId, UserId, Desired_SequenceNumber)
select
    NewData.RowId,
    NewData.UserId,
    Desired_SequenceNumber = ISNULL(NextSequence.Number, 1)
from NewData
left outer join NextSequence
    on NextSequence.UserId = NewData.UserId;

I've structured it using CTEs for ease of exposition. It could be re-factored for performance.

CTE NewData introduces test data without having to manage tables. Replace this with your actual source. Notice that RowId 7 is for an existing user and 8 is for a new user. I've hard-coded RowIds for ease of reference. This is not necessary for this solution. Your tables likely use an IDENTITY and that's OK.

Because the numbers wrap-around the solution must find the most recent value, not the largest value, and increment from there. I use RowId as a proxy for time. CTE LatestRow finds the most recent i.e. largest / MAX() RowId for each existing user. If RowId is not monotonic this solution will not work and some other measure of time will be required. Note that gaps are OK so deletions and IDENTITY / SEQUENCE is acceptable. Depending on your cardinalities performance may be improved by INNER joining to NewData in this CTE.

CTE NextSequence maps the RowId to an existing sequence value and calculates the next sequence number. The modulo (%) does the wrap-around at 9999. I changed my sample data from that in your question so UserId 1 will wrap around. I'm assuming one row per user in the new data. If there are more you can can assign each in-coming row a local sequence within the new data and add & modulo to the existing base sequence number.

The final select gives the data to be inserted. If your real table has an identity omit RowId. Using an outer join allows new users to be inserted. The ISNULL sets new users' sequences to 1.

Since you get to name SEQUENCE objects you could, in a very convoluted way, create a new one for each value of UserId, and try to code which one to pull from for each in-coming new row. As an exercise it would be interesting. As a production system it would be appalling. Don't do this. The meta-coding would be awful and there will be some limit to the number of sequence objects available in a database.