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:

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.

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

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:

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:

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:

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:

innych konstrukcjach jak np. przypisania do zmiennej:

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

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:

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 🙂

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

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

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:

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

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:

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.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

Leave a Comment

Your email address will not be published. Required fields are marked *