Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

SQL Server 2012 – Trabalhando com Sequences e comparações com IDENTITY

Visualizações: 8.587 views
Tempo de Leitura: 5 minutos

Olá pessoal!
Bom dia!

Hoje me deparei com uma situação em que a utilização das sequences, velhas conhecidas minhas do Oracle e que a partir do SQL Server 2012 foram introduzidas no SGBD da Microsoft, vi uma solução perfeita para o problema que eu estava enfrentando.

Introdução – Detalhando o Problema

Esse problema consistia em uma tabela com dezenas de milhões de registros e que possui um número sequencial para controle e unicidade do registro e agora surgiu uma necessidade de criar um outro número sequencial nessa mesma tabela, independente, onde os registros existentes deveriam receber NULL e que seria iniciado em 1, para controlar um outro tipo de informação e sendo preenchido apenas quando um determinado evento ocorrer (diferente do sequencial já existente, que é gerado a cada inserção na tabela)

Uma outra situação que deve ser evitado à todo custo, é que nunca duas sessões podem pegar o mesmo sequencial e gravar na tabela e essa possibilidade é bem possível, já que essa tabela possui várias inserções em sessões paralelas por segundo.

Nesse caso, a utilização do IDENTITY não seria possível, uma vez que o IDENTITY é aplicado em todos os registros da tabela, o que não seria o caso. A utilização de estruturas de ranking como o ROW_NUMBER até poderia ser viável, se não fosse tão pesado calcular esse ranking a cada nova inserção (além da possibilidade de duas sessões pegarem o mesmo sequencial).

Diferenças entre Sequence e IDENTITY

SEQUENCEIDENTITY
É um objeto independente, que pode ser utilizado pra preencher qualquer coluna (inclusive, mais de uma na mesma tabela) do tipo numero inteiro (int, bigit, smallint, tinyint, decimal com escala 0 ou numeric com escala 0), de uma ou mais tabelasÉ associado a uma coluna de uma tabela
É populada quando for chamada. Ou seja, pode ser populada a cada inserção ou apenas quando alguma condição for atendidaÉ populada em cada inserção
Ao ser iniciada em uma tabela já populada, os registros anteriores não serão alteradosAo ser iniciada na tabela, a coluna inteira é populada com o sequencial
Deve ser chamado manualmente para gerar o sequencialO sequencial é gerado automaticamente
Possui permissões à parteNão requer permissões adicionais além da tabela
Pode ser definido valor mínimo e máximo (Ex: De 1 a 100)O valor máximo é o limite do tipo de dado da coluna
O sequencial pode ser reiniciado automaticamente ao atingir o valor máximo (parâmetro CYCLE)Ao atingir o valor máximo, não é possível inserir mais registros
Pode ser gerado um novo sequencial em comandos de UPDATE, caso necessárioO sequencial é gerado apenas no INSERT dos dados
Disponível a partir do SQL Server 2012Disponível a partir do SQL Server 6.0 (SQL 95)
O valor atual do sequencial pode ser O valor atual do sequencial pode ser consultando através da view sys.sequencesconsultando através da view sys.identity_columns
O valor da sequencia pode ser reiniciadoO valor da sequencia NÃO pode ser reiniciado

Como criar uma sequence

Para resolver o problema descrito acima, tive a ideia de criar uma sequence, que é um objeto do banco de dados especialmente criado pra esse tipo de necessidade. Diferente do IDENTITY, você pode utilizar mais de uma sequence na tabela e os registros anteriores não são alterados. Estes foram os motivos que me levaram a utilizar esse recurso do SQL Server para resolver essa situação.

Vamos ver agora como criar uma sequence:

