Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Eine Sequenz ist ein benutzerdefiniertes schemagebundenes Objekt, das eine Sequenz numerischer Werte gemäß der Spezifikation generiert, mit der die Sequenz erstellt wurde. Die Abfolge numerischer Werte wird in einer aufsteigenden oder absteigenden Reihenfolge in einem definierten Intervall generiert und kann auf Wunsch wiederholt werden. Sequenzen sind im Gegensatz zu Identitätsspalten nicht tabellen zugeordnet. Eine Anwendung bezieht sich auf ein Sequenzobjekt, um den nächsten Wert zu erhalten. Die Beziehung zwischen Sequenzen und Tabellen wird von der Anwendung gesteuert. Benutzeranwendungen können auf ein Sequenzobjekt verweisen und die Werteschlüssel über mehrere Zeilen und Tabellen hinweg koordinieren.
Eine Sequenz wird unabhängig von den Tabellen mithilfe der CREATE SEQUENCE-Anweisung erstellt. Mit den Optionen können Sie die inkrementellen, maximalen und minimalen Werte, den Ausgangspunkt, die Funktion für automatische Neustarts und das Zwischenspeichern steuern, um die Leistung zu verbessern. Informationen zu den Optionen finden Sie unter CREATE SEQUENCE.
Im Gegensatz zu Identitätsspaltenwerten, die beim Einfügen von Zeilen generiert werden, kann eine Anwendung die nächste Sequenznummer abrufen, bevor sie die Zeile einfügen, indem sie die FUNKTION NEXT VALUE FOR aufruft. Die Sequenznummer wird zugewiesen, wenn NEXT VALUE FOR aufgerufen wird, auch wenn die Zahl nie in eine Tabelle eingefügt wird. Die FUNKTION NEXT VALUE FOR kann als Standardwert für eine Spalte in einer Tabellendefinition verwendet werden. Verwenden Sie sp_sequence_get_range , um einen Bereich mehrerer Sequenznummern gleichzeitig abzurufen.
Eine Sequenz kann als beliebiger ganzzahliger Datentyp definiert werden. Wenn der Datentyp nicht angegeben ist, wird standardmäßig eine Sequenz verwendet bigint.
Verwenden von Sequenzen
Verwenden Sie Sequenzen anstelle von Identitätsspalten in den folgenden Szenarien:
Die Anwendung erfordert eine Zahl, bevor das Einfügen in die Tabelle erfolgt.
Die Anwendung erfordert die Verwendung einer einzigartigen Zahlenreihe zwischen mehreren Tabellen oder Spalten innerhalb einer Tabelle.
Die Anwendung muss die Nummernreihe neu starten, wenn eine angegebene Zahl erreicht ist. Beispielsweise beginnt die Anwendung nach dem Zuweisen von Werten 1 bis 10 erneut, Werte 1 bis 10 zuzuweisen.
Für die Anwendung müssen Sequenzwerte nach einem anderen Feld sortiert werden. Die FUNKTION NEXT VALUE FOR kann die OVER-Klausel auf den Funktionsaufruf anwenden. Die OVER-Klausel garantiert, dass die zurückgegebenen Werte in der Reihenfolge der ORDER BY-Klausel der OVER-Klausel generiert werden.
Für eine Anwendung müssen mehrere Nummern gleichzeitig zugewiesen werden. 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. Das Aufrufen von sp_sequence_get_range kann mehrere Nummern in der Sequenz gleichzeitig abrufen.
Sie müssen die Spezifikation der Sequenz ändern, z. B. den Inkrementwert.
Einschränkungen
Im Gegensatz zu Identitätsspalten, deren Werte nicht geändert werden können, werden Sequenzwerte nach dem Einfügen in die Tabelle nicht automatisch geschützt. Um zu verhindern, dass Sequenzwerte geändert werden, verwenden Sie einen Aktualisierungstrigger in der Tabelle, um Änderungen rückgängig zu machen.
Die Eindeutigkeit wird nicht automatisch für Sequenzwerte erzwungen. Die Möglichkeit, Sequenzwerte wiederzuverwenden, ist beabsichtigt. Wenn Sequenzwerte in einer Tabelle eindeutig sein müssen, erstellen Sie einen eindeutigen Index für die Spalte. Wenn Sequenzwerte in einer Tabelle in einer Gruppe von Tabellen eindeutig sein müssen, erstellen Sie Trigger, um Duplikate zu verhindern, die durch Aktualisierungsanweisungen oder Sequenznummernzyklen verursacht werden.
Das Sequenzobjekt generiert Zahlen gemäß seiner Definition, aber das Sequenzobjekt steuert nicht, wie die Zahlen verwendet werden. In eine Tabelle eingefügte Sequenznummern können Lücken aufweisen, wenn eine Transaktion zurückgesetzt wird, wenn ein Sequenzobjekt von mehreren Tabellen gemeinsam genutzt wird oder wenn Sequenznummern zugewiesen werden, ohne sie in Tabellen zu verwenden. Wenn sie mit der CACHE-Option erstellt wird, kann ein unerwartetes Herunterfahren, wie ein Stromausfall, dazu führen, dass die Sequenznummern im Cache verloren gehen.
Wenn mehrere Instanzen der NEXT VALUE FOR Funktion denselben Sequenzgenerator in einer einzelnen Transact-SQL-Anweisung angeben, geben alle diese Instanzen denselben Wert für eine bestimmte Zeile zurück, die von dieser Transact-SQL-Anweisung verarbeitet wird. Dieses Verhalten entspricht dem ANSI-Standard.
Typische Verwendung
Zum Erstellen einer ganzzahligen Sequenznummer, die um 1 von -2.147.483.648 auf 2.147.483.647 erhöht wird, verwenden Sie die folgende Anweisung.
CREATE SEQUENCE Schema.SequenceName
AS int
INCREMENT BY 1 ;
Verwenden Sie die folgende Anweisung, um eine ganzzahlige Sequenznummer wie eine Identitätsspalte zu erstellen, die um 1 von 1 auf 2.147.483.647 erhöht wird.
CREATE SEQUENCE Schema.SequenceName
AS int
START WITH 1
INCREMENT BY 1 ;
Verwalten von Sequenzen
Weitere Informationen zu Sequenzen erhalten Sie durch Abfragen von sys.sequences.
Beispiele
Weitere Beispiele finden Sie in den Themen CREATE SEQUENCE (Transact-SQL), NEXT VALUE FOR (Transact-SQL) und sp_sequence_get_range.
Ein. Verwenden einer Sequenznummer in einer einzelnen Tabelle
Im folgenden Beispiel wird ein Schema namens "Test", eine Tabelle mit dem Namen "Orders" und eine Sequenz mit dem Namen "CountBy1" erstellt und anschließend Zeilen mithilfe der FUNKTION NEXT VALUE FOR in die Tabelle eingefügt.
--Create the Test schema
CREATE SCHEMA Test ;
GO
-- Create a table
CREATE TABLE Test.Orders
(OrderID int PRIMARY KEY,
Name varchar(20) NOT NULL,
Qty int NOT NULL);
GO
-- Create a sequence
CREATE SEQUENCE Test.CountBy1
START WITH 1
INCREMENT BY 1 ;
GO
-- Insert three records
INSERT Test.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR Test.CountBy1, 'Tire', 2) ;
INSERT test.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR Test.CountBy1, 'Seat', 1) ;
INSERT test.Orders (OrderID, Name, Qty)
VALUES (NEXT VALUE FOR Test.CountBy1, 'Brake', 1) ;
GO
-- View the table
SELECT * FROM Test.Orders ;
GO
Dies ist die Ergebnismenge.
OrderID Name Qty
1 Tire 2
2 Seat 1
3 Brake 1
B. Aufrufen von NEXT VALUE FOR vor dem Einfügen einer Zeile
Mit der Orders in Beispiel A erstellten Tabelle deklariert das folgende Beispiel eine Variable mit dem Namen @nextIDund verwendet dann die FUNKTION NEXT VALUE FOR, um die Variable auf die nächste verfügbare Sequenznummer festzulegen. Es wird angenommen, dass die Anwendung eine gewisse Verarbeitung der Bestellung durchführt, wie etwa den Kunden die OrderID Nummer ihrer potenziellen Bestellung zur Verfügung stellt und anschließend die Bestellung überprüft. Unabhängig davon, wie lange diese Verarbeitung dauern kann oder wie viele andere Bestellungen während des Vorgangs hinzugefügt werden, wird die ursprüngliche Nummer für die Verwendung durch diese Verbindung beibehalten. Schließlich fügt die INSERT Anweisung der Tabelle die Orders Reihenfolge hinzu.
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR Test.CountBy1;
-- Some work happens
INSERT Test.Orders (OrderID, Name, Qty)
VALUES (@NextID, 'Rim', 2) ;
GO
C. Verwenden einer Sequenznummer in mehreren Tabellen
In diesem Beispiel wird davon ausgegangen, dass ein Produktionslinienüberwachungsprozess Benachrichtigungen über Ereignisse empfängt, die während des gesamten Workshops auftreten. Jedes Ereignis erhält eine eindeutige und monoton steigende EventID Zahl. Alle Ereignisse verwenden dieselbe EventID Sequenznummer, sodass Berichte, die alle Ereignisse kombinieren, jedes Ereignis eindeutig identifizieren können. Die Ereignisdaten werden jedoch je nach Ereignistyp in drei verschiedenen Tabellen gespeichert. Im Codebeispiel wird ein Schema namens Audit, eine Sequenz mit dem Namen EventCounterund drei Tabellen erstellt, die jeweils die EventCounter Sequenz als Standardwert verwenden. Anschließend fügt das Beispiel den drei Tabellen Zeilen hinzu und fragt die Ergebnisse ab.
CREATE SCHEMA Audit ;
GO
CREATE SEQUENCE Audit.EventCounter
AS int
START WITH 1
INCREMENT BY 1 ;
GO
CREATE TABLE Audit.ProcessEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EventCode nvarchar(5) NOT NULL,
Description nvarchar(300) NULL
) ;
GO
CREATE TABLE Audit.ErrorEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EquipmentID int NULL,
ErrorNumber int NOT NULL,
EventDesc nvarchar(256) NULL
) ;
GO
CREATE TABLE Audit.StartStopEvents
(
EventID int PRIMARY KEY CLUSTERED
DEFAULT (NEXT VALUE FOR Audit.EventCounter),
EventTime datetime NOT NULL DEFAULT (getdate()),
EquipmentID int NOT NULL,
StartOrStop bit NOT NULL
) ;
GO
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (248, 0) ;
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (72, 0) ;
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (2735,
'Clean room temperature 18 degrees C.') ;
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (18, 'Spin rate threashold exceeded.') ;
INSERT Audit.ErrorEvents (EquipmentID, ErrorNumber, EventDesc)
VALUES (248, 82, 'Feeder jam') ;
INSERT Audit.StartStopEvents (EquipmentID, StartOrStop)
VALUES (248, 1) ;
INSERT Audit.ProcessEvents (EventCode, Description)
VALUES (1841, 'Central feed in bypass mode.') ;
-- The following statement combines all events, though not all fields.
SELECT EventID, EventTime, Description FROM Audit.ProcessEvents
UNION SELECT EventID, EventTime, EventDesc FROM Audit.ErrorEvents
UNION SELECT EventID, EventTime,
CASE StartOrStop
WHEN 0 THEN 'Start'
ELSE 'Stop'
END
FROM Audit.StartStopEvents
ORDER BY EventID ;
GO
Dies ist die Ergebnismenge.
EventID EventTime Description
1 2009-11-02 15:00:51.157 Start
2 2009-11-02 15:00:51.160 Start
3 2009-11-02 15:00:51.167 Clean room temperature 18 degrees C.
4 2009-11-02 15:00:51.167 Spin rate threshold exceeded.
5 2009-11-02 15:00:51.173 Feeder jam
6 2009-11-02 15:00:51.177 Stop
7 2009-11-02 15:00:51.180 Central feed in bypass mode.
D. Das Generieren von wiederholten Sequenznummern in einer Ergebnismenge
Das folgende Beispiel veranschaulicht zwei Funktionen von Sequenznummern: Zyklisches Durchlaufen und Verwenden von NEXT VALUE FOR in einer Select-Anweisung.
CREATE SEQUENCE CountBy5
AS tinyint
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE ;
GO
SELECT NEXT VALUE FOR CountBy5 AS SurveyGroup, Name FROM sys.objects ;
GO
E. Generieren von Sequenznummern für ein Resultset mithilfe der OVER-Klausel
Im folgenden Beispiel wird die OVER Klausel verwendet, um das Resultset Name nach dem Hinzufügen der Sequenznummernspalte zu sortieren.
USE AdventureWorks2012 ;
GO
CREATE SCHEMA Samples ;
GO
CREATE SEQUENCE Samples.IDLabel
AS tinyint
START WITH 1
INCREMENT BY 1 ;
GO
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
F. Zurücksetzen der Sequenznummer
Beispiel E hat die ersten 79 der Samples.IDLabel Sequenznummern verbraucht. (Ihre Version von AdventureWorks2012 kann eine andere Anzahl von Ergebnissen zurückgeben.) Führen Sie Folgendes aus, um die nächsten 79 Sequenznummern zu verwenden (80 bis 158).
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
Führen Sie die folgende Anweisung aus, um die Samples.IDLabel Sequenz neu zu starten.
ALTER SEQUENCE Samples.IDLabel
RESTART WITH 1 ;
Führen Sie die Select-Anweisung erneut aus, um zu überprüfen, ob die Samples.IDLabel Sequenz mit Zahl 1 neu gestartet wurde.
SELECT NEXT VALUE FOR Samples.IDLabel OVER (ORDER BY Name) AS NutID, ProductID, Name, ProductNumber FROM Production.Product
WHERE Name LIKE '%nut%' ;
G. Umwandeln einer Tabelle von einer Identity-Spalte zu einer Sequenz
Im folgenden Beispiel wird ein Schema und eine Tabelle erstellt, die drei Zeilen für das Beispiel enthält. Anschließend fügt das Beispiel eine neue Spalte hinzu und legt die alte Spalte ab.
-- Create a schema
CREATE SCHEMA Test ;
GO
-- Create a table
CREATE TABLE Test.Department
(
DepartmentID smallint IDENTITY(1,1) NOT NULL,
Name nvarchar(100) NOT NULL,
GroupName nvarchar(100) NOT NULL
CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED
(DepartmentID ASC)
) ;
GO
-- Insert three rows into the table
INSERT Test.Department(Name, GroupName)
VALUES ('Engineering', 'Research and Development');
GO
INSERT Test.Department(Name, GroupName)
VALUES ('Tool Design', 'Research and Development');
GO
INSERT Test.Department(Name, GroupName)
VALUES ('Sales', 'Sales and Marketing');
GO
-- View the table that will be changed
SELECT * FROM Test.Department ;
GO
-- End of portion creating a sample table
--------------------------------------------------------
-- Add the new column that does not have the IDENTITY property
ALTER TABLE Test.Department
ADD DepartmentIDNew smallint NULL
GO
-- Copy values from the old column to the new column
UPDATE Test.Department
SET DepartmentIDNew = DepartmentID ;
GO
-- Drop the primary key constraint on the old column
ALTER TABLE Test.Department
DROP CONSTRAINT [PK_Department_DepartmentID];
-- Drop the old column
ALTER TABLE Test.Department
DROP COLUMN DepartmentID ;
GO
-- Rename the new column to the old columns name
EXEC sp_rename 'Test.Department.DepartmentIDNew',
'DepartmentID', 'COLUMN';
GO
-- Change the new column to NOT NULL
ALTER TABLE Test.Department
ALTER COLUMN DepartmentID smallint NOT NULL ;
-- Add the unique primary key constraint
ALTER TABLE Test.Department
ADD CONSTRAINT PK_Department_DepartmentID PRIMARY KEY CLUSTERED
(DepartmentID ASC) ;
-- Get the highest current value from the DepartmentID column
-- and create a sequence to use with the column. (Returns 3.)
SELECT MAX(DepartmentID) FROM Test.Department ;
-- Use the next desired value (4) as the START WITH VALUE;
CREATE SEQUENCE Test.DeptSeq
AS smallint
START WITH 4
INCREMENT BY 1 ;
GO
-- Add a default value for the DepartmentID column
ALTER TABLE Test.Department
ADD CONSTRAINT DefSequence DEFAULT (NEXT VALUE FOR Test.DeptSeq)
FOR DepartmentID;
GO
-- View the result
SELECT DepartmentID, Name, GroupName
FROM Test.Department ;
-- Test insert
INSERT Test.Department (Name, GroupName)
VALUES ('Audit', 'Quality Assurance') ;
GO
-- View the result
SELECT DepartmentID, Name, GroupName
FROM Test.Department ;
GO
Transact-SQL Anweisungen, die SELECT * verwenden, erhalten die neue Spalte als letzte Spalte anstelle der ersten. Wenn dies nicht akzeptabel ist, müssen Sie eine völlig neue Tabelle erstellen, die Daten in die Tabelle verschieben und dann die Berechtigungen für die neue Tabelle neu erstellen.