Objetivando flexibilizar a forma como numeramos registros no banco de dados, a Microsoft incluiu Sequences no SQL Server a partir da versão SQL2012.
Embora desde os primórdios o mundo Microsoft use o campo Identity para auto numerar tabelas, muitas vezes ele apresenta limitações, por exemplo: Updates em campos identity não são permitidos.
Nesse sentido, sequences no SQL Server vieram para trazer mais robustez e flexibilidade, como veremos no vídeo de hoje:
Porque numerar registros automaticamente?
Assim como você tem um CPF e RG, que são os seus identificadores únicos, por várias razões, é importante identificar os registros das tabelas de forma única, em geral usando números.
Essa identificação única posteriormente será usada para casos como vincular a tabela em questão com outras, através de chaves primárias e estrangeiras e/ou para servir como referência para excluir ou atualizar um registro.
Em uma tabela de amigos, por exemplo, em geral teremos junto com as informações das pessoas, uma coluna contendo a informação de identificação dos registros.
O nome dessa coluna costuma chamar-se ID, que é a abreviação de IDENTIDADE. Ou então variações, como ID_AMIGO, por exemplo.
if object_id('amigos') is not null drop table amigos create table amigos (id_amigo int, nome varchar(100), dt_nascimento date) insert into amigos values (1, 'Jose', '2005-12-24') insert into amigos values (2, 'Joao', '2000-12-24') insert into amigos values (3, 'Maria', '1995-12-24') select * from amigos
id_amigo | nome | dt_nascimento |
---|---|---|
1 | Jose | 2005-12-24 |
2 | Joao | 2000-12-24 |
3 | Maria | 1995-12-24 |
Como podes ver no script acima, quem definiu o ID de cada amigo fomos nós de forma manual.
Nesse caso, antes de inserir um quarto amigo, será necessário pesquisar na tabela qual é o último ID fornecido e então definir o próximo, no exemplo, 4.
Esse é um trabalho que sua aplicação não precisa ter, pois o próprio banco de dados pode controlar as identidades das tabelas através dos campos IDENTITY ou então, a partir da versão 2012, através de Sequences no SQL Server.
Criando e alterando Sequences no SQL Server
Para gerenciar as identidades da tabela de nosso exemplo usando sequences, temos 2 passos:
1) Criar o objeto sequence: Diferente do identity, que é uma propriedade da coluna, a sequence é um objeto indepentende.
2) Usar a sequence: Para usar a sequence você possui duas opções. Pode usar diretamente nos comandos INSERT, ou então configurar uma CONSTRAINT do tipo DEFAULT na coluna de identidade.
Para criar uma sequence para esse exemplo, você pode utilizar o modelo abaixo:
-- Criando uma sequence: create sequence sTeste as smallint -- tipo de dados da sequencia start with 1 -- Valor inicial increment by 1 -- próximo valor = valor atual + increment minvalue 1 -- Valor mínimo que a sequencia fornecerá maxvalue 1000 -- Valor máximo que a sequencia fornecerá no cycle -- Quando acabarem os valores a sequencia para ou reinicia? (cycle / no cycle) cache 5 -- Quantos valores serão mantidos em cache
Para consultar as sequences existentes no seu banco, você pode acessar o Management Studio em: Databases > Nome-do-banco > Programmability > Sequences
Ou então utilizar a view de sistema: sys.sequences:
select name, start_value, increment, minimum_value, maximum_value, is_cycling, cache_size from sys.sequences
name | start_value | increment | minimum_value | maximum_value | is_cycling | cache_size |
---|---|---|---|---|---|---|
sTeste | 1 | 1 | 1 | 32767 | 0 | 5 |
Se precisares alterar a sequence depois de criada, podes usar o comando ALTER com os mesmos parâmetros de criação.
O comando ALTER inclui ainda uma outra opção “RESTART WITH”, que é opcional, naturalmente, e serve para você definir manualmente o próximo número que será fornecido.
Como usar Sequences no SQL Server
Você pode usar uma sequence em SELECT, INSERT, ONSTRAINTS e com VARIÁVEIS. Veja abaixo exemplos:
Sequences com SELECT
if object_id('amigos') is not null drop table amigos if object_id('sTeste') is not null drop sequence sTeste create sequence sTeste as smallint start with 1 increment by 1 select next value for sTeste
proximo_valor |
---|
2 |
Sequences com INSERT
if object_id('amigos') is not null drop table amigos if object_id('sTeste') is not null drop sequence sTeste create sequence sTeste as smallint start with 1 increment by 1 create table amigos (id int, nome varchar(100)) insert amigos (id, nome) values (next value for sTeste, 'josue') insert amigos (id, nome) values (next value for sTeste, 'joao') insert amigos (id, nome) values (next value for sTeste, 'maria') select * from amigos
id | nome |
---|---|
1 | josue |
2 | joao |
3 | maria |
Sequences com CONSTRAINT
if object_id('amigos') is not null drop table amigos if object_id('sTeste') is not null drop sequence sTeste create sequence sTeste as smallint start with 1 increment by 1 create table amigos (id int default next value for sTeste, nome varchar(100)) insert amigos (nome) values ('josue') insert amigos (nome) values ('joao') insert amigos (nome) values ('maria') select * from amigos
id | nome |
---|---|
1 | josue |
2 | joao |
3 | maria |
Sequences com VARIÁVEIS
if object_id('amigos') is not null drop table amigos if object_id('sTeste') is not null drop sequence sTeste create sequence sTeste as smallint start with 1 increment by 1 -- Atribuir valor da sequence a uma variável com SET declare @id smallint set @id = next value for sTeste print 'Valor atribuido pelo SET: ' + convert(varchar, @id) go -- Atribuir valor da sequence a uma variável com SELECT declare @id smallint select @id = next value for sTeste print 'Valor atribuido pelo SELECT: ' + convert(varchar, @id)
O resultado aqui será:
Valor atribuido pelo SET: 1
Valor atribuido pelo SELECT: 2
Script completo da aula
Baixe o script completo da aula aqui:
[sociallocker id=”5114″]
-------------------------------------------------------------------------------------- -- Sequences no SQL Server -------------------------------------------------------------------------------------- -------------------------------------------------- -- Banco de dados o teste -------------------------------------------------- -- Banco de testes use master if db_id('curso') is not null drop database curso go create database curso go use curso go -------------------------------------------------- -- Objetivo de autonumerar (recap para novatos) -------------------------------------------------- -- Identificação manual de registros if object_id('amigos') is not null drop table amigos create table amigos (id int, nome varchar(100)) insert into amigos (id, nome) values (1, 'josue') insert into amigos (id, nome) values (2, 'joao') insert into amigos (id, nome) values (3, 'maria') select * from amigos go -- Identificação automática com identity if object_id('amigos') is not null drop table amigos create table amigos (id int IDENTITY(1,1), nome varchar(100)) insert into amigos (nome) values ('josue') insert into amigos (nome) values ('joao') insert into amigos (nome) values ('maria') select * from amigos -- Um dos exemplos onde o identity é limitado (update): -- update amigos set id = 10 where nome = 'josue' -------------------------------------------------- -- Trabalhando com sequences -------------------------------------------------- -- Criando uma sequence: create sequence sTeste as smallint -- tipo de dados da sequencia start with 1 -- Valor inicial increment by 1 -- próximo valor = valor atual + increment minvalue 1 -- Valor mínimo que a sequencia fornecerá (smallint é -32,768, no exemplo iniciamos em 1) maxvalue 1000 -- Valor máximo que a sequencia fornecerá (smallint é 32,767, no exemplo limitamos em 1000) no cycle -- Quando a sequencia atingir o valor máximo ela para ou reinicia? (cycle / no cycle) cache 5 -- Quantos valores ela armazenará em cache -- Sequences existentes (SSMS: Databases > Curso > Programmabily > Sequences) select * from sys.sequences -- Obter o próximo valor select next value for sTeste select current_value, * from sys.sequences -- Alterar a sequence (Inicia em: 0. Menor valor: 0. Incrementa de 100 em 100) alter sequence sTeste restart with 0 minvalue 0 increment by 100 cycle -- 10 vezes atinge o valor máximo select next value for sTeste -- Numerando registros: SEM default alter sequence sTeste restart with 1 increment by 1 if object_id('amigos') is not null drop table amigos create table amigos (id int, nome varchar(100)) insert amigos (id, nome) values (next value for sTeste, 'josue') insert amigos (id, nome) values (next value for sTeste, 'joao') insert amigos (id, nome) values (next value for sTeste, 'maria') select * from amigos -- Numerando registros: COM default alter sequence sTeste restart with 1 increment by 1 if object_id('amigos') is not null drop table amigos create table amigos ( id int default next value for sTeste, nome varchar(100) ) insert amigos (nome) values ('josue') insert amigos (nome) values ('joao') insert amigos (nome) values ('maria') select * from amigos -- Atribuir valor da sequence a uma variável com SET declare @id smallint set @id = next value for sTeste print 'Valor atribuido pelo SET: ' + convert(varchar, @id) go -- Atribuir valor da sequence a uma variável com SELECT declare @id smallint select @id = next value for sTeste print 'Valor atribuido pelo SELECT: ' + convert(varchar, @id)
[/sociallocker]
CONCLUSÃO
Sequences no SQL Server certamente não chegaram para substituir campos identity, mas sim para complementar nosso repertório de ferramentas com esse poderoso recurso que tem sido a primeira opção de diversos outros bancos de dados.
Como você viu, a utilização de Sequences no SQL é bastante simples, agora é implementar por ai!
Espero que também tenha gostado desse conteúdo.
Abraço do seu amigo Josué 🙂
O SQL server é a versão express (não possui job) e eu não posso usar o agendador de tarefas do Windows.
Conceitualmente você precisa de um agendador (Windows ou SQL) para executar tarefas agendadas. Sem isso, prefiro nem sugerir outras alternativas pois se tornam exponencialmente complexas e sem sentido, como, por exemplo, criar um serviço dotnet que execute um script SQL uma vez por ano. Abraço, Josué
Muito bom !!
É possível fazer uma sequencia com Start em 1 e INCREMENT 1, e quando mudar o ano, reiniciar a sequencia ?
Tem um exemplo ?
— código #Criando a sequencia
CREATE SEQUENCE SEQ_ALUNO
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10
CYCLE CACHE 10;
— código #Criando a tabela T_ALUNO
CREATE TABLE T_ALUNO
(
COD_ALUNO INT,
NOM_ALUNO VARCHAR(50),
ANO int
)
Creio que a forma mais eficiente de fazer isso seja criar um agendamento usando o SQL Agent para que ele execute o comando de reiniciar a sequência no dia 01/01 de cada ano. Abraço, Josué