☰

Die Bigint-Identitätsspalte springt von 17 auf 1004. Warum?

tag-icon • tag-icon sql-server
Die Bigint-Identitätsspalte springt von 17 auf 1004. Warum?

Ich verwende Bigint-Identitätsspalten in mehreren Tabellen und stelle fest, dass sie ständig von niedrigen zu hohen Zahlen springen. In diesem Fall von 17 auf 1004, in anderen Tabellen jedoch in den 10000er-Bereich. Wenn dieses Verhalten anhält, bin ich innerhalb weniger tausend Datensätze am Bigint-Grenzwert angelangt! Warum passiert das und wie kann ich es beheben?

Screenshot hier:

Antwort1

Bigint reicht von -9.223.372.036.854.775.808 bis 9.223.372.036.854.775.807. Ich denke, Sie haben noch eine Weile Zeit, bis Sie die Obergrenze erreichen – selbst wenn Sie 100.000 Datensätze auf einmal hochspringen, könnten Sie eine Milliarde Sprünge machen und trotzdem nicht an der Obergrenze kratzen. Wenn Sie immer noch besorgt sind, ändern Sie Ihre Identität so, dass sie im negativen Bereich beginnt, und wir sind alle tot, bevor Sie sich Sorgen machen müssen.

Warum weist ein Identitätswert Lücken auf?

Eine Anwendung versucht, einen Datensatz in Ihre Tabelle einzufügen, und eine Nummer wird aus dem Pool entfernt. Aus Gründen der referenziellen Integrität schlägt das Einfügen fehl oder das aufrufende Programm macht das durchgeführte Einfügen rückgängig (Rollback). In beiden Fällen befindet sich der Identitätswert nicht mehr im Kandidatenpool. Ja, selbst wenn die Transaktion zurückgesetzt wird, ist die Nummer erschöpft und wird nicht zurückgesetzt.

Testen Sie selbst

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;

Auf der Registerkarte „Ergebnisse“ sehen Sie 1, 1, 3, 4 und 5, da dies die verwendeten Zahlen waren. Da wir jedoch 4 zurücksetzen, sehen die übernommenen Ergebnisse der Tabelle #Demo folgendermaßen aus:

demoid  CantBeNull
1       First Entry
3       Third Entry
5       Fifth Entry

Ich mag dieses Verhalten nicht

So funktioniert die Identitätseigenschaft – es gibt keine Möglichkeit, sie zu ändern. Dasselbe gilt, wenn Sie zum Sequenzobjekt wechseln (neu in 2012).

Wenn die Lücken Anlass zur Sorge geben, würde ich versuchen, herauszufinden, welcher Prozess sie verbraucht. Wenn es sich um Verstöße gegen die referenzielle Integrität (RI) handelt, verschärfen Sie die Validierung, bevor Sie versuchen, zu speichern. Wenn die Grundursache darin besteht, dass Transaktionen zurückgesetzt werden, führen Sie ein zweiphasiges Commit oder etwas Ähnliches durch – Speichern in Stage.MyTable. Wenn Identitätserschöpfung ein Problem darstellt, wechseln Sie zu einem SEQUENCE-Objekt und aktivieren Sie die Zyklenfunktion. Abfragen Sie diese Tabelle regelmäßig auf festgeschriebene Transaktionen und fügen Sie sie dann von dort in dbo.MyTable ein. Dadurch sollte die Möglichkeit von Lücken in Ihrem Ersatzschlüssel verringert werden.

Kendra KleinDas schöne Poster mit den Isolationsstufen hilft mir dabei, mich daran zu erinnern, welche Isolationsstufe ich zum Überprüfen brauche, was wirklich, wirklich festgeschrieben ist.

Bildbeschreibung hier eingeben

verwandte Informationen