Utilizando a propriedade Identity Insert e DBCC CHECKIDENT

É comum hoje encontramos em entidades de um banco de dados colunas que são auto incremento que é definida pela propriedade IDENTITY com os seus respectivos valores seqüenciados incorretamente, isso ocorre devido a eventos de exclusão de registros na entidade, e isso gerará intervalos indesejáveis, se esta for uma preocupação, não use a propriedade IDENTITY.

por José Heberton Vilela de Melo

Olá Pessoal,

É comum hoje encontramos em entidades de um banco de dados colunas que são auto incremento que é definida pela propriedade IDENTITY com os seus respectivos valores seqüenciados incorretamente, isso ocorre devido a eventos de exclusão de registros na entidade, e isso gerará intervalos indesejáveis, se esta for uma preocupação, não use a propriedade IDENTITY.

Entretanto, para que nenhum intervalo seja criado ou para preencher um intervalo existente causado por uma exclusão, podemos avaliar os valores de identidade existentes antes de inserir valores explicitamente, ai você me pergunta, inserir valores manualmente em uma coluna que é auto incremento? Sim, isso mesmo, isso é possível realizar utilizando a propriedade SET IDENTITY_INSERT para ON.

Digamos que criamos uma entidade com uma coluna que será auto incremento, inserimos 4 registros nela e teremos a seguinte população na entidade.

Agora se excluímos os dois últimos registros e inserir dois novos registros teríamos a seguinte população na entidade.

Agora observe que nossa entidade possui um intervalo do segundo registro para o terceiro, isso é totalmente desagradável para quem gosta de manter seus registros organizados na sua base de dados, e para corrigir esse tipo de problema mostrarei dois exemplos que ajudará a reorganizar os registros da coluna IdCliente.

Para melhor entendermos, nada melhor que demonstrar na prática.

-- CRIANDO UMA ENTIDADE TEMPORARIA

CREATE TABLE #TbIdentity

                (

                                IdCliente INT IDENTITY(1,1)

                                               PRIMARY KEY

                               ,NoCliente VARCHAR(35) NOT NULL

                               ,DaCadastro DATE

                                               DEFAULT(GETDATE())

                )

GO

-- POPULANDO A ENTIDADE #TbIdentity COM 4 REGISTRO

INSERT INTO #TbIdentity(NoCliente, DaCadastro)

                VALUES ("Nome1",GETDATE())

                                 ,("Nome2",GETDATE())

                                 ,("Nome3",GETDATE())

                                 ,("Nome4",GETDATE())

-- RECUPERANDO AS INFORMAÇÕES DA ENTIDADE #TbIdentity

SELECT * FROM #TbIdentity

Nesse momento teremos a seguinte seituação na entidade temporaria.

Agora vamos excluir os dois últimos registros e inserir mais dois novos registros.

-- REMOVENDO 2 REGISTRO DA ENTIDADE #TbIdentity

DELETE FROM #TbIdentity WHERE IdCliente > 2

-- INSERINDO DOIS NOVOS REGISTROS

INSERT INTO #TbIdentity(NoCliente, DaCadastro)

                VALUES ("Nome5",GETDATE())

                                 ,("Nome6",GETDATE())

Nesse ponto teremos a seguinte situação na entidade temporaria.

Vamos agora inserir valores para que possamos preencher o intervalo entre o segundo registro e o terceiro registro, mas para isso temos que definir a propriedade SET IDENTITY_INSERT para ON, essa propriedade permite que seja inserido explicitamente valores em uma coluna definida com a propriedade IDENTITY, ou seja, auto incremento.

Vale lembrar que somente uma tabela em uma sessão pode ter a propriedade IDENTITY_INSERT definida como ON. Isso quer dizer que se uma entidade estiver com a propriedade definida como ON, e uma instrução SET IDENTITY_INSERT ON for emitida para outra entidade, o SQL Server retornará uma mensagem de erro informando que SET IDENTITY_INSERT já é ON.

-- DEFININDO A PROPRIEDADE IDENTITY_INSERT PARA ON

                              

SET IDENTITY_INSERT #TbIdentity ON

O valor default desta propriedade é OFF, e é precisso especificar o nome da tabela na qual irá receber o valor da propriedade.

-- INSERINDO NOVOS REGISTROS

