Compartilhar via

CREATE TABLE (Transact-SQL) IDENTITY (Propriedade)

  • Artigo

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Banco de Dados SQL no Microsoft Fabric

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 propriedade Identity do SQL-DMO que expõe a propriedade de identidade da linha de uma coluna.

Convenções de sintaxe de Transact-SQL

Sintaxe

IDENTITY [ (seed , increment) ]

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

Argumentos

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.

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 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.

    Observação

    O Azure Synapse Analytics não é compatível com a restrição PRIMARY KEY ou UNIQUE ou com o í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 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.

Apenas uma coluna de identidade pode ser criada por tabela.

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.

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.

Exemplos

R. 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.

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;

Conteúdo relacionado