O problema dos IDs ausentes

Boa Noite Pessoal,

Dando continuidade às dicas e truques passados no meu último Webcast, falarei hoje sobre o problema dos IDs ausentes. Quem nunca se deparou com uma tabela com uma coluna Identity "furada". Inicia-se no 01, passa-se para o 02, 03 e de repente aparece o 07. Esse problema ocorre por conta do funcionamento do Identity. Como a definição do próximo Identity não é transacional, caso um ocorra um ROLLBACK o próximo Identity é utilizado deixando-se um GAP. Atividades de replicação, comando de DELETE e a utilização da instrução SET IDENTITY_INSERT também podem produzir GAPs em colunas Identity.

O objetivo não é descrever o funcionamento do Identity. Já o fiz em um artigo publicado no Plugmasters (SQL Server: Geração de seqüenciais de forma automática). Descreverei aqui como localizar os IDs que ficaram de fora, ou seja, se temos uma seqüência 1, 2, 3, 4 e 6 é sinal de que o ID 5 ficou fora da seqüência tornando-se portanto um ID ausente já que logicamente deveria estar entre o 4 e o 6.

Para demonstrar como listar os IDs ausentes, utilizaremos um script propositalmente com GAPs entre os IDs. Os IDs ausentes estão em intervalos bem espaçados propositalmente, pois, demonstrarei alguns outros truques.

CREATE TABLE tblProdutos (
    ProdutoID INT, CategoriaID INT, ProdutoNome VARCHAR(100))

INSERT INTO tblProdutos VALUES (01,2,‘Câmera digital’)
INSERT INTO tblProdutos VALUES (03,2,‘Pen Drive’)
INSERT INTO tblProdutos VALUES (07,2,‘Monitor LCD’)
INSERT INTO tblProdutos VALUES (13,3,‘Luva’)
INSERT INTO tblProdutos VALUES (14,2,‘MP3 Player’)
INSERT INTO tblProdutos VALUES (20,3,‘Meia’)
INSERT INTO tblProdutos VALUES (25,1,‘Maçã’)

Considerando que existem apenas seis produtos, podemos observar uma quantidade considerável de IDs ausentes. Visualmente pode-se notar que estão ausentes os IDs 2, 4, 5, 6, 8, 9, 10, 11, 12, 15, 16, 17, 18, 19, 21, 22, 23 e 24.

No artigo "Criando uma tabela com uma seqüência de números" demonstrei como criar uma tabela que produz uma seqüência numérica até um determinado intervalo. Farei utilização da função previamente criada nesse artigo de modo que para que o script abaixo funcione, é necessário criar previamente a função apresentada no artigo.

— Determinar o maior ID
DECLARE @MaxIDProduto INT
SET @MaxIDProduto = (SELECT MAX(ProdutoID) FROM tblProdutos)
SELECT @MaxIDProduto AS MaiorID

— Produzir a seqüência numérica até o maior ID
DECLARE @MaxIDProduto INT
SET @MaxIDProduto = (SELECT MAX(ProdutoID) FROM tblProdutos)
SELECT Num FROM dbo.FNRetNum(@MaxIDProduto)

Uma vez que a seqüência produzida até o maior ID da tabela de produtos seja retornada pela função, basta consultar o que está na seqüência que não esteja na tabela e conseguiremos os IDs ausentes. Um simples NOT IN consegue produzir esse resultado, mas por questões de desempenho e boas práticas sou mais adepto do NOT EXISTS.

DECLARE @MaxIDProduto INT
SET @MaxIDProduto = (SELECT MAX(ProdutoID) FROM tblProdutos)

— Verifica os IDs ausentes (NOT IN)
SELECT Num FROM dbo.FNRetNum(@MaxIDProduto)
WHERE Num NOT IN (SELECT ProdutoID FROM tblProdutos)

— Verifica os IDs ausentes (NOT EXISTS)
SELECT Num FROM dbo.FNRetNum(@MaxIDProduto) AS F
WHERE NOT EXISTS (
    SELECT ProdutoID FROM tblProdutos AS P
    WHERE F.Num = P.ProdutoID)

