Atualizando uma coluna IDENTITY

Galera,

Recebi uma dúvida hoje: “É possível atualizar uma coluna IDENTITY de um registro específico?”
Respondendo: a atualização direta com a utilização do comando UPDATE não é permitida. Mas, se você realmente precisa realizar este procedimento por algum motivo específico, há uma solução de contorno bem simples.

Primeiro vamos criar o cenário para realizarmos o procedimento.

  • Criação da tabela:

Captura de Tela 2016-01-14 às 20.41.20

  • Adição de registros:

Captura de Tela 2016-01-14 às 20.41.25

  • Consulta:

Captura de Tela 2016-01-14 às 20.42.26

Agora vamos fazer uma tentativa de atualização da coluna id criada como IDENTITY na tabela TesteIdentity:

Captura de Tela 2016-01-14 às 20.45.47
Como podemos ver, ocorreu o erro 8102 “Cannot update identity column ‘id'”, pois não é possível atualizarmos diretamente um campo com a propriedade IDENTITY.
Para resolvermos este problema vamos precisar apenas de dois passos:

  1. Deletar o registro que queremos atualizar (Não se esqueça de fazer backup da sua tabela);
  2. Inserir o registro novamente com o seu novo valor para a coluna ID utilizando a cláusula SET IDENTITY_INSERT [nomeTabela] ON.

Agora que já sabemos a solução, vamos tentar atualizar o mesmo registro do cenário criado anteriormente utilizando os passos acima.

  • Vamos excluir o registro com o valor de id = 1:

Captura de Tela 2016-01-14 às 20.46.24

  • E por último vamos inserir o registro com o seu novo valor para a coluna id utilizando a cláusula SET IDENTITY_INSERT [nomeTabela] ON:

Captura de Tela 2016-01-14 às 20.47.23

O resultado é a “atualização” do id = 1 para id = 20, conforme abaixo:

Captura de Tela 2016-01-15 às 01.43.38

  • Se esquecermos de utilizar a cláusula SET IDENTITY_INSERT [nomeTabela] ON, o SQL Server irá retornar o erro 544 “Cannot insert explicit value for identity column in table ‘TesteIdentity’ when IDENTITY_INSERT is set to OFF“:

Captura de Tela 2016-01-15 às 01.35.58

IMPORTANTE:

  • Nunca se esqueça de retornar a cláusula IDENTITY_INSERT para OFF, pois o SQL Server permite que apenas uma tabela por vez utilize esta opção. Caso esqueça de desligar esta propriedade para uma tabela e tente ativar para uma segunda tabela o SQL Server irá retornar o erro abaixo:Captura de Tela 2016-01-15 às 01.43.07
  • Se o valor inserido for maior que a IDENTITY atual da tabela, automaticamente o SQL Server irá alterar o IDENTITY para o valor inserido, como podemos ver na imagem abaixo ao inserir um novo valor após termos inserido o valor com a cláusula SET IDENTITY_INSERT ON:

Captura de Tela 2016-01-15 às 01.49.38

Captura de Tela 2016-01-15 às 01.50.01

  • Se for necessário visualizar ou alterar o valor atual da IDENTITY podemos utilizar o comando DBCC CHECKIDENT. No nosso caso vamos primeiramente conferir o valor atual da IDENTITY, alterá-lo para 30 e por último inserir um novo registro na tabela.

Conferindo o valor atual da IDENTITY. Notem que o valor atual é 21.

Captura de Tela 2016-01-15 às 02.01.38

Alterando o valor da IDENTITY para 30:

Captura de Tela 2016-01-15 às 02.02.06

Conferindo novamente o valor atual da IDENTITY. Notem que agora o valor atual foi alterado para 30, conforme comando executado anteriormente. O valor 21 que ainda permanece é referente ao valor máximo da coluna IDENTITY existente na tabela:

Captura de Tela 2016-01-15 às 02.02.20

Inserindo um novo registro na tabela TesteIdentity:

Captura de Tela 2016-01-15 às 02.02.43

Como podemos ver após alterarmos o valor da IDENTITY para 30, o próximo valor inserido foi o 31, conforme evidência abaixo:

Captura de Tela 2016-01-15 às 02.03.28

Por hoje é isso galera! Se tiverem dúvidas, críticas ou sugestões de assuntos podem enviar pelos comentários ou até mesmo por e-mail: felipe@lauffer.me

Obrigado!

Anúncios

Uma resposta em “Atualizando uma coluna IDENTITY

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s