INSERT INTO #TbIdentity(IdCliente ,NoCliente, DaCadastro)

                VALUES (3,"Nome5",GETDATE())

                                 ,(4,"Nome6",GETDATE())

Observe que depois de definida a propriedade para ON é precisso explicitar o nome da coluna e os valores explicitamente no comando INSERT, como mostra o exemplo T-SQL anterior.

Agora podemos observar o intervalo que tinhamos na entidade # TbIdentity entre o segundo registro e o terceiro registro foi preenchido corretamente comforme mostra a tabela a seguir.

Uma observação importante é que se for explicito um valor maior que o valor de identidade atual na entidade, o SQL Server usará esse novo valor como o valor de identitade atual, ou seja, se for explicito o valor 10 no comando INSERT e você torna o valor da propriedade IDENTITY_INSERT para OFF e inserir um novo valor, o valor da coluna identidade receberá o valor 11 como mostra o exemplo a seguir.

-- INSERINDO NOVOS REGISTROS AINDA COM A PROPRIEDADE DEFINIDA PARA ON

INSERT INTO #TbIdentity(IdCliente ,NoCliente, DaCadastro)

                VALUES(10,"Nome10",GETDATE())

Teremos a seguinte situação.

Agora vamos definir a propriedade IDENTITY_INSERT para OFF

-- DEFININDO A PROPRIEDADE IDENTITY_INSERT PARA OFF

                              

SET IDENTITY_INSERT #TbIdentity OFF

-- INSERINDO UM NOVO REGISTRO

INSERT INTO #TbIdentity(NoCliente, DaCadastro)

                VALUES("Nome11",GETDATE())

Depois de executado esse comando T-SQL teremos a seguinte situação comprovando a teoria explicada anteriormente.

Outra forma de podemos estar reorganizando a identidade de uma entidade especifica é utilizando a propriedade CHECKIDENT do comando DBCC, podendo ser definido um novo valor manualmente para uma coluna identidade de uma entidade.

Digamos que tenhamos uma entidade com uma coluna setada com o valor identidade, inserimos 4 registros nela e teremos a seguinte população na entidade.

Agora se excluímos os dois últimos registros e inserir dois novos registros teríamos a seguinte população na entidade.

Agora observe que nossa entidade possui um intervalo do segundo registro para o terceiro, isso é totalmente desagradável para quem gosta de manter seus registros organizados na sua base de dados conforme falado anteriormente, e uma outra forma de corrigir esse tipo de problema alem da propriedade IDENTITY seria a utilização da propriedade CHECKIDENT.

O primeiro passo é verificar o valor identidade da coluna da entidade com a propriedade CHECKIDENT.

-- VERIFICANDO A IDENTITIDADE DA COLUNA IDENTITADE

DBCC CHECKIDENT("#TbIdentity", NORESEED)

Checking identity information: current identity value "4", current column value "4".

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Ao executar o T-SQL acima, o SQL Server retornará uma mensagem nos mostrando o valor atual da identidade como quatro e o valor corrente também como quatro.

Observe que quem é responsável por mostrar o valor da identidade sem modificado é o argumento NORESEED especificado juntamente com a propriedade CHECKIDENT.

E para reorganizarmos esse valor para que não corramos o risco de ser criado intervalos entre os registros na entidade devemos especificar o argumento RESEED acompanhado de um valor que será substituído pelo valor da identidade atual, veremos no exemplo a seguir.

-- DEFININDO O VALOR DA IDENTITADE DA COLUNA IDENTITADE

DBCC CHECKIDENT("#TbIdentity", RESEED, 2)

Checking identity information: current identity value "4", current column value "2".

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Observe agora que o valor identidade corrente passa a ser justamente o que foi passado na expressão T-SQL.

Agora vamos inserir mais dois novos registros e observa o resultado do mesmo.

-- INSERINDO DOIS NOVOS REGISTROS

INSERT INTO #TbIdentity(NoCliente, DaCadastro)

      VALUES ("Nome3",GETDATE())

            ,("Nome4",GETDATE())

Agora teremos o seguinte resultado após a inserção dos valores na entidade #TbIdentity.

Espero ter sido bem sucinto quanto ao uso da propriedade SET IDENTITY_INSERT e DBCC CHECKIDENT.

A todos um bom estudo e até a próxima.