Anlegen & Abfragen

-- Listing 1

CREATE SEQUENCE [dbo].[SeqCounter] AS INT-- Zulässig: Alle Integer-Datentypen (Std.: BIGINT)

START WITH 1                             -- Ohne START WITH wird beim Minimalwert begonnen

INCREMENT BY 1                           -- Hinweis: Inkremente können auch negativ sein

Zusätzlich zum Abrufen per NEXT VALUE FOR hat man auch die Möglichkeit ganze Wertebereiche mit der Stored Procedure sp_sequence_get_range abzurufen. Hierzu möchte ich aber auf den entsprechenden MSDN-Artikel verweisen.

Sequences ändern

ALTER SEQUENCE [dbo].[SeqCounter]

RESTART WITH 10        -- nächsten Wert auf 10 einstellen

NO CYCLE               -- NO CYCLE = Bei Überschreitung von 10 wird eine Exception ausgelöst

                       -- CYCLE    = Bei Überschreitung von 10 wird vom MINVALUE neu
                                     begonnen

Nach Ausführung des Kommandos

SELECT NEXT VALUE FOR [dbo].[SeqCounter]

erscheint wie erwartet der Wert 10, der mit RESTART WITH festgelegt wurde.

Wenn ich das Kommando erneut ausführe, wird durch die Option NO CYCLE folgende Exception ausgelöst:

Msg 11728, Level 16, State 1, Line 1
The sequence object ‘SeqCounter’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Hätten wir statt NO CYCLE die Option CYCLE verwendet, wäre das Ergebnis 1 ausgegeben worden.

Sequences löschen

Wenn wir die Sequence nicht mehr benötigen, kann sie mit dem DROP-Statement entfernt werden.

-- Löschen eines Sequence-Objekts
DROP SEQUENCE [dbo].[SeqCounter]

Welche Sequences gibt es?

Über den INFORMATION_SCHEMA-Katalog (INFORMATION_SCHEMA.SEQUENCES) oder über den Systemkatalog (SYS.SEQUENCES) lassen sich alle im aktuellen Datenbankkontext angelegten Sequences abfragen.

Alternativ hat das SQL Server-Team Sequences als neuen Knoten unter “Programmability” bzw. “Programmierbarkeit” einer Datenbank aufgenommen. Dort kann man div. Einstellungen mit Hilfe der GUI vornehmen.

Transaktionales Verhalten

Sequences wissen nichts von Transaktionen, d.h. wenn man ein ROLLBACK durchführt, sind die innerhalb des Transaktionsblocks abgerufenen Sequence-Werte “verbraucht”, sodass ohne separates Eingreifen eine Lücke in der sequentiellen Werte-Reihenfolge entsteht.

Caching

Die Caching-Option ist der größte Unterschied zu gewöhnlichen IDENTITY-Columns. Während bei Identitätsspalten per Default 20 Werte im Cache liegen (dieser Wert ist fest, differiert u.U. jedoch von Version zu Version), lässt sich das bei SEQUENCES nach individuellem Bedarf steuern. Standardmäßig wird ein Cache von 50 Werten verwendet, sofern weder die Option CACHE noch NO CACHE angegeben wird.

Mit Angabe der CACHE-Option hat man somit direkten Einfluss auf I/O-Vorgänge. Aber wie funktioniert das genau?

Hier ein kleines Beispiel (wir starten bei 10 und stellen den Cache auf 100 Werte ein)

ALTER SEQUENCE [dbo].[SeqCounter]
RESTART WITH 10
MINVALUE 10
MAXVALUE 1000000
CACHE 100

