Generieren automatischer Werte

• 3 Minuten

Möglicherweise müssen Sie sequenzielle Werte für eine Spalte in einer bestimmten Tabelle automatisch generieren. Transact-SQL bietet zwei Möglichkeiten dafür: Verwenden Sie die IDENTITY-Eigenschaft mit einer bestimmten Spalte in einer Tabelle, oder definieren Sie ein SEQUENCE-Objekt, und verwenden Sie Werte, die von diesem Objekt generiert werden.

IDENTITY-Eigenschaft

Um die IDENTITY-Eigenschaft zu verwenden, definieren Sie eine Spalte mit einem numerischen Datentyp mit einer Dezimalstellenzahl von 0 (d. h. nur ganze Zahlen), und schließen Sie das Schlüsselwort IDENTITY ein. Die zulässigen Typen umfassen alle Ganzzahltypen und Dezimaltypen, bei denen Sie explizit eine Dezimalstellenzahl von 0 angeben.

Ein optionaler Ausgangswert (Startwert) und ein Inkrementwert (Schrittwert) können ebenfalls angegeben werden. Wenn Sie den Startwert und den Inkrementwert auslassen, werden beide auf 1 festgelegt.

Die IDENTITY-Eigenschaft kann nur für eine Spalte in einer Tabelle festgelegt sein. Diese wird häufig entweder als Primärschlüssel (PRIMARY KEY) oder als alternativer Schlüssel verwendet.

Der folgende Code zeigt die Erstellung der Tabelle Sales.Promotion, die in den Beispielen im vorherigen Abschnitt verwendet wurde, diesmal jedoch mit einer Identitätsspalte namens PromotionID als Primärschlüssel:

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Einfügen von Daten in eine Identitätsspalte

Wenn die IDENTITY-Eigenschaft für eine Spalte definiert ist, geben INSERT-Anweisungen für die Tabelle im Allgemeinen keinen Wert für die IDENTITY-Spalte an. Die Datenbank-Engine generiert einen Wert anhand des nächsten verfügbaren Werts für die Spalte.

Sie können beispielsweise eine Zeile in die Tabelle Sales.Promotion einfügen, ohne einen Wert für die Spalte PromotionID anzugeben:

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Obwohl die VALUES-Klausel keinen Wert für die Spalte PromotionID enthält, müssen Sie keine Spaltenliste in der INSERT-Klausel angeben, da Identitätsspalten diese Anforderung nicht aufweisen.

Wenn diese Zeile die erste ist, die in die Tabelle eingefügt wird, ist das Ergebnis eine neue Zeile wie die folgende:

PromotionID

PromotionName

StartDate

ProductModelID

Discount

Notizen

1

Clearance Sale

2021-01-01T00:00:00

23

0,1

10% discount

Beim Erstellen der Tabelle wurden keine Ausgangs- oder Inkrementwerte für die IDENTITY-Spalte festgelegt. Daher wird die erste Zeile mit dem Wert 1 eingefügt. Der nächsten eingefügten Zeile wird ein PromotionID-Wert von 2 zugewiesen usw.

Abrufen eines Identitätswerts

Um den zuletzt zugewiesenen IDENTITY-Wert innerhalb derselben Sitzung und desselben Bereichs zurückzugeben, verwenden Sie die SCOPE_IDENTITY-Funktion wie folgt:

SELECT SCOPE_IDENTITY();

Die SCOPE_IDENTITY-Funktion gibt den zuletzt im aktuellen Bereich erzeugten Identitätswert für eine beliebige Tabelle zurück. Wenn Sie den neuesten Identitätswert in einer bestimmten Tabelle benötigen, können Sie die IDENT_CURRENT-Funktion wie folgt verwenden:

SELECT IDENT_CURRENT('Sales.Promotion');

Überschreiben von Identitätswerten

Wenn Sie den automatisch generierten Wert überschreiben und der IDENTITY-Spalte einen bestimmten Wert zuweisen möchten, müssen Sie zunächst mithilfe der Anweisung „SET IDENTITY INSERT Tabellenname ON“ Identitätseinfügungen aktivieren. Wenn diese Option aktiviert ist, können Sie wie für jede andere Spalte auch für die Identitätsspalte einen expliziten Wert einfügen. Anschließend können Sie mithilfe der Anweisung „SET IDENTITY INSERT Tabellenname OFF“ mit der Verwendung automatischer Identitätswerte fortfahren, wobei der zuletzt von Ihnen explizit eingegebene Wert als Startwert verwendet wird.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

