Início / dba / Perguntas / 106209
Accepted
Ege Ersoz
Asked: 2015-07-08 08:13:18 +0800 CST

Lacunas inesperadas na coluna IDENTIDADE

  • 772

Estou tentando gerar números de ordem de compra exclusivos que começam em 1 e incrementam em 1. Tenho uma tabela PONumber criada usando este script:

CREATE TABLE [dbo].[PONumbers]
(
  [PONumberPK] [int] IDENTITY(1,1) NOT NULL,
  [NewPONo] [bit] NOT NULL,
  [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(),
  CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC)    
);

E um procedimento armazenado criado usando este script:

CREATE PROCEDURE [dbo].[GetPONumber] 
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1);
    SELECT SCOPE_IDENTITY() AS PONumber;
END

No momento da criação, isso funciona bem. Quando o procedimento armazenado é executado, ele começa no número desejado e é incrementado em 1.

O estranho é que, se eu desligar ou hibernar meu computador, na próxima vez que o procedimento for executado, a sequência avançará quase 1.000.

Veja os resultados abaixo:

Números de pedidos

Você pode ver que o número saltou de 8 para 1002!

  • Por que isso está acontecendo?
  • Como posso garantir que os números não sejam ignorados assim?
  • Tudo o que preciso é que o SQL gere números que sejam:
    • a) Único garantido.
    • b) incrementar pelo valor desejado.

Admito que não sou um especialista em SQL. Eu entendi mal o que SCOPE_IDENTITY() faz? Devo usar uma abordagem diferente? Pesquisei as sequências no SQL 2012+, mas a Microsoft diz que não há garantia de que sejam exclusivas por padrão.

sql-server sql-server-2012

2 respostas

  1. Best Answer

    Este é um problema conhecido e esperado - a forma como as colunas IDENTITY são gerenciadas pelo SQL Server foi alterada no SQL Server 2012 ( algumas informações sobre o histórico ); por padrão, ele armazenará 1.000 valores em cache e, se você reiniciar o SQL Server, reiniciar o servidor, fizer failover etc., ele terá que descartar esses 1.000 valores, porque não terá uma maneira confiável de saber quantos deles foram realmente publicado. Isso está documentado aqui . Há um sinalizador de rastreamento que altera esse comportamento de forma que cada atribuição de IDENTITY seja registrada*, evitando essas lacunas específicas (mas não lacunas de reversões ou exclusões); no entanto, é importante observar que isso pode ser muito caro em termos de desempenho, portanto, nem vou mencionar o sinalizador de rastreamento específico aqui.

    * (Pessoalmente, acho que esse é um problema técnico que poderia ser resolvido de maneira diferente, mas como não escrevo o mecanismo, não posso mudar isso.)

    Para ser claro sobre como IDENTITY e SEQUENCE funcionam:

    • Nenhum dos dois é garantido como único (você precisa impor isso no nível da tabela, usando uma chave primária ou restrição exclusiva)
    • Nenhum dos dois é garantido como sem lacunas (qualquer reversão ou exclusão, por exemplo, produzirá uma lacuna, apesar desse problema específico)

    A exclusividade é fácil de impor. Evitar lacunas não é. Você precisa determinar o quão importante é para você evitar essas lacunas (em teoria, você não deve se preocupar com as lacunas, pois os valores IDENTITY/SEQUENCE devem ser chaves substitutas sem sentido). Se for muito importante, você não deve usar nenhuma das implementações, mas sim lançar seu próprio gerador de sequência serializável (veja algumas ideias aqui , aqui e aqui ) - apenas observe que isso eliminará a simultaneidade.

    Muita informação sobre este "problema":

    • 30

  2. Este é um problema do SQL Server. Tudo o que você pode fazer é propagar novamente a coluna.

    exclua as entradas com id de coluna errado. Propagar novamente a identidade da coluna. E então a próxima entrada tem o ID adequado.

    Reseed Identity usando o seguinte comando sql: DBCC CHECKIDENT ('YOUR_TABLE_NAME', RESEED, 9)-- 9 é o último ID correto

    • -4

relate perguntas