Criar sequência com base no valor de outra coluna

Isso produzirá o resultado desejado para uma única consulta.

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;

A função interna ROW_NUMBER cria um número inteiro sequencial para cada linha no resultado. partition by UserIdgarante que cada valor de UserId comece a contar novamente em 1. Order by RowIdgarante que a ordenação de SequenceNumber corresponda à ordenação de RowId. O - 1) % 9999) + 1bit garante o wrap-around em 9999 e o início novamente em 1, em vez de zero.

Se os SequenceNumbers devem ser escritos uma vez e consistentes para todas as leituras subsequentes, o acima não funcionará. Então você vai precisar de algo como

;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;

Eu estruturei usando CTEs para facilitar a exposição. Poderia ser re-fatorado para desempenho.

CTE NewData introduz dados de teste sem ter que gerenciar tabelas. Substitua isso pela sua fonte real. Observe que RowId 7 é para um usuário existente e 8 é para um novo usuário. Eu codifiquei RowIds para facilitar a referência. Isso não é necessário para esta solução. Suas tabelas provavelmente usam uma IDENTIDADE e tudo bem.

Porque os números que envolvem a solução devem encontrar o valor mais recente, não o maior valor, e incrementar a partir daí. Eu uso RowId como um proxy para o tempo. O CTE LatestRow encontra o RowId mais recente, ou seja, o maior / MAX() para cada usuário existente. Se RowId não for monotônico esta solução não funcionará e alguma outra medida de tempo será necessária. Observe que as lacunas estão corretas, portanto, exclusões e IDENTIDADE / SEQUÊNCIA são aceitáveis. Dependendo de suas cardinalidades, o desempenho pode ser melhorado pela adesão do INNER ao NewData neste CTE.

CTE NextSequence mapeia o RowId para um valor de sequência existente e calcula o próximo número de sequência. O módulo (%) faz o wrap-around em 9999. Mudei meus dados de amostra disso na sua pergunta para que o UserId 1 seja envolvido. Estou assumindo uma linha por usuário nos novos dados. Se houver mais, você pode atribuir a cada linha de entrada uma sequência local dentro dos novos dados e adicionar & módulo ao número de sequência base existente.

A seleção final fornece os dados a serem inseridos. Se sua tabela real tiver uma identidade, omita RowId. O uso de uma junção externa permite que novos usuários sejam inseridos. O ISNULL define as sequências de novos usuários para 1.

Como você pode nomear objetos SEQUENCE , você pode , de uma maneira muito complicada, criar um novo para cada valor de UserId e tentar codificar qual deles extrair para cada nova linha de entrada. Como exercício seria interessante. Como sistema de produção, seria terrível. Não faça isso. A meta-codificação seria horrível e haveria algum limite para o número de objetos de sequência disponíveis em um banco de dados.