Ambas as consultas retornam a relação de todos os IDs ausentes que constituem 19 IDs no total. Ter o número isoladamente é importante, mas algumas soluções de apresentação sugerem algo um pouco mais apresentável. Poderíamos ao invés de retornar os 19 IDs categorizá-los por intervalos. Podemos notar que entre o ID 1 e o 3 existe o ID ausente 2, entre o ID 7 e o 13 existem 5 IDs contínuos, entre o ID 14 e o 20 existem mais 5 IDs contínuos e por último entre o ID 20 e o 25 mais quatro IDs contínuos. Talvez uma forma melhor de apresentar os IDs ausentes pudesse ser expressa conforme a tabela abaixo:

Início

Término

2

2

4

6

8

12

15

19

21

24

De fato a apresentação melhora bastante principalmente quando há muito IDs ausentes de forma contínua, mas inegavelmente o desenvolvimento de uma consulta desse tipo torna-se bem mais difícil seja pela complexidade de uma instrução SELECT ou pela perda de desempenho na tentadora vontade de utilizar um curosor. Embora os cursores possam tornar o desenvolvimento dessa consulta consideravelmente mais fácil demonstrarei como fazê-lo através de uma instrução SELECT.

O primeiro passo é determinar onde se iniciam os GAPs. Se observarmos, o GAP inicia sempre que o próximo número está ausente. O ID 3 está presente, mas o ID 4 não. O ID 4 então é o início de um GAP e os próximos números representam um GAP até que a seqüência seja novamente preenchida. O ID 7 está presente, mas o ID 8 não. O ID 8 então é o início de um novo GAP que dura até o ID 13, o mesmo se aplica para os IDs 14 e 20. O GAP se inicia sempre uma unidade a mais do último ID presente. Poderíamos então adicionar uma unidade aos IDs presentes e observar o resultado.

SELECT ProdutoID + 1 FROM tblProdutos

O resultado produziu os IDs 2, 4, 8, 14, 15, 21 e 26. Essa é uma lista muito próxima da exibida acima exceto pelos valores 14 e 26. O valor 14 é o resultado da soma do ID 13 + 1. Para os IDs 1, 3 e 7 de fato bastou somar uma unidade para localizar o início de um GAP, mas o mesmo não foi verdadeiro para o ID 14 uma vez que coincidentemente ele é um ID presente. Podemos repetir a consulta só que devemos eliminar os resultados (ID + 1) que realmente existam. Isso envolverá comparar a tabela com ela mesma. Uma possível solução é o uso do NOT EXISTS. Ex:

SELECT ProdutoID + 1 AS Inicio FROM tblProdutos AS P1
WHERE NOT EXISTS
(SELECT * FROM tblProdutos AS P2
WHERE P1.ProdutoID + 1 = P2.ProdutoID)

O resultado desconsiderou o ID 14. O ID 14 é produzido somando-se o ID 13 + 1, mas como 14 é um ID existente, a consulta com o NOT EXISTS o eliminou. Quando somou-se o ID 14 + 1 e produziu-se 15, o mesmo não foi eliminado, pois, o ID não existe originalmente na tabela de produtos. O ID 26 (obtido da soma do ID 25 + 1) necessita ser eliminado, pois, o ID 26 constitui um ID que é superior ao ID 25 (maior ID da tabela) e não pode ser considerado um ID ausente. Para eliminá-lo basta colocar uma condição que impeça que o ID do Produto seja maior que o maior ID da tabela.

SELECT ProdutoID + 1 AS Inicio FROM tblProdutos AS P1
WHERE NOT EXISTS
(SELECT * FROM tblProdutos AS P2
WHERE P1.ProdutoID + 1 = P2.ProdutoID) AND
ProdutoID < (SELECT MAX(ProdutoID) FROM tblProdutos)

