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.
Observação
A propriedade IDENTITY é especificada no lugar de especificar NULL ou NOT NULL na definição de coluna. Qualquer coluna com a propriedade IDENTITY é automaticamente não anulável. Você pode especificar NOT NULL apenas para auto-documentação, mas, se você especificar a coluna como NULL (o que significa anulável), a instrução de criação de tabela gerará um erro.
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
);
Observação
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 – 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:
PromotionID
PromotionName
StartDate
ProductModelID
Desconto
Observações
1
Liquidação
2021-01-01T00:00:00
23
0,1
Desconto de 10%
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 será necessário habilitar as inserções de identidade, usando-se 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 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 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 do Transact-SQL.
SEQUENCE
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 Nome 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. A solicitação de valores de identidade poderia resultar em intervalos na série se fossem emitidos números para outros processos 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.