Nowość w SQL Server 2012 – sekwencje

                Nareszcie, nareszcie, nareszcie! Kiedy przesiadałem się z baz Oracle na SQL Server w żaden sposób nie mogłem pojąć jak można poradzić sobie bez sekwencji?! Że identity to taki odpowiednik?! Żarty…

                Wraz z najnowszym SQL Serverem pojawiły się i sekwencje! :) Panie i Panowie – a więc czas na sekwencje:

Sekwencja – to obiekt bazy danych generujący kolejne liczby. Przechowuje informację o aktualnej wartości. Na żądanie podaje kolejną wartość(wartości) aktualizując przechowywaną informację.

Tyle definicji, jak definiować sekwencję?

create sequence <schema>.<name>
as int
start with 1
increment by 1
minvalue 1
maxvalue 1
cycle
cache 1

Postaram się opisać teraz linia po linii, co które wyrażenie znaczy

create sequence <schema>.<name>
Sekwencje podlegają tym samym regułom co inne obiekty bazodanowe. Możemy tworzyć je w dowolnym schemacie, nadając im unikalną (w ramach schematu) nazwę. Zwyczajowo (o ile można tak mówić w przypadku nowości do SQL Server 2012) przyjęło się używać dla nazw przedrostka seq_

as int
możemy wskazać jakiego typu dane będzie zwracała nasza sekwencja. Możemy zadeklarować dowolny typ numeryczny. Parametr jest opcjonalny, jeśli go pominiemy domyślnie sekwencja zwraca wartości typu bigint

start with X
określamy od jakiej wartości mają być podawane kolejne liczby. Pamiętajmy, że do dyspozycji mamy dowolne typy numeryczne. Parametr jest opcjonalny, domyślna wartość to 1.

increment by X
określa jaki jest przyrost pomiędzy dwoma kolejnymi wartościami z sekwencji. Jeśli zdefiniujemy przyrost 10, to jeśli pierwszą wartością będzie 1, to kolejna przyjmie wartość 11. Parametr jest opcjonalny, domyślna wartość to 1.
Jako ciekawostkę należy podać fakt że nic nie stoi na przeszkodzie, aby kolejne liczby inkrementowane były w dół. Tak więc definiując przyrost jako -10, to po wartości 11 kolejną wartością będzie liczba 1.

minvalue X
minimalna wartość, jaką może przyjąć sekwencja. Parametr opcjonalny, domyślnie -9223372036854775808. Jeżeli sekwencja może się „przekręcić” (o tym za chwilę) to po osiągnięciu wartości max kolejną wartością jest wartość min (lub jeśli następuje inkrementacja w dół to po osiągnięciu wartości min kolejną wartością jest wartość max)

maxvalue X
maksymalna wartość, jaką może przyjąć sekwencja. Parametr opcjonalny, domyślnie 9223372036854775808. Jeżeli sekwencja może się „przekręcić” (o tym za chwilę) to po osiągnięciu wartości max kolejną wartością jest wartość min (lub jeśli następuje inkrementacja w dół to po osiągnięciu wartości min kolejną wartością jest wartość max)

cycle
zapewnia wcześniej już wspomnianą możliwość przekręcenia się sekwencji. Parametr opcjonalny, domyślnie ustawiony na „nie” czyli po osiągnięciu ostatecznej wartości kolejne wywołanie zwróci wyjątek.

