Nowości w SQL Server 2012 – sekwencje

2012-05-23 

W najnowszej wersji SQL Server pojawił się mechanizm sekwencji. Sekwencje przypominają Identity, z tą różnicą, że ich działanie nie ogranicza się do pojedynczej tabeli. Utworzona sekwencja jest globalnym obiektem bazy danych i może być wykorzystywana w wielu tabelach, ponadto można w łatwy sposób zarządzać nią z poziomu języka T-SQL. Na poszczególnych przykładach pokażę w jaki sposób korzystać z obiektów sekwencji.

Tworzenie/usuwanie

Najprostszy sposób utworzenia sekwencji:

create sequence dbo.Numerator

Przy tworzeniu sekwencji możemy określić dodatkowe parametry:

create sequence dbo.Numerator
    as int
    start with 1
    increment by 1
    minvalue 1
    maxvalue 100
    cycle
    cache 10
  • AS – typ wartości (tinyint, smallint, int, bigint, numeric), domyślnym typem jest bigint
  • START WITH – wartość początkowa, domyślnie minimalna/maksymalna wartość wybranego typu danych
  • INCREMENT BY – wartość o jaką sekwencja jest zwiększana/zmniejszana przy pobieraniu kolejnej wartości, domyślnie 1
  • MINVALUE – minimalna wartość sekwencji
  • MAXVALUE – maksymalna wartość sekwencji
  • CYCLE – opcja określa, czy po osiągnieciu wartości MAXVALUE/MINVALUE licznik sekwencji jest „przekręcany”
  • CACHE – liczba kolejnych wartości, które przy pobieraniu są buforowane w pamięci w celu ograniczenia operacji odczytu/zapisu z dysku, zwiększa to wydajność ale w przypadku wyłączenia serwera dochodzi do powstania luk w numeracji (dane z cache zostają utracone)

Usunięcie sekwencji:

drop sequence dbo.Numerator

Sekwencjami można również zarządzać z poziomu Management Studio, są one widoczne w gałęzi Programmability -> Sequences:

Resetowanie wartości

Istnieje możliwość zresetowania sekwencji do danej wartości początkowej:

alter sequence dbo.Numerator
restart with 3

Podgląd aktualnej wartości

W celu sprawdzenia aktualnej wartości sekwencji bez jej zwiększania/zmniejszania należy posłużyć się widokiem sys.sequences (kolumna current_value):

select current_value
from sys.sequences
where name = 'Numerator'

Pobieranie wartości

Do pobrania i jednoczesnej zmiany wartości sekwencji służy polecenie NEXT VALUE FOR:

select next value for dbo.Numerator

Przykłady zastosowania:

create table #Test (Id int, Nazwa nvarchar(128))

--Bezpośrednie pobranie wartości
insert into #Test (Id, Nazwa)
values (next value for dbo.Numerator, 'Nazwa_1')

--Pobranie wartości do zmiennej
declare @Numer int = next value for dbo.Numerator
insert into #Test (Id, Nazwa)
values (@Numer, 'Nazwa_2')

--Pobranie wartości dla każdego rekordu zapytania zgodnie z porządkiem w opcjonalnej klauzuli OVER
insert into #Test(Id, Nazwa)
select next value for dbo.Numerator over(order by name), name
from sys.objects

Możliwe jest również pobranie zakresu wartości za pomocą procedury sp_sequence_get_range:

declare
    @firstValue sql_variant,
    @lastValue sql_variant

--Pobranie 10 wartości
exec sp_sequence_get_range
    @sequence_name = 'dbo.Numerator',
    @range_size = 10,
    @range_first_value = @firstValue output,
    @range_last_value = @lastValue output

select @firstValue as PierwszyNumer, @lastValue as OstatniNumer

Transakcje

W przypadku pobrania wartości sekwencji w ramach rozpoczętej transakcji, a następnie odwołaniu tej transakcji, wartość sekwencji nie jest przywracana do stanu początkowego:

select current_value
from sys.sequences
where name = 'Numerator'

--Rozpoczęcie transakcji
begin tran
select next value for dbo.Numerator
--Wycofanie transakcji
rollback tran

select current_value
from sys.sequences
where name = 'Numerator'

Parametry MINVALUE, MAXVALUE, CYCLE

Poniższy kod tworzy sekwencję rozpoczynającą się od 4 z minimalną wartością 1 i maksymalną 5. Następnie trzykrotnie pobierana jest jej wartość:

if (object_id('dbo.Numerator ') is not null)
    drop sequence dbo.Numerator

create sequence dbo.Numerator
    as int
    start with 4
    increment by 1
    minvalue 1
    maxvalue 5

select next value for dbo.Numerator
select next value for dbo.Numerator
select next value for dbo.Numerator

Ostatnia próba pobrania zakończyła się błędem, ponieważ sekwencja osiągnęła maksymalną wartość. Jeżeli przy tworzeniu sekwencji dodamy parametr CYCLE, po osiągnięciu MAXVALUE jej wartość zostanie ustawiona na MINVALUE:

if (object_id('dbo.Numerator ') is not null)
    drop sequence dbo.Numerator

create sequence dbo.Numerator
    as int
    start with 4
    increment by 1
    minvalue 1
    maxvalue 5
    cycle

select next value for dbo.Numerator
select next value for dbo.Numerator
select next value for dbo.Numerator

Warto zauważyć, że w powyższej sytuacji wartość sekwencji nie jest ustawiana na parametr START WITH tylko na MINVALUE.

Jeżeli parametr INCREMENT BY miałby wartość ujemną, sekwencja po osiągnięciu MINVALUE zostałaby ustawiona na MAXVALUE.

Default kolumny

Sekwencji można użyć jako wartości DEFAULT dla kolumny tabeli:

create table Test
(
    Id int default(next value for dbo.Numerator),
    Nazwa nvarchar(128)
)

Więcej informacji o sekwencjach w SQL Server 2012: MSDN

Reklamy

Posted on 2012-05-23, in SQL Server and tagged , . Bookmark the permalink. Dodaj komentarz.

Skomentuj

Wprowadź swoje dane lub kliknij jedną z tych ikon, aby się zalogować:

Logo WordPress.com

Komentujesz korzystając z konta WordPress.com. Log Out / Zmień )

Zdjęcie z Twittera

Komentujesz korzystając z konta Twitter. Log Out / Zmień )

Facebook photo

Komentujesz korzystając z konta Facebook. Log Out / Zmień )

Google+ photo

Komentujesz korzystając z konta Google+. Log Out / Zmień )

Connecting to %s

%d blogerów lubi to: