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

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

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:

CREATE SEQUENCE dbo.[seq_Teste]
AS [INT]
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    CYCLE
    CACHE
GO

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:

SELECT NEXT VALUE FOR seq_Teste

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.

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:

CREATE TABLE dbo.Teste_Sequence (
    Id INT DEFAULT NEXT VALUE FOR dbo.seq_Teste,
    Nome VARCHAR(100)
)

Exemplo:

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:

ALTER SEQUENCE seq_Teste RESTART WITH 1

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

Exemplo:

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:

SELECT current_value 
FROM sys.sequences
WHERE name = 'seq_Teste'

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:

ALTER SEQUENCE seq_Teste MAXVALUE 99999
ALTER SEQUENCE seq_Teste CACHE

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:

DROP SEQUENCE dbo.Sua_Sequence
GO

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:

GRANT CREATE SEQUENCE ON SCHEMA::dbo TO [DOMINIO\usuario]

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

Exemplo de grant:

GRANT ALTER ON OBJECT::dbo.Sua_Sequence TO [DOMINIO\usuario]

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

CREATE SEQUENCE dbo.[seq_Pessoa]
AS [INT]
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 999999999
    CYCLE
    CACHE
GO

CREATE TABLE dbo.Pessoa_Fisica (
    Id INT DEFAULT NEXT VALUE FOR dbo.seq_Pessoa,
    Nome VARCHAR(100),
    CPF VARCHAR(11)
)

CREATE TABLE dbo.Pessoa_Juridica (
    Id INT DEFAULT NEXT VALUE FOR dbo.seq_Pessoa,
    Nome VARCHAR(100),
    CNPJ VARCHAR(14)
)

INSERT INTO dbo.Pessoa_Fisica (Nome, CPF)
VALUES('Dirceu Resende', '11111111111')

INSERT INTO dbo.Pessoa_Juridica (Nome, CNPJ)
VALUES('Dirceu Resende Ltda', '22222222222222')

INSERT INTO dbo.Pessoa_Fisica (Nome, CPF)
VALUES('Dirceu Resende 2', '33333333333')

INSERT INTO dbo.Pessoa_Juridica (Nome, CNPJ)
VALUES('Dirceu Resende ME', '44444444444444')

SELECT * FROM dbo.Pessoa_Fisica
SELECT * FROM dbo.Pessoa_Juridica

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