Essa consulta consegue relacionar onde todos os GAPs se iniciam (a coluna de início da tabela). Agora é necessário montar a coluna de término. A lógica para calcular essa coluna não é tão complexa quanto possa parecer. O GAP deve terminar antes que o próximo ID se inicie. Se um GAP começa no ID 8 e o próximo GAP se inicia no ID 15 sabemos que o primeiro GAP termina em alguma posição entre 8 e 15. O GAP poderia terminar no 14, mas ocorre que o 14 é um ID presente. Pode-se tentar o primeiro número menor que o 14 mas o número 13 também é um ID presente. Se 13 é um ID válido, tenta-se então o próximo número menor que 13 no caso o 12. Como 12 não é um ID presente ele determina o fim do GAP.

Há uma outra forma de se chegar ao valor 12 (necessária inclusive para facilitar a escrita da solução final). Façamos a seguinte pergunta, dada a lista de IDs, qual é o menor número que seja maior que o ID da lista ? Sim, admito que a pergunta ficou confusa, mas se expressarmos em forma de consulta talvez fique mais fácil.

SELECT P1.ProdutoID AS ProdutoID_P1,
(SELECT MIN(P2.ProdutoID) FROM tblProdutos AS P2
WHERE P2.ProdutoID > P1.ProdutoID) AS ProdutoID_P2
FROM tblProdutos AS P1

Através da consulta obtivemos o seguinte resultado:

Linha

ProdutoID_P1

ProdutoID_P2

01

1

3

02

3

7

03

7

13

04

13

14

05

14

20

06

20

25

07

25

NULL

A coluna ProdutoID_P1 retorna a lista de todos os IDs e a coluna ProdutoID_P2 retorna a lista de todos os IDs imediatamente superiores, ou seja, ProdutoID_P2 representa o menor ID na tabela que seja superior a ProdutoID_P1. Se adicionarmos 1 unidade a coluna ProdutoID_P1 iremos obter a primeira lista com os números 2, 4, 8, 14, 21 e 26 obtida anteriormente. Essa é exatamente a lista com o início dos GAPs excetuando-se os números 14 e 26 (14 é um ID presente e 26 é um ID inexistente). Se combinarmos essa consulta com a consulta anterior (que restringe os números 14 e 26) chegaremos bem perto da resposta.

SELECT ProdutoID + 1 AS Inicio,
  (SELECT MIN(P2.ProdutoID) FROM tblProdutos AS P2
   WHERE P2.ProdutoID > P1.ProdutoID) AS Termino
FROM tblProdutos AS P1
WHERE NOT EXISTS
  (SELECT * FROM tblProdutos AS P2
   WHERE P2.ProdutoID = P1.ProdutoID + 1)
  AND ProdutoID < (SELECT MAX(ProdutoID) FROM tblProdutos AS P1)

A consulta conseguiu reproduzir quase que completamente a tabela de início e fim dos GAPs. O único detalhe é que a coluna de término está com o valor dos próximos IDs presentes. Onde um ID se inicia é porque um GAP termina, mas o GAP termina um número antes. Basta então subtrair uma unidade da segunda coluna e chegaremos a solução final.

SELECT ProdutoID + 1 AS Inicio,
  (SELECT MIN(P2.ProdutoID) FROM tblProdutos AS P2
   WHERE P2.ProdutoID > P1.ProdutoID) – 1 AS Termino
FROM tblProdutos AS P1
WHERE NOT EXISTS
  (SELECT * FROM tblProdutos AS P2
   WHERE P2.ProdutoID = P1.ProdutoID + 1)
  AND ProdutoID < (SELECT MAX(ProdutoID) FROM tblProdutos AS P1)

Essa é uma forma de apresentar o resultado de uma forma mais "elegante" além da solução ser compatível com o SQL Server 2000 e possivelmente outros SGBDs ANSI92 Compliance. Em todo caso, aos curiosos, há uma solução mais simples (2005 based) em SQL Server 2005 – Missing Ranges.

Acho que por hoje é só…

[ ]s,

Gustavo

Deixe uma resposta

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