CREATE TABLE (Transact-SQL) IDENTITY (Propriedade)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsWarehouse no Microsoft FabricBanco de dados SQL no Microsoft Fabric

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

Note

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

Convenções de sintaxe de Transact-SQL

Syntax

IDENTITY [ (seed , increment) ]

Arguments

seed

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

increment

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

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

Remarks

As colunas de identidade podem ser usadas para gerar valores de chave.

A propriedade de identidade em uma coluna garante as seguintes condições:

• Cada novo valor é gerado com base no incremento e na propagação atuais.

• 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 as seguintes condições:

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

• 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 propagação/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 à atribuição de tabela e 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.

Em tabelas com otimização de memória, a propagação e o incremento devem ser definidos para 1, 1. Definir a propagação ou o incremento para um valor diferente de 1 resulta no seguinte erro: The use of seed and increment values other than 1 is not supported with memory optimized tables.

Apenas uma coluna de identidade pode ser criada por tabela.

Depois que a propriedade de identidade é definida em uma coluna, ela não pode ser removida. O tipo de dados pode ser alterado desde que o novo tipo de dados seja compatível com a propriedade de identidade.

Examples

A. Use 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 AdventureWorks2022;
GO
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. Use 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.

Note

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;

Conteúdo relacionado

• ALTERAR TABELA (Transact-SQL)
• CRIAR TABELA (Transact-SQL)
• VERIFICADOR DE DBCC (Transact-SQL)
• IDENT_INCR (Transact-SQL)
• @@IDENTITY (Transact-SQL)
• IDENTITY (função) (Transact-SQL)
• IDENT_SEED (Transact-SQL)
• SET IDENTITY_INSERT (Transact-SQL)
• Replicar colunas de identidade
• sys.identity_columns