Wie bereits erwähnt, wird mithilfe der IDENTITY-Eigenschaft eine Sequenz von Werten für eine Spalte in einer Tabelle generiert. Die IDENTITY-Eigenschaft eignet sich jedoch nicht für die Koordination von Werten über mehrere Tabellen innerhalb einer Datenbank. Angenommen, Ihre Organisation unterscheidet zwischen Direktverkäufen und Verkäufen an Handelspartner und möchte Daten für diese Verkäufe in separaten Tabellen speichern. Beide Verkaufstypen erfordern möglicherweise eine eindeutige Rechnungsnummer, und Sie möchten vermeiden, dass derselbe Wert bei zwei verschiedenen Verkaufstypen dupliziert wird. Eine Lösung für diese Anforderung ist die Verwendung eines Pools eindeutiger sequenzieller Werte für beide Tabellen.

Erneutes Seeding einer Identitätsspalte

Gelegentlich müssen Sie Identitätswerte für die Spalte zurücksetzen oder überspringen. Dazu wird für die Spalte mithilfe der Funktion DBCC CHECKIDENT das Seending erneut ausgeführt. Sie können diese verwenden, um viele Werte zu überspringen oder den nächsten Identitätswert auf 1 zurückzusetzen, nachdem Sie alle Zeilen in der Tabelle gelöscht haben. Ausführliche Informationen zur Verwendung von DBCC CHECKIDENT finden Sie in der Referenzdokumentation zu Transact-SQL.

SEQUENCE

In Transact-SQL können Sie ein Sequenzobjekt verwenden, um neue sequenzielle Werte unabhängig von einer bestimmten Tabelle zu definieren. Ein Sequenzobjekt wird mithilfe der CREATE SEQUENCE-Anweisung erstellt. Optional werden der Datentyp (muss ein ganzzahliger Typ, Dezimaltyp oder numerischer Typ mit einer Dezimalstellenzahl von 0 sein), der Startwert, ein Inkrementwert, ein Höchstwert und andere Optionen in Bezug auf die Leistung angegeben.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

Zum Abrufen des nächsten verfügbaren Werts aus einer Sequenz verwenden Sie das NEXT VALUE FOR-Konstrukt wie folgt:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY oder SEQUENCE

Bei der Entscheidung, ob IDENTITY-Spalten oder ein SEQUENCE-Objekt zum automatischen Auffüllen von Werten verwendet werden sollen, sind die folgenden Punkte zu beachten:

• Verwenden Sie SEQUENCE, wenn Ihre Anwendung das Freigeben einer einzelnen Reihe von Nummern zwischen mehreren Tabellen oder mehreren Spalten innerhalb einer Tabelle erfordert.

• MIT SEQUENCE können Sie die Werte nach einer anderen Spalte sortieren. Das NEXT VALUE FOR-Konstrukt kann die OVER-Klausel verwenden, um die Sortierspalte anzugeben. Die OVER-Klausel garantiert, dass die zurückgegebenen Werte in der Reihenfolge der ORDER BY-Klausel der OVER-Klausel generiert werden. Mit dieser Funktion können Sie auch Zeilennummern für Zeilen generieren, wenn diese in einer SELECT-Anweisung zurückgegeben werden. Im folgenden Beispiel wird die Tabelle Production.Product nach der Spalte Name sortiert, und die erste zurückgegebene Spalte ist eine sequenzielle Nummer.

  SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
      ProductID,
      Name
  FROM Production.Product;
  

  Obwohl in der vorherigen Anweisung nur anzuzeigende SEQUENCE-Werte ausgewählt wurden, werden die Werte dennoch „verbraucht“, und die angezeigten SEQUENCE-Werte sind nicht mehr verfügbar. Wenn Sie die oben gezeigte SELECT-Anweisung mehrmals ausführen, erhalten Sie jedes Mal unterschiedliche SEQUENCE-Werte.

• Verwenden Sie SEQUENCE, wenn für Ihre Anwendung mehrere Nummern gleichzeitig zugewiesen werden müssen. Eine Anwendung muss z. B. fünf sequenzielle Nummern reservieren. Wenn Identitätswerte angefordert werden, können Lücken in der Reihe entstehen, wenn andere Prozesse gleichzeitig Nummern ausgeben. Mithilfe der Systemprozedur sp_sequence_get_range können Sie mehrere Nummern in der Sequenz gleichzeitig abrufen.

• Mit SEQUENCE können Sie die Spezifikation der Sequenz ändern. z. B. den Inkrementwert.

• IDENTITY-Werte sind vor Aktualisierungen geschützt. Wenn Sie versuchen, eine Spalte mit der IDENTITY-Eigenschaft zu aktualisieren, wird ein Fehler zurückgegeben.