Wenn der erste Wert angefordert wird startet der SQL Server mit 10, vermerkt aber in der Systemtabelle sys.sequences den Wert 110 (aktueller Wert plus CACHE Einstellung) – er behauptet also, dass er bereits 100 Werte mehr vergeben hat. Sobald ein neuer Wert von der Sequence abgerufen wird, liefert der SQL Server diesen aus dem Zähler im Speicher, also 11, 12, …, bis 110. Damit ist die Grenze des gecachten Bereichs überschritten. Sobald der nächste Wert abgerufen wird wiederholt der SQL Server das Vorgehen: Er liefert zwar den nächsten Wert, schreibt aber wiederum den um 100 (basierend auf der CACHE-Einstellung) größeren Wert in die Systemtabelle.

Diese Vorgehensweise vermindert im Gegensatz zu Identities die Anzahl der Schreibzugriffe auf Metadaten und kann somit (insbesondere bei Massenverarbeitungsläufen) die Performance steigern.

Übrigens:

John Huang schreibt in seinem Blog, dass das Festlegen von großen CACHE-Werten kaum Performancevorteile bringt, da die wenigen Schreibvorgänge irgendwann nicht mehr wirklich ins Gewicht fallen.

Sollte der CACHE-Wert größer sein als MAXVALUE, wird der CACHE-Wert automatisch auf MAXVALUE reduziert.

Was passiert bei unerwartetem Herunterfahren der Datenbank-Engine?

Wenn die Datenbank-Engine per Service-Stop beendet wird, läuft alles “geregelt” ab, d.h. SQL Server schreibt dann den zuletzt vergebenen Wert (unabhängig der Cache-Einstellung) auf die Platte, sodass bei Neustart der nächste Wert vergeben werden kann und keine Sequence-Gap entsteht.

Im Desaster-Fall (Stromausfall oder “jemand deinstalliert SQL Server”…ok, dann ist sowieso alles weitere egal…aber man könnte schon von einem Desaster sprechen ) hat SQL Server u.U. keine Möglichkeit mehr diese Säuberungsarbeiten durchzuführen. D.h. im Klartext…die Werte gelten als “abgerufen”, sodass Lücken entstehen, die man aber auch manuell durch das RESTART WITH-Kommando “geradebiegen” könnte, sofern sinnvoll/gewünscht. Die Gefahr, dass Werte doppelt vergeben werden, besteht jedoch nicht.

Im Übrigen haben IDENTITIES hier einen Vorteil. Beim Hochfahren der Datenbank-Engine prüft SQL Server u.a. auch die zuletzt vergebenen Identitätswerte. Das stellt sicher, dass keine IDENTITY-Gaps durch technische Probleme entstehen können.

Security

Da es sich bei Sequences um “normale” Datenbankobjekte handelt, können hierauf auch Berechtigungen gesetzt werden. Dies kann man entweder über das eben erwähnte Dialogfeld oder klassisch über GRANT….TO… erledigen.

Einschränkungen

Sequences können nicht innerhalb von benutzerdefinierten Funktionen, Views, Unterabfragen, CTEs oder abgeleiteten Tabellen verwendet werden.

Ferner bekommt man Fehlermeldungen, sobald das verwendete Statement DISTINCT, EXCEPT, INTERSECT oder UNION verwendet. Eine Ausnahme davon bildet UNION ALL.

Es gibt noch eine Reihe weiterer Limitationen, die jedoch fast alle damit zu tun haben, dass sie nicht innerhalb von Aggregationen verwendet werden dürfen (z.B. PARTITION BY, GROUP BY…usw.).

Fazit

Sequences können bei richtiger Konfiguration performanter ablaufen und sind tabellenübergreifend einsetzbar. Sie lassen sich innerhalb einer Tabellendefinition als DEFAULT-Wert einbinden und können jederzeit modifiziert werden. Diese Vorzüge kombiniert mit höherer Kontrolle machen SEQUENCES zu einer sinnvollen Alternative, deren Einsatz jedoch hinsichtlich der vorhandenen Einschränkungen abgewogen werden muss.

Offizielle Dokumentation: http://msdn.microsoft.com/en-us/library/ff878058(v=sql.110).aspx