Os parâmetros explicados:

  • START WITH: Define qual o número inicial da sequencia
  • MINVALUE e MAXVALUE: Delimitam o limite da SEQUENCE com seu respectivo valor máximo e mínimo. Caso o valor não seja definido, será atribuído o valor máximo e míni do tipo de dado escolhido.
  • INCREMENT BY: Define a quantidade que será incrementada na sequencia. No exemplo acima, será realizado o incremento de 1 em 1.
  • CYCLE: A propriedade CYCLE permite começar novamente um ciclo a partir do momento que a propriedade MINVALUE e MAXVALUE for atingida. Ou seja, ao atingir o valor definido em MAXVALUE, a sequencia será iniciada novamente no valor do parâmetro MINVALUE (quando isso ocorre, serão gerados valores duplicados na sequencia, pois o range todo já foi percorrido)
  • CACHE: Ao utilizar esse parâmetro, o SQL Server pré-aloca os números sequencias pela propriedade CACHE, sendo que o valor padrão para esta é 15, significando que valores os próximos 15 valores disponíveis serão alocados na memória até que sejam utilizados e a sequence já trata esses números como utilizados. Quando todos os números do CACHE são utilizados, 15 novos valores são alocados na memória novamente e assim segue o ciclo. Vale lembrar que se a instância for reiniciada, os números que estão no cache são perdidos e fica esse “buraco” na sequencia.

Como retornar o próximo número de uma sequence

Retornar o próximo número de uma sequence é uma tarefa muito simples:

SQL Server - Sequence Next Value For

Entretanto, você deve ter em vista que diferente do IDENTITY, a cada inserção você deve chamar a sequence para retornar o sequencial e assim inserir na tabela ou então criar uma default constraint para automatizar essa tarefa.

SQL Server - Sequence x Identity

Como retornar o próximo número de uma sequence de forma automática

Embora a maioria das pessoas (eu, inclusive) utilize a sequence manualmente a cada inserção para retornar o próximo sequencial, isso pode ser automatizado utilizando uma DEFAULT CONSTRAINT na tabela:

Exemplo:
SQL Server - Sequence NEXT VALUE FOR Automatic

Como reiniciar o valor da sequence

Em alguns momentos, é necessário que o contador da sequence seja reiniciado ou alterado para um determinado valor especifico. Para isso, podemos utilizar o ALTER SEQUENCE para essa tarefa:

No exemplo acima, estamos reiniciando o valor da sequencia para 1.

Exemplo:
SQL Server - Sequence Restart

Recuperando o valor atual da sequence, sem aumentar a sequencia

Para recuperar o valor atual da sequence basta realizar uma consulta da view sys.sequences:

SQL Server - Sequence Current Value

Como alterar uma sequence

A alteração de uma sequence segue os mesmos parâmetros da criação, podendo ser alterada a qualquer momento.

Exemplos:

Como apagar uma sequence

A remoção de uma sequence no banco de dados SQL Server é simples como a de qualquer outro objeto de banco de dados e pode ser feita utilizando a instrução DROP:

Permissões da sequence

Como já comentado, a sequence são objetos independentes no banco de dados e por tanto, possuem permissões independentes também:

CREATE SEQUENCE: Para se criar uma sequence, é necessário ter permissão de CREATE SEQUENCE, ALTER ou CONTROL no schema. Os usuários da role db_owner e db_ddladmin podem criar, alterar e dropar sequences e os usuários das roles db_owner e db_datawriter podem utilizar a sequence para retornar o próximo número da sequência.

Exemplo de grant:

ALTER SEQUENCE: Para se alterar uma sequence, é necessário ter permissão de ALTER no schema.

Exemplo de grant:

DROP SEQUENCE: Para se apagar uma sequence, é necessário ter permissão de ALTER ou CONTROL no schema.

Demonstração de uma única sequence para mais de uma tabela

Código-fonte do teste

Resultado

É isso aí, pessoal!
Espero que tenham gostado e até o próximo post.

Obs: Precisa utilizar uma sequence em uma user defined function, seja scalar, aggregate ou table-valued e não está conseguindo ? Veja a solução no post Utilizando sequences em user defined functions no SQL Server 🙂

sql server sequence como utilizar trabalhar reiniciar resetar criar apagar alterar retornar pegar o valor atual próximo valor

sql server sequence como utilizar trabalhar reiniciar resetar criar apagar alterar retornar pegar o valor atual próximo valor