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

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