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

consulta de sequences via management studio ssms

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

 

Consulta da Sequence
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é 🙂

4 respostas

  1. O SQL server é a versão express (não possui job) e eu não posso usar o agendador de tarefas do Windows.

    1. 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é

  2. 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
    )

    1. 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é

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *