IDENTITY (propriedade) (Transact-SQL)

Artigo
10/31/2013

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

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

IDENTITY [ (seed , increment) ]

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.

Você deve especificar a semente e o incremento, 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 & atual de semente.
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 deve ser imposta por meio de uma restrição PRIMARY KEY ou UNIQUE, ou do índice UNIQUE.
Valores consecutivos em uma transação – uma transação que insere várias linhas não é garantia para obter valores consecutivos para as linhas porque as demais inserções simultâneas podem ocorrer na tabela. Se os valores precisam ser consecutivos, a transação deve 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 em cache valores de identidade 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 os intervalos não são aceitáveis, o aplicativo deve usar um gerador de sequência com a opção NOCACHE ou usar seu próprio mecanismo para gerar valores de chave.
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.

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