Diferenças entre SEQUENCES x IDENTITY no Microsoft SQL Server 2012

Veja neste artigo as diferenças entre SEQUENCES x IDENTITY no Microsoft SQL Server 2012.

por Cristiano Joaquim

Diferenças entre SEQUENCES x IDENTITY no Microsoft SQL Server 2012.

Bom, a pedido dos colegas que viram o Artigo Utilizando Sequences no Microsoft SQL Server 2012, resolvi criar um novo artigo que faz uma comparação entre o uso de SEQUENCE x IDENTITY no Microsoft SQL Server 2012.

Uma das diferenças entre SEQUENCE e IDENTITY está no fato de que as SEQUENCES são acionadas sempre quando forem necessárias, sem dependência de tabelas e campos no banco, onde pode ser chamada diretamente por aplicativos.

Outra diferença está que nas SEQUENCES, nós podemos obter o novo valor antes de usá-lo em um comando, diferente do IDENTITY, onde não podemos obter um novo valor. Além disso, com o IDENTITY não podemos gerar novos valores em uma instrução UPDATE, enquanto que com SEQUENCE, já podemos.

Com SEQUENCES, podemos definir valores máximos e mínimos, além de termos a possibilidade de informar que a mesma irá trabalhar de forma cíclica e com cache, além de podemos obter mais valores em sequencia de um só vez, utilizando para isso a procedure SP_SEQUENCE_GET_RANGE, onde então é permitido atribuirmos os valores individuais para aumentar então o desempenho no uso da SEQUENCE.

Uma das grandes utilidades em IDENTITY que vejo ser muito valiosa está no fato de podermos trabalhar com o mesmo na utilização de TRANSAÇÕES de INSERT, pois, só iremos gerar um próximo valor a partir do momento que o comando for executado, ou seja, que a transação for aceita, ao contrário de uma SEQUENCE, que uma vez chamado seu próximo valor, mesmo que ocorra um erro de transação, o valor é alterado.

Abaixo, tenho um exemplo d0 Script feito e a Imagem 1 apresentando este Script que possa nos dar uma ideia do que estou falando. Este Script irá criar duas tabelas com a mesma estrutura, sendo que o que as diferenciará será, claro que o nome, e seus campos de chave primária.

CREATE DATABASE Artigos
GO

USE Artigos
GO

CREATE TABLE TBL_UsoIDENTITY
(
	ID_Uso INT IDENTITY(1,1),
	DSC_Desc NVARCHAR(30) NOT NULL,
	CONSTRAINT ID_Uso_IDENTITY_PK PRIMARY KEY (ID_Uso)
)
GO

CREATE TABLE TBL_UsoSEQUENCE
(
	ID_Uso INT,
	DSC_Desc NVARCHAR(30) NOT NULL,
	CONSTRAINT ID_Uso_SEQUENCE_PK PRIMARY KEY (ID_Uso)
)
GO

Figura 1 - Script que criará as tabelas de exemplo

Na Imagem 1 acima, percebem que na linha 9, do campo ID_Uso da tabela TBL_UsoIDENTITY, criamos o campo descrevendo que o mesmo será do tipo Inteiro e em seguida, terá o IDENTITY. Esta é a forma que já conhecíamos nas versões anteriores do SQL Server.

Contudo, a partir da linha 15 do Script na Imagem 1, verificamos a criação da tabela TBL_UsoSEQUENCE, que traz o mesmo campo ID_Uso, seguido do seu tipo, porém, sem nenhuma declaração mais.

Percebam também que ambos os campos são PRIMARY KEY, cada uma com seu respectivo nome.

Bom, conforme eu tinha falado no artigo Utilizando SEQUENCES no SQL Server 2012, os objetos SEQUENCES não estão vinculados a tabela. Este vinculo irá ocorrer, por exemplo, se utilizando de PROCEDURES.

Vamos ver a diferença na prática. Para isso, irei criar mais ScriptS para dar um exemplo do que estou falando.

Vou criar a SEQUENCE dbo.SEQ_Artigo, com o Script abaixo:

USE [Artigos]

GO

CREATE SEQUENCE dbo.SEQ_Artigo 
 START WITH 1
 INCREMENT BY 1
 NO CACHE 
 NO CYCLE

GO

Figura 2 - Script gerado para criar uma SEQUENCE

Com a SEQUENCE criada, irei agora fazer um novo Script para criar duas PROCEDURES, cada uma para trabalhar com as tabelas criadas anteriormente. Inclusive, segue o Script para criação da primeira Procedure.

USE [Artigos]
GO


CREATE PROCEDURE [dbo].[PRC_USANDO_IDENTITY]
	@DSC_Desc NVARCHAR(30)
AS
DECLARE @ValorDIVIDE NUMERIC(3);
BEGIN TRANSACTION
BEGIN TRY
	
	INSERT INTO dbo.TBL_UsoIDENTITY
	VALUES
	(
		@DSC_Desc
	);

	SET @ValorDIVIDE = 10 / 0;
	
	COMMIT TRAN

END TRY
BEGIN CATCH 
	
	ROLLBACK TRANSACTION

	SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

Figura 3 - Criando a Procedure vinculada a Tabela TBL_UsoIDENTITY

