SQL Server 2012 – Was ist eine Sequenz & wie verwendet man Sequenzen?

Hallo @all,

mit SQL Server 2012 gibt es ein neues Feature in T-SQL mit dem Namen Sequence oder im gutem altem Deutsch Sequenzen.

In der Vergangenheit hat man zur die Eindeutigkeit von Primärschlüsseln oft die Funktion Identify() verwendet. Doch wenn man bspw. ein Bulk-Insert durchführt oder eine definierte Schrittweite eines Primärschlüssels angeben möchte, dann wird es schwierig. Mit der Sequenze kann man dies gut und einfach bewerkstelligen. Schauen wir einmal kurz in den SQL 2012 MSDN…

Was ist eine Sequenze?
Hier ist der wichtige Auszug auf der MSDN Library: “Als Sequenz wird ein benutzerdefiniertes schemagebundenes Objekt bezeichnet, das eine Sequenz numerischer Werte anhand der Spezifikation generiert” …”Die Sequenz numerischer Werte wird in aufsteigender oder absteigender Reihenfolge in einem definierten Intervall generiert und kann so konfiguriert werden, dass sie beim Erreichen des Endes neu gestartet wird (Zyklus).Sequenzen werden anders als Identitätsspalten keinen bestimmten Tabellen zugeordnet. Anwendungen verweisen auf ein Sequenzobjekt, um dessen nächsten Wert abzurufen. Die Beziehung zwischen Sequenzen und Tabellen wird von der Anwendung gesteuert. Benutzeranwendungen können auf ein Sequenzobjekt verweisen und die Werte in mehreren Zeilen und Tabellen koordinieren. Im Unterschied zu Identitätsspaltenwerten, die beim Einfügen von Zeilen generiert werden, kann eine Anwendung durch Aufrufen der NEXT VALUE FOR -Funktion die nächste Sequenznummer abrufen, ohne die Zeile einzufügen.”

Wie verwendet man eine Sequenze?

Hierzu habe ich mir folgende Beispiel überlegt:

1. Erstellen einer Sequenz mit der Schrittweite von 1, ähnlich wie es mit Identity überlich ist.

 create sequence MyBulkImportSequence as int
    start with 0
    increment by 1

 Wie sieht denn eine Sequenze aus?

 select * from sys.sequences 
    where name = 'MyBulkImportSequence'

Tipp: Interessant sind die Werte: Name, start_value, increment, current_value

Erstellen kann man die Sequenz auch über die Oberfläche:

Gut damit hat man die Sequenz erstellt. Nun geht es darum diese zu Nutzen.

 

2. Erstellen von 3 Beispieltabellen

 create table MyTable1(id uniqueidentifier,sequenceNo bigint,data float)
create table MyTable2(id uniqueidentifier,sequenceNo bigint,data float)
create table MyTable3(id uniqueidentifier,sequenceNo bigint,data float)

3. Jetzt starten ich ein Bulkinsert in alle Tabellen, wobei jede Tabellen einen eigenen Schlüssel hat und eine SequenzNo, der als Fremdschlüssel über alle Tabellen verwendet wird:

 begin transaction Bulkinsert
    declare @MySequence bigint = next value for MyBulkImportSequence
    declare @i int = 0
    while  @i < 1000
    begin
        insert into MyTable1(id, sequenceNo, data)
                            values (NEWID(), @MySequence,RAND())
        insert into MyTable2(id,sequenceNo,data)
                            values (NEWID(), @MySequence,RAND())
        insert into MyTable3(id,sequenceNo,data)
                            values (NEWID(), @MySequence,RAND())
        
        set @MySequence = next value for MyBulkImportSequence
        set @i = @i+1
    end
commit

Schauen wir mal was die Tabelle nun enthält:

 select t1.Id, t2.id, t3.id, t1.sequenceNo, t1.data, t2.data, t3.data

   from MyTable1 t1 inner join MyTable2 t2    
        on t1.sequenceNo = t2.sequenceNo 
    inner join MyTable3 t3    
        on t2.sequenceNo = t3.sequenceNo

order by t1.sequenceNo desc

Damit haben wir jetzt das Datenkonstrukt erstellt das für eine Weile lebt, bis auf einmal…

4. Eine neue Anforderung definiert wird es gilt eine neue Zähleweise vom Datenschlüssel zu implementieren. Normalerweise würde man jetzt jede Stelle anfassen müssen wo das Insert-Statement implementiert wurde.

Das war gestern, denn mit der Sequenz kann man die zählweise der SequenzId ganz einfach verändern. Man ändert also nur die Sequenz  das klingt EASY, ist es auch:

 alter sequence MyBulkImportSequence increment by 10

Man könnte auch die Oberfläche nehmen, aber da ist nicht so cool:

Ab jetzt wird die neue Id-Zählweise schon verwendet und es geht nur noch darum die alten SequenzeId’s in das neue Format anzupassen. Noch ein sehr praktischer Punkt: Sequenzen kann man über Tabellengrenzen hinweg verwenden, eine Identiy() nicht.

Erstellen wir nochmal ein paar neue Einträge in den  Tabellen… und schauen uns die SequenzNo. an: (ist das nicht schön… alles in 10er Schritten)

Fertig und viel Spaß mit den Sequenzen.

Liebe Grüße

Patrick