Nieoczekiwane luki w kolumnie TOŻSAMOŚĆ

Próbuję wygenerować unikalne numery zamówień zakupu, które zaczynają się od 1 i zwiększają o 1. Mam tabelę PONumber utworzoną za pomocą tego skryptu:

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)    
);

I procedura składowana utworzona za pomocą tego skryptu:

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

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

W momencie tworzenia działa to dobrze. Po uruchomieniu procedura przechowywana rozpoczyna się od żądanej liczby i zwiększa się o 1.

Dziwne jest to, że jeśli wyłączę lub przejdę w stan hibernacji komputera, to przy następnym uruchomieniu procedury sekwencja zwiększy się o prawie 1000.

Zobacz wyniki poniżej:

Widać, że liczba wzrosła z 8 do 1002!

• Dlaczego to się dzieje?
• Jak mogę się upewnić, że liczby nie są pomijane w ten sposób?
• Potrzebuję tylko, aby SQL wygenerował liczby, które są:
  • a) Gwarantowany niepowtarzalny.
  • b) przyrost o żądaną kwotę.

Przyznaję, że nie jestem ekspertem od SQL. Czy źle rozumiem, co robi SCOPE_IDENTITY ()? Czy powinienem stosować inne podejście? Sprawdziłem sekwencje w SQL 2012+, ale Microsoft twierdzi, że domyślnie nie ma gwarancji, że będą unikalne.

Jest to znany i oczekiwany problem - sposób, w jaki SQL Server zarządza kolumnami TOŻSAMOŚCI, zmienił się w SQL Server 2012 ( część tła ); domyślnie buforuje 1000 wartości, a jeśli zrestartujesz SQL Server, uruchomisz ponownie serwer, przełączysz w tryb awaryjny itp., będzie musiał wyrzucić te 1000 wartości, ponieważ nie będzie miał wiarygodnego sposobu, aby dowiedzieć się, ile z nich faktycznie było wydany. Jest to udokumentowane tutaj . Istnieje flaga śledzenia, która zmienia to zachowanie tak, że każde przypisanie TOŻSAMOŚCI jest rejestrowane *, zapobiegając tym konkretnym lukom (ale nie lukom w przywracaniu lub usuwaniu); Należy jednak zauważyć, że może to być dość kosztowne z punktu widzenia wydajności, więc nawet nie wspomnę tutaj o konkretnej fladze śledzenia.

* (Osobiście uważam, że jest to problem techniczny, który można rozwiązać inaczej, ale ponieważ nie piszę silnika, nie mogę tego zmienić).

Aby wyjaśnić, jak działa TOŻSAMOŚĆ i SEKWENCJA:

• Żadne z nich nie jest unikalne (musisz wymusić to na poziomie tabeli, używając klucza podstawowego lub unikalnego ograniczenia)
• Żadna z nich nie jest gwarantowana bez przerw (na przykład jakiekolwiek wycofanie lub usunięcie spowoduje powstanie luki, pomimo tego konkretnego problemu)

Unikalność jest łatwa do wyegzekwowania. Unikanie luk nie jest. Musisz określić, jak ważne jest unikanie tych luk (teoretycznie nie powinieneś przejmować się lukami, ponieważ wartości TOŻSAMOŚĆ / SEKWENCJA powinny być pozbawionymi znaczenia kluczami zastępczymi). Jeśli jest to bardzo ważne, nie powinieneś używać żadnej z implementacji, a raczej rzucić własny generowalny generator sekwencji (zobacz kilka pomysłów tutaj , tutaj i tutaj ) - pamiętaj, że zabije to współbieżność.

Wiele informacji o tym „problemie”:

• SQL Server 2012 przyrost tożsamości kolumny przeskakuje z 6 do 1000+ przy 7. pozycji
• przyrost tożsamości przeskakuje w bazie danych serwera SQL
• Wartość kolumny tożsamości nagle przeskakuje do 1001 na serwerze sql

To jest problem SQL Servera. Wszystko, co możesz zrobić, to ponownie ustawić kolumnę.

usuń wpisy z niewłaściwym identyfikatorem kolumny. Ponownie sprawdź tożsamość kolumny. A następnie następny wpis ma odpowiedni identyfikator.

Reseed Identity przy użyciu następującego polecenia sql: DBCC CHECKIDENT ('YOUR_TABLE_NAME', RESEED, 9)- 9 to ostatni poprawny identyfikator