Já pensou em criar uma sequência de valores  para inserção automática em uma coluna de banco de dados? Hoje quero mostrar exatamente isso, uma forma de programar o banco para uma determinada coluna insira dados automaticamente.

SEQUENCE

A função sequence é um alternativa ao identity porém com mais recursos e maior flexibilidade na sua implementação.

Por ser um objeto criado  para o banco, ela não é atrelada a nenhuma coluna  e qualquer usuário pode utilizar, desde que tenha permissão.

Um grande ponto positivo em comparação ao identity é que aqui  podemos especificar o valor mínimo e máximo de uma sequência criada para atender uma determinada tabela.

Antes de entrar na criação da sequência, vamos observar quais os parâmetros que ela aceita:

  • START WITH –  qual valor irá começar;
  • INCREMENT BY – define como a sequência irá aumentar;
  • MINVALUE – valor mínimo para esta sequência;
  • MAXVALUE – valor máximo;
  • CYCLE – define se uma sequência deve iniciar do valor mínimo ou máximo quando o range da sequência é finalizado;
  • CACHE – é um parâmetro que podemos utilizar quando queremos aumentar a performance de aplicações;  

Suponhamos que queremos uma sequência de 1 – 15. Quando o comando for executado, o banco irá armazenar na memória tais valores até que o último seja utilizado pela aplicação.

Então, se passarmos um parâmetro de cache 50, o SQL Server irá armazenar do 01 ao 50 em memória.

NOTA: se o cache não for especificado, o banco irá assumir o valor default do SQL Server. e se a sequência for criada sem cache, ela pode acabar prejudicando a performance uma vez que para cada valor gerado, o banco irá acessar o disco para escrita de dados.

Vamos criar abaixo uma sequência simples apenas para fins didáticos.

A sequência criada começa com o valor 1, aumenta em 1 e tem a cláusula cycle ativa.

NOTA: uma vez criada a sequência, mesmo podendo alterar diversos parâmetros criados, não podemos alterar o datatype atribuído e quando alterar, não utilizar o start with, o comando acusa erro.

Para alterar uma sequência é simples, vamos ver no exemplo abaixo:

Agora, vamos ver um insert básico utilizando a sequência criada. Repare que só precisamos invocar a sequência na hora do insert  e os valores são inseridos automaticamente.

Um fato ao qual precisamos nos atentar é que para utilizar de fato a função de sequência criada precisamos da cláusula next value for ‘nome_sequence’ sem ela, a sequência não pode ser utilizada e é um simples objeto armazenado na banco.

O interessante é que podemos declarar a função criada com uma constraint e assim eliminamos a necessidade de declarar no insert.

Outro ponto versátil desta função é poder declarar dentro de uma variável a sequência e assim, invocar apenas a variável no insert. Veja o exemplo abaixo:

Para o update, o sistema também é o mesmo, passamos  o next value  e atualizamos a coluna desejada modificando a sequência. Veja o exemplo

DESVANTAGEM

O problema de usar sequence é que uma vez que uma sequência foi criada, aquele valor não pode ser refeito. Como assim?

Digamos que por algum erro ou um update mal feito, executemos um rollback na operação e com isso, voltamos ao estado original. Os dados lá gravados retornarão ao seu estado e  as colunas continuarão íntegras, o problema é que essa sequência que foi criada para esse comando não retorna.

Assim, se formos fazer qualquer operação utilizando aquela função de sequência, a contagem irá iniciar do último valor criado por ela, independente do rollback.

No exemplo anterior, abri uma transação e executei um update, irei dar um rollback nela e fazer um insert em uma tabela, veja a sequência que irá começar. Repare que o update terminou com o número 34.

Veja que após o rollback, o valor da sequência continuou contando, independente d’eu ter voltado atrás na transação. Esse tipo de problema não tem como resolver de uma forma trivial nem reiniciar a contagem ou ‘apagar’ os valores para voltar ao ponto que queremos. 

Caso isso ocorra o que pode ser feito é deletar a sequência, recriar e declarar no parâmetro start with qual número este novo objeto deve começar.

Vou deletar esta função e criar uma nova declarando que ela deve começar do número 37.

E o que acontece se estouramos o valor máximo estipulado para o objeto de sequência criado no banco de dados?

Utilizando a mesma sequência criada para o último exemplo onde o máximo permitido (maxvalue)  é 50, fiz mais alguns inserts na mesma tabela e olha qual foi o resultado obtido após o comando finalizar a operação:

Se repararem, o valor passou a ser negativo, isso ocorre porque a função assume o menor valor para o datatype INT. Como o cycle está habilitado para esta função de sequência criada, ele retorna este valor.

Se o cycle não estivesse habilitado, acusaria erro de limite de range para função.

Se quiser localizar onde as sequences criadas ficam armazenadas no banco, veja:

E para consultar como foi criada e suas propriedades, temos a seguinte DMV executada com algumas das colunas principais, claro, fica a critério de cada um. Trouxe apenas um resumo.

Este foi um post bem extenso sobre como automatizar a criação de sequências nas operações de insert e update. O post também nos mostra maneiras de utilizar este recurso e uma possível desvantagem

No geral é um recurso bem útil que pode ajudar nas suas tarefas diárias.

Espero que tenham gostado! saúde!