IDENTITY Wert überschreitet den Gültigkeitsbereich

By Frank Kalis

Posted on Aug 22, 2010 von in SQL Server

Die IDENTITY Eigenschaft ist eine oft genutzte Methode eine eindeutige Kennzeichnung eines Datensatzes zu erreichen. Sie wird in der Regel für eine Spalte vom Datentyp INTEGER verwendet. Damit stehen ca. 2,1 Mrd. Werte zur Verfügung. Doch was passiert eigentlich, wenn man an die Grenzen des INTEGER Gültigkeitsbereiches stößt...

Bevor wir uns der Ausgangsfrage widmen, zunächst ein kleines Repetitorium in Sachen IDENTITY Eigenschaft und numerische Datentypen im SQL Server.

Die IDENTITY Eigenschaft kann für Spalten vom Typ INTEGER oder DECIMAL mit Scale 0 definiert werden. Damit stehen folgende Wertebereiche zur Verfügung:

TINYINT 0 - 255
SMALLINT -32.768 - 32.767
INT -2.147.483.648 - 2.147.483.647
BIGINT -2^63 - 2^63-1

Darüber hinaus steht der DECIMAL Datentyp zur Verfügung. Und damit ein Gültigkeitsbereich von -10^38 - 10^38-1.

So, mit diesem Wissen im Hintergrund, können wir uns wieder der eigentlichen Frage widmen. Was passiert, wenn unser IDENTITY Wert Gefahr läuft, einen Gültigkeitsbereich zu überschreiten?

Ausprobieren bringt folgendes Ergebnis:

CREATE TABLE id_overflow
(
col1 INT identity(2147483647,1)
)
go
INSERT INTO id_overflow DEFAULT VALUES
INSERT INTO id_overflow DEFAULT VALUES
SELECT * FROM id_overflow
DROP TABLE id_overflow

(1 row(s) affected)

Server: Msg 8115, Level 16, State 1, Line 2
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

Obiges Skript erstellt eine Tabelle mit einer einzigen Spalte vom Typ INTEGER. Da wir jetzt keine Lust haben über 2 Mrd. Datensätze einzugeben, kürzen wir dies ab, indem wir den Startwert für die IDENTITY Eigenschaft auf den positiven Maximalwert für INTEGER setzen. Der erste eingefügte Datensatz erhält diesen Maximalwert zugeteilt. Nichts Ungewöhnliches passiert. Der Datensatz wird eingefügt. Der nächste Datensatz hingegen sprengt den Rahmen eines INTEGERs, verursacht also einen Überlauf und läßt den Batch abbrechen.

Also offensichtlich fängt SQL Server nicht an, von vorne zu zählen oder eventuell vorhandene Löcher in der IDENTITY Sequenz zu stopfen.

Was kann man nun in einem solchen Fall machen?
Am einfachsten ist es wohl, den Datentypen dieser Spalte auf BIGINT oder DECIMAL(38,0) zu ändern.

CREATE TABLE id_overflow
(
col1 INT IDENTITY(2147483647,1)
)
go
INSERT INTO id_overflow DEFAULT VALUES
ALTER TABLE id_overflow
ALTER COLUMN col1 BIGINT
INSERT INTO id_overflow DEFAULT VALUES
SELECT * FROM id_overflow
DROP TABLE id_overflow

col1
--------------------
2147483647
2147483648

(2 row(s) affected)

Weiss man bereits im Vorfeld, dass die Tabelle eine derart grosse Anzahl von Zeilen aufnehmen soll, kann man auch folgendes machen:

CREATE TABLE bigint_t
(
col1 BIGINT IDENTITY(-9223372036854775808, 1)
)
go
INSERT INTO bigint_t DEFAULT VALUES
SELECT * FROM bigint_t
DROP TABLE bigint_t

col1
--------------------
-9223372036854775808

(1 row(s) affected)

oder die DECIMAL(38,0) Variante:

CREATE TABLE decimal_t
(
col1 DECIMAL(38,0) IDENTITY(-99999999999999999999999999999999999999, 1)
)
GO
INSERT INTO decimal_t DEFAULT VALUES
SELECT * FROM decimal_t
DROP TABLE decimal_t

col1
----------------------------------------
-99999999999999999999999999999999999999

(1 row(s) affected)

Der Eine oder Andere mag sich vielleicht durch die negativen Zahlen in seinem ästhetischen Empfinden gestört fühlen, Fakt aber ist, daß dies einem dann für längere Zeit Ruhe geben sollte.

Dieser Eintrag wurde eingetragen von und ist abgelegt unter SQL Server. Tags: , ,

7 Kommentare

Benutzerwertungen
5 Stern:
 
(1)
4 Stern:
 
(0)
3 Stern:
 
(0)
2 Stern:
 
(0)
1 Stern:
 
(0)
1 Bewertung
Durschn. Benutzerwertung:
(5.0)
Interessant wäre jetzt noch ein Skript, welches alle Tabellen einer Datenbank untersucht, ob Gefahr besteht sich dem individuellen Grenzwert zu nähern. Wenn die Produktion erst mal steht, hat man selten die Ruhe um noch mal an solche hilfreichen Tipps zu denken...
24.08.10 @ 10:56
Gute Idee! Ein Quick and Dirty Basis-Skelett hänge ich gleich an.
24.08.10 @ 11:33
So, fertig. Das kann man natürlich noch mit SSRS hübschen und bunt machen. :-)
24.08.10 @ 11:36
Vielen Dank! Jetzt muss man nur noch die Ermittlung des Schemas ergänzen! SELECT s.name as SchemaName, O.name AS TabellenName, IC.name AS SpaltenName, T.name AS Datentyp, IC.seed_value, IC.increment_value, IC.last_value, CAST( CASE T.name WHEN 'bigint' THEN 9223372036854775807 WHEN 'int' THEN 2147483647 WHEN 'smallint' THEN 32767 WHEN 'tinyint' THEN 255 ELSE 0 END AS decimal(38, 0) ) - CAST(ISNULL(IC.last_value, 0) AS decimal(38, 0)) AS Puffer FROM sys.identity_columns IC JOIN sys.types T ON IC.system_type_id = T.system_type_id JOIN sys.objects O ON IC.object_id = O.object_id join sys.schemas s on o.schema_id = s.schema_id WHERE O.is_ms_shipped = 0 ORDER BY s.name, o.name;
27.08.10 @ 08:26
...und eingebaut :-)
27.08.10 @ 09:14
Ich habe jetzt das Skript etwas überarbeitet und die Information über den prozentuallen Verbrauch von Werten innerhalb des IDENTITY Bereiches eingebaut.
04.11.10 @ 11:33
Mann, Skript funktioniert richtig gut. Leider nur hat niemand es laufen lassen, bevor wir den Overflow hatten... :-)
19.03.19 @ 14:56


Formular wird geladen...