SQL Server 2012 - Sekwencje  

Autor: Damian Widera

Opublikowano: 2012-11-21

Sekwencje są nowymi obiektami definiowanymi na poziomie schematu bazy danych. Te obiekty generują kolejne wartości numeryczne, zgodnie z podaną definicją. Wartości te mogą być zarówno rosnące, jak i malejące oraz mogą się powtarzać.

Sekwencje, w odróżnieniu od kolumn IDENTITY, nie są powiązane z żadną tabelą, a kolejne numery sekwencji są sterowane, np. z logiki aplikacji lub procedury składowanej.

Sekwencja tworzona jest za pomocą polecenia CREATE SEQUENCE:

CREATE SCHEMA Test;
GO

CREATE SEQUENCE Test.Dodaj1
    START WITH 1
    INCREMENT BY 1 ;
GO

W powyższym przykładzie został utworzony najpierw schemat Test, a następnie obiekt sekwencji o nazwie Dodaj1, który jako wartość początkową przyjmuje 1 i będzie zwiększany również o 1.

W celu odczytania aktualnej wartości sekwencji należy wywołać składnię NEXT VALUE FOR, jak pokazano na poniższym przykładzie:

SELECT NEXT VALUE FOR Test.Dodaj1;

Obiekt sekwencji ma nieco bardziej rozbudowaną składnię. W trakcie jego tworzenia można określić kilka innych parametrów:

• typ danych sekwencji – musi być numeryczny, całkowity,
• wartość początkową (START WITH),
• inkrementację (INCREMENT BY),
• wartość minimalną (MINVALUE),
• wartość maksymalną (MAXVALUE),
• cykliczność (CYCLE) – czy sekwencja po wygenerowaniu wartości maksymalnej powinna rozpocząć ponowne generowanie od wartości minimalnej,
• czy powinna zostać wygenerowana większa ilość elementów (CACHE).

Obiekt z powyższymi parametrami zaprezentowano na kolejnym przykładzie:

CREATE SEQUENCE Test.Numeryczna
    AS decimal(3,0) 
    START WITH 125
    INCREMENT BY 25
    MINVALUE 100
    MAXVALUE 200
    CYCLE
    CACHE 3
;

Można również utworzyć sekwencję z minimalną liczbą parametrów, definiujących ją jako obiekt:

CREATE SEQUENCE Test.SekwencjaDefault

W takim wypadku wszystkie parametry będą ustawione na wartości domyślne, czyli typem danych będzie bigint, wartością początkową liczba 1 oraz inkrementacja będzie równa 1.

Sekwencje posiadają własny widok katalogowy, w którym można sprawdzić zarówno parametry, dla jakich zostały zdefiniowane, jak i ich aktualne wartości:

SELECT * FROM sys.sequences

Typowe zastowosanie sekwencji:

• aplikacja generuje kolejny numer i zna go przed wstawieniem do tabeli,
• aplikacja kliencka może wstawić ten sam numer do wielu tabel,
• numery muszą być okresowo resetowane – np. dla celów fakturowania. Kolejne numery faktury posiadają również numer porządkowy dokumentu, generowany każdego dnia od nowa.

Sekwencje posiadają również pewne ograniczenia. Głównym z nich jest brak kontroli takiej, jaką dawała kolumna IDENTITY – można wstawić ten sam numer wielokrotnie do tabeli. Jeśli taka sytuacja nie może wystąpić, to należy się przed nią zabezpieczyć, np. pisząc odpowiedni wyzwalacz, który albo wygeneruje kolejny numer sekwencji (co może być niewystarczające), albo wycofa zmiany.

Używanie opcji CACHE powoduje, że w trakcie generowania powstaje tyle liczb, ile wynosi wartość tej opcji. W przypadku restartu serwera wszystkie wygenerowane numery, które rezydują w pamięci zostaną utracone.

Obiekt sekwencji nie zapewnia, że po wygenerowaniu kolejnego numeru, a przed wstawieniem go do tabeli, numery będą ciągłe i bez dziur – wycofanie transakcji działa tak samo, jak dla kolumny IDENTITY.

Poniżej znajduje się przykład zaczerpnięty z MSDN, ilustrujący podstawowe operacje na sekwencjach:

--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