cache X
parametr opcjonalny domyślnie ustawiony na 1 mówiący ile kolejnych wartości sekwencji ma być przechowywanych w cache`u SQL Servera

Po teorii przejdźmy do praktycznej części:

Gdzie znajdę sekwencje?

W SSMS po rozwinięciu baz danych, wybraniu interesującej nas bazy, rozwinięciu listy obiektów programowalnych znajdujemy sekwencje.

 

Możemy też bezpośrednio odwołać się do tabeli z sekwencjami:

SELECT *
  FROM SYS.SEQUENCES

Jak pobrać kolejną wartość z sekwencji?

SELECT NEXT VALUE FOR <schema>.<nazwa_sekwencji>

Jak odczytać aktualną wartość sekwencji?

SELECT CURRENT_VALUE 
  FROM SYS.SEQUENCES 
WHERE NAME = '<nazwa_sekwencji>'

Tu drobna uwaga – zapytanie to nie zawsze zwróci to, czego byśmy oczekiwali. Ale szczegóły przedstawię w dalszej części wpisu.

Jak pobrać zakres wartości z sekwencji?

declare
    @pierwsza_wartosc sql_variant,
    @ostatnia_wartosc sql_variant,
    @ile_pobrac_wartosci int = 5
--Pobranie kilku wartości
exec sp_sequence_get_range
    @sequence_name = '<schemat>.<nazwa_sekwencji>',
    @range_size = @ile_pobrac_wartosci,
    @range_first_value = @pierwsza_wartosc output,
    @range_last_value = @ostatnia_wartosc output

Można też zresetować sekwencję, czyli nadać jej nową aktualną wartość

alter sequence <schemat>.<sekwencja> 
restart with 10

W tym momencie sekwencja przyjmie wartość 10.

Przejdźmy do przykładów.

Tworzę sekwencję

CREATE SEQUENCE seq_pierwsza
START WITH 1
INCREMENT BY 1
minvalue 1
maxvalue 2
select next value for seq_pierwsza
-- zwrocilo 1
select next value for seq_pierwsza
-- zwroci 2

co zwroci kolejne wywołanie?

Otóż ponieważ nasza sekwencja nie ma ustawionej wartości cycle to otrzymamy wyjatek:

Msg 11728, Level 16, State 1, Line 1

The sequence object ‘seq_pierwsza’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

Praktyczne zastosowanie – podejście 1 – sekwencja może mi służyć do numeracji wierszy wyników

Tworzę sekwencję

CREATE SEQUENCE seq_tablica
START WITH 1
INCREMENT BY 1
minvalue 1
CYCLE

a następnie wykorzystuję ją:

 

Praktyczne zastosowanie – podejście 2 – sekwencja jako domyślna wartość kolumny, zamiast identity

Resetuję moją sekwencję, bo chciałbym wyniki mieć od id równego 1

alter sequence seq_tablica 
restart with 1

Tworzę tabelę, ustawiam dla kolumny id defaultową wartość z sekwencji, po czym na tej kolumnie zakładam również klucz główny

CREATE TABLE TablicaSekwencji
       (
              id int NOT NULL,
              nazwa nvarchar(MAX) NULL
       )
GO

ALTER TABLE TablicaSekwencji ADD CONSTRAINT
       DF_TablicaSekwencji DEFAULT (NEXT VALUE FOR seq_tablica) FOR id
GO

ALTER TABLE TablicaSekwencji ADD CONSTRAINT
       PK_TablicaSekwencji PRIMARY KEY CLUSTERED
       (
              id
       )
GO

Wrzucę kilka imion w pole nazwa

INSERT INTO TablicaSekwencji (Nazwa)
       SELECT 'Tomasz' AS imie
       UNION
       SELECT 'Anna' AS imie
       UNION
       SELECT 'Jan' AS imie
       UNION
       SELECT 'Katarzyna' AS imie

Wykonam zapytanie

SELECT * 
from TablicaSekwencji

I otrzymuję:

 

Ok, gdzie widzę przewagę sekwencji nad identity? Otóż w przypadku, który przedstawiłem powyżej, z jakiegoś powodu z tabeli TablicaSekwencji usunąłem wiersze o id 1 i 2. Następnie okazałoby się, że jednak te wiersze potrzebuję i to z tymi dokładnie id co były wcześniej.

W przypadku identity musiałem wyłączyć tą własność dla kolumny, wstawić wartość dla kolumny z atrybutem identity, a następnie włączyć własność identity ponownie. Sekwencje nie przysparzają mi takiego problemu. Sekwencja nie nakłada dodatkowych ograniczeń na kolumnę i mogę wstawić wartość jaka jest dla mnie pożądana bez dodatkowych kroków.

Praktyczne zastosowanie – podejście 3 – jedna sekwencja a kilka tabel

Każdy system się zmienia, okazuje się że to co pierwotnie było super zaplanowane i przemyślane, trzeba zmienić, a przy okazji nowe wymagania mogą pozostawać w sprzeczności z dotychczas przyjętymi rozwiązaniami projektowymi.

W pewnym systemie była tablica przechowująca adresy klientów. Teraz trzeba dodać adresy służbowe. Z jakiegoś powodu nie mogą one trafić do tej samej tablicy co adresy klientów więc stworzono dwie identyczne tabele. Tylko kolejne wymaganie spowodowało, że id w obu tabelach nie powinno się powtarzać (powinno być unikalne). Jak do tego problemu podejść? Proponuję sekwencję:

Tworzę obie tabele

CREATE TABLE adresy_klientow
       (
              id int NOT NULL,
              adres nvarchar(MAX) NULL
       )
GO

ALTER TABLE adresy_klientow ADD CONSTRAINT
       DF_adresy_klientow DEFAULT (NEXT VALUE FOR seq_tablica) FOR id
GO

CREATE TABLE adresy_sluzbowe
       (
              id int NOT NULL,
              adres nvarchar(MAX) NULL
       )
GO

ALTER TABLE adresy_sluzbowe ADD CONSTRAINT
       DF_adresy_sluzbowe DEFAULT (NEXT VALUE FOR seq_tablica) FOR id
GO

Powiedzmy że do tej pory w tablicy z adresami klientów miałem jakieś rekordy z id poniżej 1000

Więc moją sekwencję ustawiam żeby zaczęła podawać wartości od 1000

alter sequence seq_tablica 
restart with 1000

Insertuję kilka wierszy. Zwróćcie uwagę, że jako trzeci trafił adres służbowy:

INSERT into adresy_klientow (adres) SELECT 'Apoloniusza 1/3'
INSERT into adresy_klientow (adres) SELECT 'Kościuszki 14'
INSERT into adresy_sluzbowe (adres) SELECT 'Plac Zebrań Ludowych 13'
INSERT into adresy_klientow (adres) SELECT 'Grunwaldzka 176a/47'

I teraz chciałbym zobaczyć jakie są wyniki po złączeniu obu tabel:

SELECT 'adresy_klientow', id, adres from adresy_klientow
UNION ALL
SELECT 'adresy_sluzbowe', id, adres from adresy_sluzbowe
order by id

Otrzymuję:

 

Sekwencja a rollback

Ponieważ głównym założeniem sekwencji jest fakt, że wartości nie powtarzają się to rollback nie wycofuje pobrania wartości z sekwencji. Żeby łatwiej zrozumieć prześledźmy przykład:

-- Tworzę sekwencję
CREATE SEQUENCE seq_do_dziesieciu
START WITH 1
INCREMENT BY 1
minvalue 1
maxvalue 10
CYCLE

SELECT NEXT VALUE FOR seq_do_dziesieciu
--zwrocilo 1

--pobranie wartości a rollback
BEGIN TRAN
       SELECT NEXT VALUE FOR seq_do_dziesieciu
ROLLBACK TRAN
       --co zwroci poniższe zapytanie?
       SELECT CURRENT_VALUE 
       FROM SYS.SEQUENCES 
       WHERE NAME = 'seq_do_dziesieciu'

Przed transakcją sekwencja miała wartość 1, w ramach transakcji pobrałem wartość a następnie wykonałem rollback. Okazuje się że mimo rollbacka aktualna wartość sekwencji ustawiona jest na 2.

Co właściwie odczytam pobierając aktualną wartość sekwencji?

Tworzę trzy identycznie brzmiące sekwencje. Dwie w bazie AdventureWorks w różnych schematach i jedną w bazie master:

use AdventureWorks2012
CREATE SEQUENCE dbo.seq_problematyczna
CREATE SEQUENCE mg.seq_problematyczna

use master
CREATE SEQUENCE dbo.seq_problematyczna

Ustawiam je na różne wartości, tak abym odróżnił wartość której odczytałem

use AdventureWorks2012
alter sequence dbo.seq_problematyczna restart with 10
alter sequence mg.seq_problematyczna restart with 20

use master
alter sequence dbo.seq_problematyczna restart with 30

Teraz chcę odczytać wartość sekwencji, więc wykonuję polecenie

use AdventureWorks2012

SELECT CURRENT_VALUE 
FROM SYS.SEQUENCES 
WHERE NAME = 'seq_problematyczna'

No właśnie, ale której sekwencji? Co otrzymam w wyniku?

Otóż w wyniku otrzymam wartości dla seq_problematyczna dla wszystkich schematów w bazie Adventure Works:

 

I teraz dwie sprawy.

Aby zapytać o sekwencję z bazy master powinienem albo użyć wcześniej komendy use i przełączyć się na bazę master, albo podać prefix bazy do selecta:

SELECT CURRENT_VALUE 
FROM master.SYS.SEQUENCES 
WHERE NAME = 'seq_problematyczna'

Jeżeli natomiast interesuje mnie sekwencja z określonego schematu to mogę zawęzić wyniki po jednym z nich:

SELECT CURRENT_VALUE
FROM SYS.SEQUENCES
WHERE NAME = 'seq_problematyczna'
AND schema_id IN (SELECT schema_id from sys.schemas where name = 'mg')

Zainteresowanych odsyłam jeszcze do tabeli sys.sequences, można tam znaleźć sporo interesujących informacji. Dodatkowo jeśli komuś wciąż mało to całkiem fajnie piszą też o sekawnecjach na MSDN.

Miłej zabawy,
pozdrawiam,

Podziel się na:
  • Google Bookmarks
  • RSS
  • Dodaj do ulubionych
  • email
  • Facebook
  • Twitter
  • Blogger.com
  • LinkedIn
  • Gadu-Gadu Live