Vamos agora explicar o que está ocorrendo dentro desta PROCEDURE:

Se vocês olharem bem, eu tenho a declaração de uma BEGIN TRANSACTION, sendo que possuo dentro desta transação um comando de INSERT na tabela TBL_UsoIDENTITY. Não estou passando nenhuma valor para o campo ID_Uso, pois, pelo fato dele ser IDENTITY, automaticamente irá gerar/incluir os campos . Antes porém, além da declaração do parâmetro de entrada, tenho também a declaração de uma variável chamada @ValorDIVIDE do tipo NUMERIC. Após realizar o INSERT, tenho esta mesma variável setada com uma operação de DIVISÃO, que conforme verão, será feita pelo número 10 / 0. Contudo, sabemos que não existe divisão por zero, ocasionando um erro dentro da PROCEDURE e um ROLLBACK da transação.

Resumindo: Este comando de INSERT não será efetivado dentro da Tabela.

Agora, vou criar uma Procedure que irá ser vinculada a Tabela TBL_UsoSEQUENCE e que irá tentar INSERIR informações dentro desta tabela. Para isso, segue mais um Script:

USE [Artigos]
GO

CREATE PROCEDURE [dbo].[PRC_USANDO_SEQUENCE]
	@DSC_Desc NVARCHAR(30)
AS
DECLARE @ValorDIVIDE NUMERIC(3);
BEGIN TRANSACTION
BEGIN TRY

	INSERT INTO dbo.TBL_UsoSEQUENCE (ID_Uso, DSC_Desc)
	VALUES
	(
		NEXT VALUE FOR dbo.SEQ_ARTIGO,
		@DSC_Desc
	);

	SET @ValorDIVIDE = 10 / 0;

	COMMIT TRANSACTION
END TRY
BEGIN CATCH 
	
	ROLLBACK TRANSACTION

	SELECT 
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_MESSAGE() AS ErrorMessage;

		
END CATCH
GO

Figura 4 - Criando a Procedure vinculada a Tabela TBL_UsoSEQUENCE

Da mesma forma, vamos explicar esta PROCEDURE:

Se vocês analisarem o Script na Imagem 4, verão que eu estou utilizando uma variável chamada @ValorSEQUENCE e atribuindo a ela o resultado do comando NEXT VALUE FOR dbo.SEQ_ARTIGO (que é a SEQUENCE criada anteriormente), indicando assim para PROCEDURE buscar o próximo valor de sequencia da SEQUENCE. Este valor, será inserido dentro do campo ID_Uso, que pelo fato de não ser mais IDENTITY, necessita ser descrito no comando, e da mesma forma, incluo a variável @ValorDIVIDE e tento fazer uma divisão por ZERO e como não existe divisão por zero, apresenta um erro e a transação sofre um ROLLBACK.

Contudo, o valor da SEQUENCE foi alterado, não sendo mais o valor 1 iniciado no momento que a criamos.

Mas, vamos ver isso na prática. Execute os Scripts abaixo:

EXEC [dbo].[PRC_USANDO_IDENTITY] 'Teste de IDENTITY'
GO

EXEC [dbo].[PRC_USANDO_SEQUENCE] 'Teste de SEQUENCE'
GO

Percebam o erro da informação de Divisão por zero sendo retornado, pois, tratamos esse erro com um TRY CATCH.

Figura 5 - Executando as Procedures

O Conteúdo de ambas as tabelas não foi aferido, pelo fato de ser executado um ROLLBACK. Porém, a SEQUENCE utilizada pela PROCEDURE PRC_USANDO_SEQUENCE não estará mais com o seu valor inicial igual a 1.

Para confirmar isso, execute o Script abaixo a veja o resultado semelhante ao da Imagem 6:

SELECT NAME, CURRENT_VALUE FROM SYS.SEQUENCES
GO

SELECT IDENT_CURRENT('dbo.TBL_UsoIDENTITY')
GO

Figura 6 - Verificando o valor atual da SEQUENCE e IDENTITY utilizados no exemplo

Com isso, percebemos que para vermos o valor de uma SEQUENCE devemos executar um SELECT na VIEW chamada SYS.SEQUENCES, sendo que temos um objeto totalmente apartado de uma Tabela. No caso do IDENTITY, para verificarmos seu valor, executamos um outro SELECT, porém, utilizando a função IDENTITY_CURRENT e passando a tabela que desejamos consultar o último IDENTITY.

Entrentanto, se executarmos o Script abaixo, verificaremos que a possibilidade de inserir valores para a SEQUENCE mesmo fora de uma PROCEDURE, conforme é mostrado na Imagem 7, abaixo:

SELECT NEXT VALUE FOR dbo.SEQ_ARTIGO;
GO

Figura 7 - Atribuindo um valor para a SEQUENCE fora de uma PROCEDURE

Enfim, em alguns casos é interessante se utilizar IDENTITY e em outros, SEQUENCE. O ideal é analisarmos nossa estrutura de tabelas e informações para vermos se realmente queremos armazenar as informações com Autonumeração gerada pelo IDENTITY ou pelo controle de Numeração de uma SEQUENCE.

Bons estudos.