CREATE TABLE (Transact-SQL) IDENTITY (Propriedade)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsArmazém no Microsoft FabricBase de dados SQL no Microsoft Fabric

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

Note

A propriedade IDENTITY é diferente da propriedade SQL-DMO Identity que expõe a propriedade row identity de uma coluna.

Transact-SQL convenções de sintaxe

Syntax

IDENTITY [ (seed , increment) ]

Arguments

seed

O valor usado para a primeira linha carregada na tabela.

increment

O valor incremental que é adicionado ao valor de identidade da linha anterior que foi carregada.

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

Remarks

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

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

• Cada novo valor é gerado com base na semente e incremento atuais.

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

A propriedade identity em uma coluna não garante as seguintes condições:

• Exclusividade do valor - A exclusividade deve ser imposta usando uma PRIMARY KEY restrição UNIQUE ou UNIQUE índice.

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

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

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

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

Se uma tabela com uma coluna de identidade for publicada para replicação, a coluna de identidade deverá ser gerenciada de 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 semente e o incremento devem ser definidos como 1, 1. Definir a semente ou incremento para um valor diferente resulta 1 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 identity é 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 identity.

Examples

A. Use a propriedade IDENTITY com CREATE TABLE

O exemplo a seguir cria uma nova tabela usando a IDENTITY propriedade para um número de identificação de incremento automático.

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 encontrar lacunas nos valores de identidade

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

Note

A primeira parte do seguinte Transact-SQL script foi concebida apenas para ilustração. Você pode executar o script Transact-SQL que começa 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 DBCC (Transact-SQL)
• IDENT_INCR (Transact-SQL)
• @@IDENTITY (Transact-SQL)
• IDENTIDADE (função) (Transact-SQL)
• IDENT_SEED (Transact-SQL)
• CONJUNTO IDENTITY_INSERT (Transact-SQL)
• Replicar colunas de identidade
• sys.identity_columns