Gerar valores automáticos

  • 3 minutos

Talvez seja necessário gerar automaticamente valores sequenciais 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 valores gerados por esse objeto.

A propriedade IDENTITY

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

Uma semente opcional (valor inicial) e um incremento (valor de etapa) também podem ser especificados. Deixando de fora a semente e o incremento definirá ambos para 1.

Nota

A propriedade IDENTITY é especificada no lugar de especificar NULL ou NOT NULL na definição de coluna. Qualquer coluna com a propriedade IDENTITY não é automaticamente anulável. Você pode especificar NOT NULL apenas para autodocumentação, mas se especificar a coluna como NULL (significando anulável), a instrução de criação de tabela gerará um erro.

Apenas uma coluna em uma tabela pode ter a propriedade IDENTITY definida; é frequentemente usado como a CHAVE PRIMÁRIA 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 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
);

Nota

Os detalhes completos da instrução CREATE TABLE estão além do escopo deste módulo.

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 Identity estão isentas desse requisito.

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

ID promocional

PromotionName

StartDate

ID do Modelo do Produto

Desconto

Notas

1

Liquidação

2021-01-01T00:00:00

23

0.1

10% desconto

Quando a tabela foi criada, nenhum valor de semente ou incremento foi definido para a coluna IDENTITY, de modo que a primeira linha é inserida com um 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; Assim:

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, poderá usar a função IDENT_CURRENT, da seguinte forma:

SELECT IDENT_CURRENT('Sales.Promotion');

Valores de identidade prevalecentes

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

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 dentro de uma tabela. No entanto, a propriedade IDENTITY não é adequada para coordenar valores em várias tabelas dentro de 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. Ambos os tipos de venda podem precisar de um número de fatura exclusivo, e você pode querer evitar a duplicação do 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.

Repropagando uma coluna de identidade

Ocasionalmente, você precisará redefinir ou ignorar valores de identidade para a coluna. Para fazer isso, você estará "resemeando" 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 para 1 depois de excluir todas as linhas da tabela. Para obter detalhes completos sobre o uso do DBCC CHECKIDENT, consulte a documentação de referência do Transact-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 de sequência é criado usando a instrução CREATE SEQUENCE, opcionalmente fornecendo o tipo de dados (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 de uma sequência, use a construção NEXT VALUE FOR, da seguinte forma:

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

IDENTIDADE ou SEQUÊNCIA

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

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

  • SEQUENCE permite que você classifique os valores por outra coluna. A construção 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 cláusula ORDER BY da cláusula OVER. Essa funcionalidade também permite gerar números de linha para linhas à medida que elas são 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 para exibir, 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. A solicitação de valores de identidade poderia resultar em lacunas na série se outros processos fossem emitidos simultaneamente números. Você pode usar o procedimento do sistema sp_sequence_get_range para recuperar vários números na sequência de uma só vez.

  • SEQUENCE permite alterar a especificação da sequência, como o valor de incremento.

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