SQL Server – tworzenie sekwencji liczbowych z wykorzystaniem obiektu SEQUENCE

Wśród specjalistów zajmujących się zawodowo bazą danych SQL Server powszechna jest znajomość IDENTITY, czyli pewnej właściwości przypisanej do określonej kolumny tabeli, która ma za zadanie tworzenie automatycznie numerowanej kolumny (więcej o tej właściwości można przeczytać tutaj). Jest jednak obiekt o podobnym zastosowaniu dający w niektórych aspektach nieco więcej możliwości – chodzi mianowicie o obiekt SEQUENCE, który chciałbym opisać w niniejszym artykule – zapraszam do lektury.

Sekwencja jest obiektem przypisanym do określonego schematu – nie jest w żaden sposób powiązana z określoną tabelą, a co za tym idzie możliwe jest jej zastosowanie w więcej niż jednej tabeli, co nie było możliwe przy użyciu IDENTITY. Również składnia tego typu obiektu jest dużo bardziej rozbudowana niż IDENTITY i została przedstawiona poniżej:

CREATE SEQUENCE [schema_name . ] sequence_name  
     AS integer_type  
     START WITH   
     INCREMENT BY   
     MINVALUE | NO MINVALUE 
     MAXVALUE | NO MAXVALUE  
     CYCLE | NO CYCLE  
     CACHE | NO CACHE ;

Aby zobrazować działanie poszczególnych właściwości obiektu sekwencji stwórzmy sobie tabelę, która będzie nam użyteczna podczas testów. Całość demonstracji będzie używać bazy AdventureWorksDW2014 i właśnie w ramach tej bazy stworzymy sobie tabelę o nazwie dbo.Test składającą się z pola id oraz ProductKey.

CREATE TABLE dbo.Test
(
id bigint,
ProductKey int
)

Następnie stwórzmy prostą sekwencję o nazwie dbo.Seq1, która wygeneruje liczby od 1 powiększane o 100

CREATE SEQUENCE dbo.Seq1
AS BIGINT
START WITH 1
INCREMENT BY 1

W powyższym zapisie możemy zauważyć, iż po słowie kluczowym AS zdefiniowany został typ generowanych danych BIGINT. Ogólnie rzecz biorąc sekwencje mogą generować każdy typ danych oparty o INTEGER (SMALLINT, BIGINT,INT itp) oraz typy liczbowe zmiennoprzecinkowe o skali równej 0 (np. DECIMAL(19,0)). Następnie zdefiniowana została klauzula START WITH, która jak sama nazwa wskazuje służy do skonfigurowania początkowej wartości sekwencji. Warto w tym miejscu powiedzieć, iż każda z omawianych właściwości może przyjmować dowolną wartość, która mieści się w zakresie typu danych podanego po słowie kluczowym AS. Ostatnią właściwością podaną w powyższym zapytaniu jest INCREMENT BY mówiący o tym o jaką wartość ma być zwiększana sekwencja z każdym przebiegiem. Tak więc po uruchomieniu powyższego zapytania stworzony zostanie obiekt i chcemy go użyć jako wartość auto numerowana kolumny id stworzonej wcześniej tabeli dbo.Test. Aby to zrobić możemy użyć słowa kluczowego NEXT VALUE FOR tak jak zostało to pokazane poniżej:

INSERT INTO dbo.Test (id,ProductKey)
SELECT
NEXT VALUE FOR dbo.Seq1,
ProductKey
FROM
DimProduct

Jak widać powyżej sekwencja zadziałała prawidłowo i wiersze zostały ponumerowane. W kolejnym kroku wyczyśćmy tabelę i wstawmy do niej dane na nowo:

TRUNCATE TABLE dbo.Test
GO

INSERT INTO dbo.Test (id,ProductKey)
SELECT
NEXT VALUE FOR dbo.Seq1,
ProductKey
FROM
DimProduct
GO

select * from dbo.Test

Dane zostały wstawione na nowo jednakże wartości sekwencji przypisanej do pola id nie zostały nadane od 1 lecz od ostatnio nadanej przez sekwencję wartości (607 jest kolejną wartością ponieważ tabela wykorzystana  wcześniej tj. DimProduct zawiera 606 produktów które zostały ponumerowane sekwencją i wstawione do tabeli). W tym momencie w Waszych głowach może pojawiać się pytanie jak zresetować taką sekwencję – możemy to osiągnąć używając składni ALTER SEQUENCE RESTART:

ALTER SEQUENCE dbo.Seq1 RESTART

Składnia NEXT VALUE FOR nie jest związana z SELECT INTO i możemy bez żadnego problemu użyć jej także w INSERT VALUES:

INSERT INTO dbo.Test (id,ProductKey)
VALUES (NEXT VALUE FOR dbo.Seq1,1000)

innych konstrukcjach jak np. przypisania do zmiennej:

DECLARE @SeqVal BIGINT
SET @SeqVal=NEXT VALUE FOR dbo.Seq1

czy też użycia wartości sekwencji jako wartości domyślnej pola w tabeli:

ALTER TABLE dbo.Test 
    ADD   
        DEFAULT    
        NEXT VALUE FOR dbo.Seq1   
        FOR Id;

Kolejnymi dwoma właściwościami o których chciałbym wspomnieć są MINVALUE i MAXVALUE, które definiują wartość minimalną i maksymalną jaką omawiany obiekt może przyjąć. Aby zilustrować jak ich podanie zmodyfikuje działanie sekwencji najpierw zrestartujmy jej licznik i nadajmy jej wartość maksymalną równą 50:

ALTER SEQUENCE dbo.Seq1 RESTART
GO
ALTER SEQUENCE dbo.Seq1
MAXVALUE 50
GO

W momencie gdy będziemy próbować dodać dane z tabeli DimProduct otrzymamy komunikat błędu ponieważ wybrana przez nas tabela ma 606 produktów, a sekwencja wygenerowała tylko 50 liczb i jej możliwości twórcze się skończyły 🙂

The sequence object 'Seq1' has reached its minimum or maximum value. 
Restart the sequence object to allow new values to be generated.

W takiej sytuacji mamy kilka opcji:

• zaakceptować występowanie tego błędu gdyż poprawnie obrazuje on logikę biznesową
• nie używać stworzonej sekwencji
• rozszerzyć MAXVALUE na większą wartość
• Użyć kolejnej właściwości o nazwie CYCLE, która została przedstawiona poniżej 🙂

Słowo kluczowe CYCLE występujące w definicji sekwencji określa, że po przekroczeniu wartości maksymalnej (MAXVALUE) sekwencja rozpocznie numerowanie wierszy od początku tj. od wartości zdefiniowanej jako minimalna (Pamiętajcie proszę, że jeżeli nie zdefiniowaliście właściwości MINVALUE to domyślną wartością minimalną sekwencji jest minimalna wartość typu danych np. w przypadku BIGINT będzie to wartość -9223372036854775808). Aby zobrazować to zachowanie wyczyśćmy sobie naszą testową tabelę i stwórzmy na nowo naszą sekwencję:

TRUNCATE TABLE dbo.Test
GO

DROP SEQUENCE IF EXISTS dbo.Seq1
GO

CREATE SEQUENCE dbo.Seq1
AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 5
CYCLE

Następnie wstawmy do niej wiersze z tabeli DimProduct w taki sam sposób jak poprzednio:

INSERT INTO dbo.Test (id,ProductKey)
SELECT
 NEXT VALUE FOR dbo.Seq1,
 ProductKey
FROM
 DimProduct

Sekwencja działa tutaj zgodnie z naszymi oczekiwaniami tj. numerowanie jest restartowane co 5 wierszy. Oczywiście moglibyśmy stworzyć coś działającego w podobny sposób nieco inaczej, jednakże podejście z sekwencją daje nam pewną elastyczność no i nikt nie zaprzeczy, że taki zapis jest czytelny i łatwy w interpretacji.

Ostatnią opcją obiektu SEQUENCE jest CACHE. Ustawienie to definiuje ile przyszłych wartości sekwencji ma być przechowywanych w pamięci operacyjnej. Dla przykładu wyobraźmy sobie, że CACHE zostało ustawione na 15 – wtedy też gdy wstawiona do tabeli zostanie wartość 1 to silnik bazodanowy będzie prealokował kolejne 14 wartości w pamięci w celach optymalizacyjnych. W tym samym czasie wartość 15 zostaje zapisana w tabeli systemowej jako ostatnia prealokowana wartość sekwencji. Jeśli w tabelach systemowych znajduje się wartość 15 jako ostatnio prealokowana wartość sekwencji,a serwer zostanie zamknięty to wartość ta zostanie zmieniona przed zamknięciem na ostatnią wartość sekwencji jaka została użyta np. 11. Inaczej się dzieje w przypadku wystąpienia awarii – wtedy też wartość metadanych będzie równa maksymalnej prealokowanej wcześniej wartości. Zachowanie to może prowadzić do powstania pewnych “luk” w danych. Zilustrujmy to zachowanie na przykładzie – na samym początku wyczyśćmy naszą testową tabelę i stwórzmy sekwencję, która będzie prealokowała aż milion wartości. Następnie wstawmy do tabeli testowej dane z DimProduct czyli 606 wierszy:

DROP SEQUENCE IF EXISTS dbo.Seq1
GO

CREATE SEQUENCE dbo.Seq1
AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000000
CYCLE
CACHE 1000000

TRUNCATE TABLE dbo.Test
GO

INSERT INTO dbo.Test (id,ProductKey)
SELECT
NEXT VALUE FOR dbo.Seq1,
ProductKey
FROM
DimProduct

Następnie “zabijmy” proces korzystając np. ze standardowego Menedżera Zadań – taki sposób działania z procesem bazy danych w dobry sposób symuluje poważną awarię jak np. odcięcie prądu.

Po wyłączeniu procesu możemy uruchomić usługę w Configuration Manager, połączyć się z Management Studio, ponownie wstawić wiersze do testowej tabeli, a następnie ją odpytać:

INSERT INTO dbo.Test (id,ProductKey)
SELECT
NEXT VALUE FOR dbo.Seq1,
ProductKey
FROM
DimProduct
GO

select * from dbo.Test
GO

Jak widać pierwsze numerowanie przebiegło poprawnie (pierwsze 606 wierszy) potem nastąpiła awaria i po tym wydarzeniu kolejna numeracja rozpoczęła się od wartości 1000000 czyli ostatniej wartości przechowywanej w pamięci. Ze względu na to, że wyłączenie usługi nastąpiło gwałtownie to SQL Server nie miał czasu na zmodyfikowanie metadanych w tabelach systemowych na ostatnio użytą wartość sekwencji jaką była liczba 606 i pozostała tam liczba 1000000. Dlatego też wraz ze wstawieniem nowych wartości SQL Server zajrzał do metadanych, znalazł wartość sekwencji dodał do niej wartość własności INCREMENT BY i wstawił do tabeli – tak właśnie to działa. W większości przypadków takie zachowanie nie przeszkadza nam w niczym bo klucze tego typu zazwyczaj nie niosą ze sobą żadnej informacji, a jedynie są liczbami porządkowymi i nie mają znaczenia przeskoki w ich wartościach. W przypadku gdy chcemy mieć stuprocentową pewność, iż nie powstaną żadne luki w tabelach możemy użyć zapisu NO CACHE – musimy mieć wtedy jednak świadomość, że przy każdym wierszu SQL Server będzie musiał zapisywać bieżącą wartość sekwencji w metadanych co może być kosztowne i skutkować spadkiem wydajności.

Ostatnią kwestią jaką chciałbym poruszyć w ramach niniejszego artykułu są metadane sekwencji. Do dyspozycji dostaliśmy standardowy widok systemowy tj. sys.sequences – poniżej przedstawiłem zapytanie wybierające użyteczne informacje z tego widoku:

SELECT
	SCHEMA_NAME(S.schema_id) as Schema_Name,
	S.name,
	S.object_id,
	t.name,
	S.create_date,
	S.modify_date,
	S.start_value,
	S.increment,
	S.minimum_value,
	S.maximum_value,
	S.is_cycling,
	S.is_cached,
	S.cache_size,
	S.current_value,
	S.is_exhausted
FROM sys.sequences AS S
JOIN sys.types AS t
	ON t.system_type_id=S.system_type_id

Większość z tych właściwości nie wymaga komentarza – warto jednak zwrócić uwagę, na właściwość z bieżącą wartością sekwencji (current_value) oraz is_exhausted, która mówi czy sekwencja została wyczerpana czy też nie.

Istnieje jeszcze procedura składowana sp_sequence_get_range ale o niej powiemy sobie w ramach innego artykułu. To by było na tyle – jak pewnie zauważyliście sekwencje to obiekty dużo bardziej elastyczne niż klasyczne IDENTITY i rozwiązują całą gamę przeróżnych problemów, które wcześniej trzeba było rozwiązywać w procedurach składowanych, aplikacjach klienckich lub (o zgrozo) za pomocą wyzwalaczy. Mam nadzieję, że same tematyka przypadła Wam do gustu i znajdziecie ich zastosowanie w praktyce.