Fala pessoal,
Nesse post vou explicar como resolver um problema envolvendo a utilização de SEQUENCE e ROWCOUNT.
Erro: “NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET”
SEQUENCE:
Primeiro, vamos explicar o que é um SEQUENCE:
- É um objeto que gera uma sequência de valores numéricos de acordo com a especificação com a qual a sequência foi criada.
- É gerada em ordem CRESCENTE ou DECRESCENTE em um intervalo definido e pode ser configurada para reiniciar (em um ciclo) quando se esgotar.
- Ao contrário de colunas IDENTITY, não são associadas a tabelas específicas.
- Ao contrário de valores de colunas IDENTITY que são gerados quando as linhas são inseridas, um aplicativo pode obter o próximo número de sequência sem inserir a linha por meio da chamada da função NEXT VALUE FOR.
No post abaixo, o Dirceu Resende trás vários exemplos de como utilizar o SEQUENCE. Recomendo a leitura!
https://dirceuresende.com/blog/sql-server-2012-trabalhando-com-sequences-e-comparacoes-com-identity/
ROWCOUNT:
O comando SET ROWCOUNT é utilizado para fazer com que o SQL Server pare o processamento da query quando um número específico de registros for retornado.
Referência: https://docs.microsoft.com/pt-br/sql/t-sql/statements/set-rowcount-transact-sql?view=sql-server-ver15
Para mais detalhes, sugiro que leia o post abaixo também:
https://luizlima.net/casos-do-dia-a-dia-cuidado-ao-utilizar-o-comando-set-rowcount/
Casos do Dia a Dia – SEQUENCE x ROWCOUNT:
Agora sim vamos ao nosso caso real. Um cliente informou que estava recebendo o erro abaixo (coloquei o texto do erro em inglês):
“NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET”
Ele informou que estava utilizando uma procedure que tinha um SEQUENCE para retornar o próximo ID de uma forma mais rápida e que gerava menos LOCKs no ambiente dele. Contudo, estava apresentando o erro mencionado acima.
Fizemos uma simulação para gerar o erro e coletei algumas informações utilizando um SQL Profiler, o que foi suficiente para encontrar os comandos “SET ROWCOUNT X“ sendo executados pela aplicação.
Visto isso, fiz alguns testes. Aqui vamos criar um SEQUENCE e utilizar alguns valores apenas para demonstrar o funcionamento da função NEXT VALUE FOR.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- CRIANDO O SEQUENCE CREATE SEQUENCE dbo.[Teste_Sequence] AS [INT] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 1000000 GO -- UTILIZANDO O SEQUENCE SELECT NEXT VALUE FOR [Teste_Sequence] -- 1 SELECT NEXT VALUE FOR [Teste_Sequence] -- 2 SELECT NEXT VALUE FOR [Teste_Sequence] -- 3 |
Como podemos observar, a cada vez que utilizamos o SEQUENCE, ele vai retornar o próximo valor (parecido com o funcionamento do IDENTITY, mas não precisamos associar ao insert de uma tabela).
TESTE 1 – SEM ROWCOUNT:
Aqui vamos utilizar o SEQUENCE sem alterar o valor padrão do SET ROWCOUNT = 0 e não vamos ter nenhum problema.
|
1 2 3 4 5 6 7 |
SET ROWCOUNT 0; DECLARE @SEQ INT SELECT @SEQ = NEXT VALUE FOR [Teste_Sequence] SELECT @SEQ |
TESTE 2 – COM ROWCOUNT:
Ao utilizar o SET ROWCOUNT = 500 (qualquer valor diferente de 0) vamos ter o mesmo erro reportado pelo cliente no início do post!
|
1 2 3 4 5 6 7 |
SET ROWCOUNT 500; DECLARE @SEQ INT SELECT @SEQ = NEXT VALUE FOR [Teste_Sequence] SELECT @SEQ |
Msg 11739, Level 15, State 1, Line 41
NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET.
Ao comprovar isso, bastou dizer para o cliente utilizar um SET ROWCOUNT = 0 no início das queries para resolver o problema!
Espero que tenha gostado e que isso também possa ser útil no seu dia a dia. Até o próximo post!
Me siga no LinkedIn e YouTube para ficar por dentro das novidades.
Abraço,
Luiz Vitor França Lima
Consultor SQL Server