Gerar valores automáticos

Talvez seja necessário gerar valores sequenciais automaticamente para uma coluna em uma tabela específica. O Transact-SQL fornece duas maneiras de fazer isso: usar a propriedade IDENTITY com uma coluna específica em uma tabela ou definir um objeto SEQUENCE e usar os valores gerados por ele.

A propriedade IDENTITY

Para usar a propriedade IDENTITY, defina uma coluna usando um tipo de dados numérico com uma escala de 0 (o que significa apenas números inteiros) e inclua a palavra-chave IDENTITY. Os tipos permitidos incluem todos os tipos inteiros e tipos decimais em que você atribui explicitamente uma escala de 0.

Também podem ser especificados uma semente opcional (valor inicial) e um incremento (valor de etapa). Não inserir a semente e o incremento os definirá como 1.

Somente uma coluna em uma tabela pode ter a propriedade IDENTITY definida; ela é frequentemente usada como PRIMARY KEY ou uma chave alternativa.

O código a seguir mostra a criação da tabela Sales.Promotion usada nos exemplos da seção anterior, mas desta vez com uma coluna de identidade chamada PromotionID como a chave primária:

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Inserindo dados em uma coluna de identidade

Quando a propriedade IDENTITY é definida para uma coluna, as instruções INSERT na tabela geralmente não especificam um valor para a coluna IDENTITY. O mecanismo de banco de dados gera um valor usando o próximo valor disponível para a coluna.

Por exemplo, você pode inserir uma linha na tabela Sales.Promotion sem especificar um valor para a coluna PromotionID :

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Observe que, embora a cláusula VALUES não inclua um valor para a coluna PromotionID , você não precisa especificar uma lista de colunas na cláusula INSERT – as colunas de identidade são isentas desse requisito.

Se essa linha for a primeira inserida na tabela, o resultado será uma nova linha como esta:

Quando a tabela foi criada, nenhum valor de semente ou de incremento foi definido para a coluna IDENTITY. Portanto, a primeira linha é inserida com valor de 1. A próxima linha a ser inserida receberá um valor PromotionID de 2 e assim por diante.

Recuperando um valor de identidade

Para retornar o valor IDENTITY atribuído mais recentemente dentro da mesma sessão e escopo, use a função SCOPE_IDENTITY; da seguinte forma:

SELECT SCOPE_IDENTITY();

A função SCOPE_IDENTITY retorna o valor de identidade mais recente gerado no escopo atual para qualquer tabela. Se você precisar do valor de identidade mais recente em uma tabela específica, pode usar a função IDENT_CURRENT, desta forma:

SELECT IDENT_CURRENT('Sales.Promotion');

Substituindo valores de identidade

Se você quiser substituir o valor gerado automaticamente e atribuir um valor específico à coluna IDENTITY, primeiro precisará habilitar inserções de identidade usando a instrução SET IDENTITY INSERT table_name ON. Com essa opção habilitada, você pode inserir um valor explícito para a coluna de identidade, assim como qualquer outra coluna. Quando terminar, você poderá usar a instrução SET IDENTITY INSERT table_name OFF para retomar o uso de valores de identidade automáticos, usando o último valor inserido explicitamente como um valor inicial.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

Como você aprendeu, a propriedade IDENTITY é usada para gerar uma sequência de valores para uma coluna em uma tabela. No entanto, a propriedade IDENTITY não é adequada para coordenar valores em várias tabelas em um banco de dados. Por exemplo, suponha que sua organização diferencie entre vendas diretas e vendas para revendedores e queira armazenar dados para essas vendas em tabelas separadas. Talvez os dois tipos de venda precisem de um número de fatura exclusivo e talvez você prefira não duplicar o mesmo valor para dois tipos diferentes de venda. Uma solução para esse requisito é manter um pool de valores sequenciais exclusivos em ambas as tabelas.

Propagar novamente uma coluna de identidade

Ocasionalmente, você precisará redefinir ou ignorar os valores de identidade para a coluna. Para fazer isso, você "propagará novamente" a coluna usando a função DBCC CHECKIDENT. Você pode usar isso para ignorar muitos valores ou para redefinir o próximo valor de identidade como 1 depois de excluir todas as linhas na tabela. Para obter detalhes completos usando DBCC CHECKIDENT, consulte a documentação de referência doTransact-SQL.

SEQUÊNCIA

No Transact-SQL, você pode usar um objeto de sequência para definir novos valores sequenciais independentemente de uma tabela específica. Um objeto sequencial é criado usando-se a instrução CREATE SEQUENCE, fornecendo-se, opcionalmente, o tipo de dado (deve ser um tipo inteiro ou decimal ou numérico com uma escala de 0), o valor inicial, um valor de incremento, um valor máximo e outras opções relacionadas ao desempenho.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

Para recuperar o próximo valor disponível a partir de uma sequência, use o constructo NEXT VALUE FOR, desta forma:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY ou SEQUENCE

Ao decidir se deseja usar colunas IDENTITY ou um objeto SEQUENCE para preencher valores automaticamente, tenha os seguintes pontos em mente:

• Use SEQUENCE se o aplicativo exigir o compartilhamento de uma única série de números entre várias tabelas ou várias colunas em uma tabela.

• SEQUENCE permite que você classifique os valores por outra coluna. O constructo NEXT VALUE FOR pode usar a cláusula OVER para especificar a coluna de classificação. A cláusula OVER garante que os valores retornados sejam gerados na ordem da subcláusula ORDER BY da cláusula OVER. Essa funcionalidade também permite que você gere números para linhas à medida que elas estão sendo retornadas em um SELECT. No exemplo a seguir, a tabela Production.Product é classificada pela coluna Name e a primeira coluna retornada é um número sequencial.

  SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
      ProductID,
      Name
  FROM Production.Product;
  

  Embora a instrução anterior estivesse apenas selecionando valores SEQUENCE a serem exibidos, os valores ainda estão sendo 'usados' e os valores SEQUENCE exibidos não estarão mais disponíveis. Se você executar o SELECT acima várias vezes, obterá valores SEQUENCE diferentes a cada vez.

• Use SEQUENCE se seu aplicativo exigir que vários números sejam atribuídos ao mesmo tempo. Por exemplo, um aplicativo precisa reservar cinco números sequenciais. Solicitar valores de identidade pode resultar em lacunas na série se outros processos tiverem números emitidos simultaneamente. Você pode usar o procedimento de sistemasp_sequence_get_range para recuperar vários números na sequência de uma só vez.

• SEQUENCE permite que você altere as especificações da sequência, como por exemplo o valor de incremento.

• Os valores IDENTITY são protegidos contra atualizações. Se tentar atualizar uma coluna com a propriedade IDENTITY, você obterá um erro.