A coluna de identidade Bigint salta de 17 para 1004. Por quê?

Question

Estou usando colunas de identidade bigint em diversas tabelas e percebo que elas continuam saltando de números baixos para números altos. Neste caso, de 17 a 1.004, mas em outras tabelas até 10.000. Se esse comportamento continuar, estarei no limite de bigint dentro de alguns milhares de registros! Por que isso está acontecendo e como posso consertar?

Captura de tela aqui:

Answer 1

Bigint varia de -9.223.372.036.854.775.808 a 9.223.372.036.854.775.807. Acho que você ainda tem um tempo antes de atingir os limites superiores - mesmo se estiver saltando 100 mil registros por vez, você pode ter um bilhão de saltos e ainda assim não ultrapassar os limites superiores. Se você ainda estiver preocupado, propague novamente sua identidade para começar na faixa negativa e todos estaremos mortos antes que você precise se preocupar com as coisas.

Por que um valor de identidade tem lacunas?

Um aplicativo tenta inserir um registro na sua tabela e um número é removido do pool. Por motivos de integridade referencial, a inserção falha ou o programa de chamada desfaz (reverte) a inserção transacionada - de qualquer forma, o valor da identidade não está mais no conjunto de candidatos. Sim, mesmo que a transação seja revertida, o número se esgotou e não será revertido.

Teste você mesmo

SET NOCOUNT ON;
IF OBJECT_ID('tempdb..#Demo') IS NOT NULL
BEGIN
    DROP TABLE #Demo;
END

CREATE TABLE #Demo
(
    demoid int IDENTITY(1,1) NOT NULL
,   CantBeNull varchar(50) NOT NULL
);

-- add a record and look at our identity
INSERT INTO #Demo
    (CantBeNull)
VALUES
    ('First Entry');
SELECT SCOPE_IDENTITY() AS GeneratedId;

-- GO forces the batch to commit (assuming default implicit transaction model)
GO
-- Violate RI
INSERT INTO #Demo
    (CantBeNull)
VALUES
    (NULL);
SELECT SCOPE_IDENTITY() AS RIViolationGeneratedId;
GO
-- add a record and look at our identity
INSERT INTO #Demo
    (CantBeNull)
VALUES
    ('Third Entry');
SELECT SCOPE_IDENTITY() AS ThirdGeneratedId;
GO
-- Transaction consumes

BEGIN TRAN
INSERT INTO #Demo
    (CantBeNull)
VALUES
    ('Fourth Entry');
SELECT SCOPE_IDENTITY() AS FourthGeneratedId;
ROLLBACK;

INSERT INTO #Demo
    (CantBeNull)
VALUES
    ('Fifth Entry');

SELECT SCOPE_IDENTITY() AS FifthGeneratedId;
GO
SELECT D.* FROM #Demo AS D;

Na guia de resultados, você verá 1, 1, 3, 4 e 5, pois esses eram os números usados, mas como revertemos 4, os resultados confirmados da tabela #Demo parecerão

demoid  CantBeNull
1       First Entry
3       Third Entry
5       Fifth Entry

Eu não gosto desse comportamento

É assim que funciona a propriedade de identidade - não há como alterá-la. O mesmo vale se você mudar para o objeto Sequence (novo em 2012).

Se as lacunas forem uma preocupação, eu procuraria identificar qual processo as está consumindo. Se houver violações de integridade referencial (RI), aperte a validação antes de tentar salvar. Se a causa raiz for a reversão das transações, faça um commit em duas fases ou algo parecido - Salvar em Stage.MyTable. Se o esgotamento da identidade for um problema, mude para um objeto SEQUENCE e ative o ciclo. Periodicamente, pesquise esta tabela para transações confirmadas e insira-a em dbo.MyTable a partir daí. Isso deve diminuir a oportunidade de existirem lacunas em sua chave substituta.

Kendra Pequenalindo pôster de nível de isolamento me ajuda a lembrar qual nível de isolamento eu preciso para verificar o que está realmente comprometido.