CREATE TABLE (Transact-SQL) IDENTITY (Propriedade)

Aplica-se a:yesSQL Server (todas as versões com suporte) YesBanco de Dados SQL do Azure YesInstância Gerenciada de SQL do Azure yesAzure Synapse Analytics

Cria uma coluna de identidade em uma tabela. Esta propriedade é usada com as instruções CREATE TABLE e ALTER TABLE Transact-SQL.

Observação

A propriedade IDENTITY é diferente da propriedadeIdentity de SQL-DMO que expõe a propriedade de identidade de linha de uma coluna.

Topic link iconConvenções de sintaxe do Transact-SQL

Sintaxe

IDENTITY [ (seed , increment) ]

Observação

Não há suporte a essa sintaxe para o pool de SQL sem servidor no Azure Synapse Analytics.

Observação

Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

seed
É o valor usado para a primeira linha carregada na tabela.

increment
É o valor de incremento adicionado ao valor de identidade da linha anterior que foi carregada.

Observação

No Azure Synapse Analytics, os valores da identidade não são incrementais devido à arquitetura distribuída do data warehouse. Confira Usar IDENTITY para criar chaves alternativas no pool de SQL do Synapse para obter mais informações.

Você deve especificar seed e increment, ou nenhum dos dois. Se nenhum for especificado, o padrão será (1,1).

Comentários

As colunas de identidade podem ser usadas para gerar valores de chave. A propriedade de identidade em uma coluna garante o seguinte:

  • Cada novo valor é gerado com base no incremento & semente atual.

  • Cada novo valor para uma transação específica é diferente de outras transações simultâneas na tabela.

A propriedade de identidade em uma coluna não garante o seguinte:

  • Exclusividade do valor – a exclusividade precisa ser imposta usando uma restrição PRIMARY KEY ou UNIQUE ou um índice UNIQUE.

Observação

O Azure Synapse Analytics não dá suporte à restrição de PRIMARY KEY ou UNIQUE nem ao índice UNIQUE. Confira Usar IDENTITY para criar chaves alternativas no pool de SQL do Synapse para obter mais informações.

  • Valores consecutivos em uma transação – não é garantido que uma transação que insere várias linhas obtenha valores consecutivos para as linhas porque outras inserções simultâneas podem ocorrer na tabela. Se os valores precisarem ser consecutivos, a transação deverá usar um bloqueio exclusivo na tabela ou usar o nível de isolamento SERIALIZABLE.

  • Valores consecutivos após o reinício do servidor ou outras falhas – o SQL Server pode armazenar valores de identidade em cache por questões de desempenho e alguns dos valores atribuídos podem ser perdidos durante uma falha de banco de dados ou uma reinicialização do servidor. Isso pode resultar em intervalos no valor de identidade após a inserção. Se não forem aceitos intervalos, o aplicativo deverá usar seu próprio mecanismo para gerar valores de chave. Usar um gerador de sequência com a opção NOCACHE pode limitar os intervalos de transações que nunca são confirmadas.

  • Reutilização de valores – para determinada propriedade de identidade com semente/incremento específico, os valores de identidade não são reutilizados pelo mecanismo. Se uma instrução de inserção específica falhar ou se a instrução de inserção for revertida, os valores de identidade consumidos serão perdidos e não serão gerados novamente. Isso pode resultar em intervalos quando os valores de identidade subsequentes são gerados.

Essas restrições são parte do design para melhorar o desempenho, e por serem aceitáveis em muitas situações comuns. Se você não pode usar valores de identidade devido a essas restrições, crie uma tabela separada contendo um valor atual e gerencie o acesso à tabela e a atribuição de número com seu aplicativo.

Se uma tabela com uma coluna de identidade for publicada para replicação, a coluna de identidade deverá ser gerenciada de uma forma apropriada para o tipo de replicação usado. Para obter mais informações, consulte Replicar colunas de identidade.

Apenas uma coluna de identidade pode ser criada por tabela.

