SQL Server - Tratando a coluna Identity

SQL Server - Tratando a coluna Identity

Hoje veremos como tratar a coluna Identity usada nas tabelas dos bancos de dados SQL Server.

O que é uma coluna Identity no SQL Server ?

As colunas do tipo Identity podem ser usadas para gerar valores inteiros que são auto-incrementais

A propriedade Identity em uma coluna garante o seguinte comportamento para aquela coluna:

Cada novo valor é gerado baseado nos valores da semente e incremento atuais definidos;
Cada novo valor para uma transação específica é diferente de outras transações simultâneas na tabela;

A propriedade Identity é definida para a coluna na criação da tabela e neste momento você define a numeração inicial para a coluna e o incremento a ser utilizado.

Sintaxe:

IDENTITY [ (seed , increment) ]

onde:

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;

Nota: Quando você usa o Entity Framework Core e define uma coluna como chave primeira com o tipo int esta coluna vai ser automaticamente definida com a propriedade Identity com valor inicial igual a 1 e incremento de 1.

Vamos abrir o SQL Server Management Studio e criar uma tabela Cliente com três colunas: ClienteId, Nome e Email em um banco de dados Teste já existente.

use Teste;
CREATE TABLE [dbo].[Cliente](
	[ClienteId] [int] IDENTITY(1,1) NOT NULL,
	[Nome] [varchar](100) NULL,
	[Email] [varchar](200) NULL
 CONSTRAINT [PK_Usuario] PRIMARY KEY CLUSTERED (ClienteId)
)

Observe que a coluna ClienteId foi definida como sendo do tipo int e a propriedade IDENTITY(1,1) com semente(seed) definida com o valor 1 e o incremento (increment) também com o valor 1.

Após executar a consulta para criar a tabela podemos verificar o resultado obtido:

Agora se eu incluir 3 registros nesta tabela serão gerados valores sequenciais para a coluna ClienteId iniciando com 1. Assim teremos 1, 2 e 3:

Excluindo todos os registros da tabela e incluindo mais dois registros veremos que o valor da coluna ClienteId para o primeiro registro será igual a 4 e não igual a 1, e o próximo registro vai continuar a seqüência de 1 em 1:

Como faça para retornar à numeração inicial

Para 'zerar' o valor da propriedade Identity para o início basta executar o comando:

DBCC CHECKIDENT('Cliente', RESEED, 0)

Agora ao incluir novos registros o primeiro terá o valor de ClienteId igual a 1 e assim por diante...

Definindo um valor para a numeração inicial

E se você desejar que o primeiro valor da coluna ClienteId comece em 100 e não em 1 ?

É claro que você pode fazer isso ao criar a tabela definindo o valor da propriedade Identity assim : IDENTITY(100,1)

Para tabelas que já foram criadas , para alterar o valor da semente (seed) use o comando:

DBCC CHECKIDENT('Cliente', RESEED, 100)

Agora ao incluir um novo registro teremos o primeiro valor de ClienteId igual a 101:

Em ambos os exemplos usados no artigo usamos o comando DBCC CHECKIDENT cuja sintaxe é a seguinte:

-- Syntax for SQL Server and Azure SQL Database

DBCC CHECKIDENT
(
    table_name  
        [, { NORESEED | { RESEED [, new_reseed_value ] } } ]  
)  
[ WITH NO_INFOMSGS ]

onde temos que :

table_name - É o nome da tabela sobre a qual verificar o valor de identidade atual. A tabela especificada deve conter uma coluna Identity;
NORESEED - Especifica que o valor de identidade atual não deve ser alterado.
RESEED - Especifica que o valor de identidade atual deve ser alterado;
new_reseed_value - O novo valor a ser usado como o valor atual da coluna de identidade;
WITH NO_INFOMSGS - Suprime todas as mensagens informativas;

Podemos realizar diversas operações usando este comando como por exemplo:

Exibir o valor da Identity atual

Forçar o valor do Identity atual para um novo valor em uma tabela com dados

O exemplo a seguir força o valor do Identity na coluna ClienteId na tabela Cliente para um valor de 10.

Como vemos, a tabela tem linhas existentes, a próxima linha inserida usará 11 como o valor, isto é, o valor da identidade atual definido para a coluna mais 1 (que é o valor de incremento da coluna).

Ao incluir um novos registros nesta tabela teremos o seguinte resultado:

E estamos conversados...

"O entendimento para aqueles que o possuem, é uma fonte de vida, mas a instrução dos tolos é a sua estultícia."
Provérbios 16:22

Referências:

José Carlos Macoratti