Por que Identity e Sequence deixam lacunas no SQL Server

Um valor identity ausente não prova que alguém excluiu uma linha. O SQL Server pode atribuir um número a uma inserção posteriormente revertida, sem desfazer a atribuição. Tratar identity como um contador sem lacunas gera falsos alertas e pode incentivar tentativas perigosas de reaproveitar identificadores.

Uma chave técnica responde a qual linha está sendo referenciada. Um número de negócio pode representar a posição de um documento em um processo de emissão. Esses requisitos têm ciclos de vida distintos. Separá-los impede que detalhes do armazenamento definam silenciosamente a política de numeração.

Observar alocação e confirmação separadamente

O exemplo usa uma tabela temporária e mantém a primeira linha confirmada para mostrar claramente a sequência.

CREATE TABLE #Tickets (
    TicketId int IDENTITY(1,1) PRIMARY KEY,
    Note nvarchar(80) NOT NULL
);
INSERT #Tickets(Note) VALUES (N'First committed row');
BEGIN TRANSACTION;
INSERT #Tickets(Note) VALUES (N'This row is rolled back');
ROLLBACK TRANSACTION;
INSERT #Tickets(Note) VALUES (N'Next committed row');
SELECT TicketId, Note FROM #Tickets ORDER BY TicketId;
DROP TABLE #Tickets;

Os identificadores restantes são 1 e 3. O número 2 foi atribuído à inserção revertida. Não falta uma linha confirmada: a transação removeu corretamente sua gravação, enquanto o mecanismo de alocação continuou.

IDENTITY pertence a uma tabela. SEQUENCE é um objeto de esquema independente, cujos valores podem ser pedidos antes da inserção e compartilhados entre tabelas. Isso ajuda quando a chave precisa ser conhecida antecipadamente, mas também torna normais os números alocados e não utilizados. Rollback não recupera valores de sequência.

O cache melhora a eficiência e pode criar lacunas adicionais quando valores reservados são perdidos em uma parada inesperada. Desativá-lo reduz essa causa específica, mas não devolve números de operações revertidas ou abandonadas. NO CACHE não garante numeração sem lacunas.

Separe ainda geração e unicidade. Uma chave primária ou restrição única deve proteger o identificador armazenado. Reseed, inserções explícitas de identity e sequências cíclicas podem causar colisões sem essa proteção. São ações de migração controlada, não consertos rotineiros de números ausentes.

Recuperar os valores realmente gerados

Ler MAX(Id) e somar um não prevê o próximo número com segurança. Outra sessão pode inserir entre a leitura e a escrita. A diferença entre maior identificador e quantidade de linhas também não mede exclusões de forma confiável.

Em uma inserção de uma linha, SCOPE_IDENTITY retorna a última identidade gerada no escopo atual. @@IDENTITY pode refletir uma identidade criada por um gatilho em outro escopo. Para várias linhas, OUTPUT inserted.Id devolve as chaves reais, mas sua ordem não deve ser associada automaticamente à ordem de entrada. Use uma correlação explícita.

Valores retornados por OUTPUT não comprovam que a transação inteira confirmou. Uma falha posterior ou rollback pode desfazer a gravação. Notificações externas devem acompanhar a conclusão confirmada do negócio e possuir recuperação para resultados de commit desconhecidos.

A ordem dos identificadores também não equivale à ordem dos commits. Uma transação pode receber o número menor e confirmar depois de outra. Relatórios precisam de horário explícito e contrato de ordenação; consumidores que não podem perder alterações precisam de um mecanismo de captura apropriado.

Planejar capacidade e numeração de negócio

A consulta lista colunas identity e os últimos valores alocados.

SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName,
       OBJECT_NAME(object_id) AS TableName,
       name AS ColumnName,
       TYPE_NAME(user_type_id) AS DataType,
       seed_value, increment_value, last_value
FROM sys.identity_columns
ORDER BY SchemaName, TableName;

Monitore o intervalo restante conforme tipo, valor inicial, direção do incremento e taxa de alocação. Uma identity int positiva tem teto finito, e inserções malsucedidas também consomem números. Excluir linhas antigas não aumenta essa capacidade.

A migração para bigint pode afetar chaves estrangeiras, índices adicionais, parâmetros, exportações e tipos na aplicação. Planeje-a antes do esgotamento em vez de tratá-la como uma alteração emergencial de coluna.

Se o negócio exige uma sequência documental controlada, atribua o número no momento correto de emissão, armazene-o separadamente e defina como representar cancelamentos. Um contador transacional pode serializar a atribuição e limitar a vazão. Dividi-lo por cliente, categoria ou período só é válido quando a regra de negócio permite.

Teste emissão concorrente, cancelamento, rollback e repetições próximas ao commit. A garantia útil é uma política explicada e aplicada, não a aparência de valores consecutivos em uma coluna técnica que nunca foi projetada para prometê-los.

Referências técnicas: Microsoft Learn: IDENTITY property · Microsoft Learn: CREATE SEQUENCE · Microsoft Learn: OUTPUT clause.