Em tabelas com otimização de memória, a semente e o incremento devem ser definidos para 1.1. Configurar seed ou increment como um valor diferente de 1 resulta no seguinte erro: o uso de valores seed e increment diferentes de 1 não é compatível com tabelas com otimização de memória.

Exemplos

a. Usando a propriedade IDENTITY com CREATE TABLE

O exemplo a seguir cria uma nova tabela que usa a propriedade IDENTITY para um número de identificação automaticamente incrementando.

USE AdventureWorks2012;  
  
IF OBJECT_ID ('dbo.new_employees', 'U') IS NOT NULL  
   DROP TABLE new_employees;  
GO  
CREATE TABLE new_employees  
(  
 id_num int IDENTITY(1,1),  
 fname varchar (20),  
 minit char(1),  
 lname varchar(30)  
);  
  
INSERT new_employees  
   (fname, minit, lname)  
VALUES  
   ('Karin', 'F', 'Josephs');  
  
INSERT new_employees  
   (fname, minit, lname)  
VALUES  
   ('Pirkko', 'O', 'Koskitalo');  

B. Usando sintaxe genérica para localizar intervalos em valores de identidade

O exemplo a seguir mostra a sintaxe genérica para localizar intervalos em valores de identidade quando os dados são removidos.

Observação

A primeira parte do script Transact-SQL a seguir foi criada apenas para fins de ilustração. Você pode executar o script Transact-SQL que inicia com o comentário: -- Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.  
-- The illustrative example starts here.  
SET IDENTITY_INSERT tablename ON;  
DECLARE @minidentval column_type;  
DECLARE @maxidentval column_type;  
DECLARE @nextidentval column_type;  
SELECT @minidentval = MIN($IDENTITY), @maxidentval = MAX($IDENTITY)  
    FROM tablename  
IF @minidentval = IDENT_SEED('tablename')  
   SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')  
   FROM tablename t1  
   WHERE $IDENTITY BETWEEN IDENT_SEED('tablename') AND   
      @maxidentval AND  
      NOT EXISTS (SELECT * FROM tablename t2  
         WHERE t2.$IDENTITY = t1.$IDENTITY +   
            IDENT_INCR('tablename'))  
ELSE  
   SELECT @nextidentval = IDENT_SEED('tablename');  
SET IDENTITY_INSERT tablename OFF;  
-- Here is an example to find gaps in the actual data.  
-- The table is called img and has two columns: the first column   
-- called id_num, which is an increasing identification number, and the   
-- second column called company_name.  
-- This is the end of the illustration example.  
  
-- Create the img table.  
-- If the img table already exists, drop it.  
-- Create the img table.  
IF OBJECT_ID ('dbo.img', 'U') IS NOT NULL  
   DROP TABLE img;  
GO  
CREATE TABLE img (id_num INT IDENTITY(1,1), company_name sysname);  
INSERT img(company_name) VALUES ('New Moon Books');  
INSERT img(company_name) VALUES ('Lucerne Publishing');  
-- SET IDENTITY_INSERT ON and use in img table.  
SET IDENTITY_INSERT img ON;  
  
DECLARE @minidentval SMALLINT;  
DECLARE @nextidentval SMALLINT;  
SELECT @minidentval = MIN($IDENTITY) FROM img  
 IF @minidentval = IDENT_SEED('img')  
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')  
    FROM img t1  
    WHERE $IDENTITY BETWEEN IDENT_SEED('img') AND 32766 AND  
      NOT    EXISTS (SELECT * FROM img t2  
          WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img'))  
 ELSE  
    SELECT @nextidentval = IDENT_SEED('img');  
SET IDENTITY_INSERT img OFF;  

Consulte Também

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
DBCC CHECKIDENT (Transact-SQL)
IDENT_INCR (Transact-SQL)
@@IDENTITY (Transact-SQL)
IDENTITY (função) (Transact-SQL)
IDENT_SEED (Transact-SQL)
SELECT (Transact-SQL)
SET IDENTITY_INSERT (Transact-SQL)
Replicar colunas de identidade