Lekcja 26. Użycie SEQUENCE i IDENTITY

Właściwość kolumn IDENTITY oraz objekt SEQUENCE są funkcjami pozwalającymi na automatyczne generowanie sekwencji liczbowych. Sekwencje te są zwykle używane jako klucze zastępcze w tabelach. Właściwość IDENTITY jest bardzo starą funkcjonalnością i ma wiele ograniczeń. Obiekt SEQUENCE został wprowadzony do SQL Serwera w wersji 2012 i eliminuje wiele ograniczeń jakie ma IDENTITY.

IDENTITY

IDENTITY jest właściwością kolumny w tabeli. Właściwość ta automatycznie przypisuje wartość do tej kolumny w trakcie wstawiania danych. Można zdefiniować ją dla kolumn o typie numerycznym który posiada skalę równą 0. Oznacza to typ INTEGER oraz NUMERIC/DECIMAL ze skalą 0. Definiując tę właściwość można opcjonalnie podać początek i liczbę o którą wartość będzie inkrementowana. Domyślnymi wartościami są 1 i 1. Tylko jedna kolumna w tabeli może mieć właściwość IDENTITY. Poniżej przedstawiony jes kod tworzący tabelę Sales.MyOrders z kolumną orderid która posiada zdefiniowaną właściwość IDENTITY:

USE TSQL2012;
IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders;
GO
CREATE TABLE Sales.MyOrders
(
orderid INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY,
custid INT NOT NULL
CONSTRAINT CHK_MyOrders_custid CHECK(custid > 0),
empid INT NOT NULL
CONSTRAINT CHK_MyOrders_empid CHECK(empid > 0),
orderdate DATE NOT NULL
);

Kiedy wstawiamy dane do tabeli to nie określamy wartości dla kolumny z właściwością IDENTITY ponieważ jest ona generowana automatycznie. Poniższe zapytanie wstawia wartości do utworzonej tabeli lecz nie wstawia wartości do kolumny orderid:

INSERT INTO Sales.MyOrders(custid, empid, orderdate) VALUES
(1, 2, '20120620'),
(1, 3, '20120620'),
(2, 2, '20120620');
SELECT * FROM Sales.MyOrders;

Wynik:

orderid custid empid orderdate ----------- ----------- ----------- ---------- 1 1 2 2012-06-20 2 1 3 2012-06-20 3 2 2 2012-06-20

W przypadku kiedy chcemy wstawić wiersz do tabeli i określić bezpośrednio wartość kolumny z właściwością IDENTITY trzeba ustawić opcję SET IDENTITY_INSERT <table> na wartość ON. Nie istnieje opcja która pozwala na aktualizację kolumny z właściwością IDENTITY. T-SQL posiada wiele funkcji które pozwalają na pobranie ostatniej wartości wygenerowanej przez właściwość IDENTITY:

SCOPE_IDENTITY - zwraca ostatnią wygenerowaną w sesji wartość w aktualnym zasięgu (scope).
@@IDENTITY - zwraca ostatnią wygenerowaną w sesji wartość niezależnie od zasięgu (scope).
IDENT_CURRENT - funkcja ta akceptuje nazwę tabeli na wejściu i zwraca ostatnio wygenerowaną wartość IDENTITY.

Przykładowo poniższy kod prezentuje wynik tych funkcji dla sesji w której dokonaliśmy wstawienia danych:

SELECT
SCOPE_IDENTITY() AS SCOPE_IDENTITY,
@@IDENTITY AS [@@IDENTITY],
IDENT_CURRENT('Sales.MyOrders') AS IDENT_CURRENT;

Wynik:

SCOPE_IDENTITY @@IDENTITY IDENT_CURRENT --------------------------------------- --------------------------------------- --------------------------------------- 3 3 3

Uruchomienie tego zapytania w innym oknie da następujący wynik:

SCOPE_IDENTITY @@IDENTITY IDENT_CURRENT --------------------------------------- --------------------------------------- --------------------------------------- NULL NULL 3

Dzieje się tak dlatego, że zapytanie zostało uruchomione w innej sesji niż ta w której zostały wygenerowane wartości. Aby pokazać różnicę pomiędzy SCOPE_IDENTITY i @@IDENTITY załóżmy, że mamy procedurę składowaną P1 z trzema wyrażeniami:

INSERT generujący nową wartość dla kolumny z IDENTITY.
Odwołanie do procedury składowanej P2 która również wykonuje INSERT generujący nową wartość dla kolumny z IDENTITY.
Wyrażenie które pobiera wartość funkcji SCOPE_IDENTITY i @@IDENTITY.

Funkcja SCOPE_IDENTITY zwróci wartość wygenerowaną w pierwszym wyrażeniu procedury składowanej P1 (ta sama sesja i zasięg). Funkcja @@IDENTITY zwróci wartość wygenerowaną przez P2 (ta sama sesja niezależnie od zasięgu). Jeżeli chcemy usunąć wszystkie wiersze z tabeli to należy rozważyć różnicę między usuwaniem z użyciem instrukcji DELETE i TRUNCATE. Pierwsza nie wpływa na aktualną wartość właściwości IDENTITY natomiast druga resetuje tę wartość. Aby to sprawdzić uruchomimy poniższe zapytanie:

TRUNCATE TABLE Sales.MyOrders;
SELECT IDENT_CURRENT('Sales.MyOrders') AS [IDENT_CURRENT];

Wynik:

IDENT_CURRENT --------------------------------------- 1

Otrzymaliśmy w wyniku 1. Aby przywrócić poprzednią wartość można użyć komendy DBCC CHECKIDENT:

DBCC CHECKIDENT('Sales.MyOrders', RESEED, 4);

Aby sprawdzić, że wartość została przywrócona dodamy jeden rekord:

INSERT INTO Sales.MyOrders(custid, empid, orderdate) VALUES(2, 2, '20120620');
SELECT * FROM Sales.MyOrders;

Wynik:

orderid custid empid orderdate ----------- ----------- ----------- ---------- 4 2 2 2012-06-20

Właściwość IDENTITY nie gwarantuje unikalności wartości w kolumnie ponieważ można wstawić wartość bezpośrednio za pomocą opcji IDENTITY_INSERT lub przywrócić wartość początkową za pomocą DBCC CHECKIDENT. Aby zagwarantować unikalność trzeba założyć klucz główny lub indeks unikalny na kolumnie. Właściwość IDENTITY nie gwarantuje również, że pomiędzy kolejnymi wartościami w wygenerowanej sekwencji nie będzie "dziur". Np gdy instrukcja INSERT nie powiedzie się to wartość IDENTITY nie wraca do poprzedniej wartości. Aby to sprawdzić spróbujmy dodać rekord który nie spełnia predykatu empid > 0:

INSERT INTO Sales.MyOrders(custid, empid, orderdate) VALUES(3, -1, '20120620');

Wynik:

Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "CHK_MyOrders_empid". The conflict occurred in database "TSQL2012", table "Sales.MyOrders", column 'empid'. The statement has been terminated.

Teraz wstawmy poprawny rekord i sprawdźmy jego wygenerowaną przez IDENTITY wartość:

INSERT INTO Sales.MyOrders(custid, empid, orderdate) VALUES(3, 1, '20120620');
SELECT * FROM Sales.MyOrders;

Wynik:

orderid custid empid orderdate ----------- ----------- ----------- ---------- 4 2 2 2012-06-20 6 3 1 2012-06-20

Wartość 5 została wygenerowana dla instrukcji INSERT która się nie powiodła dlatego powstała "dziura" między wartościami. Właściwość IDENTITY nie wspiera cykliczności. Oznacza to, że po dotarciu do maksymalnej wartości następna wartość zgłosi błąd przepełnienia. Aby to obejść trzeba przywrócić wartość właściwości IDENTITY.

SEQUENCE

SQL 2012 wprowadził do użycia obiekt sekwencji. W przeciwieństwie do właściwości kolumny IDENTITY, sekwencje są niezależnymi obiektami w bazie danych. Sekwencje nie mają wielu z ograniczeń jakie ma IDENTITY:

IDENTITY jest przypisana do jednej kolumny w jednej tabeli. Nie można usunąć jej z kolumny lub dodać do istniejącej kolumny. Kolumna musi zostać zdefiniowana z tą właściwością.
Czasem trzeba nadać klucz który jest unikalny pośród kilku tabel lecz IDENTITY działa tylko na jednej tabeli.
Nie można aktualizować kolumny z IDENTITY.
IDENTITY nie jest cykliczne.
TRUNCATE resetuje właściwość IDENTITY.

Sekwencje nie mają tych ograniczeń. Sekwencje są niezależnymi obiektami bazy danych. Nie są przypisane do konkretnej kolumny lub tabeli. Tworzenie sekwencji odbywa się z pomocą instrukcji CREATE SEQUENCE. Trzeba co najmniej podać nazwę sekwencji jak poniżej:

CREATE SEQUENCE <schema>.<object>;

Tak samo jak dal IDENTITY w sekwencjach dopuszczalne są typy numeryczne ze skalą 0. Jeżeli nie określimy tego to SQL Serwer domyślnie nada typ BIGINT. Jeżeli chcemy użyć innego typu trzeba dodać go poprzez wyrażenie na końcu deklaracji AS <type>. Sekwencje mają wiele właściwości które można ustawić, wszystkie mają domyślne wartości. poniżej znajdują się najważniejsze z nich oraz ich wartości domyślne:

INCREMENT BY - wartość inkrementacji, domyślnie 1.
MINVALUE - minimalna wartość, domyślnie minimalna wartość typu np dla INT to -2147483648.
MAXVALUE - maksymalna wartość, domyślnie maksymalna wartość dla typu.
CYCLE/NO CYCLE - czy sekwencja ma być cykliczna, domyślnie NO CYCLE.
STARTWITH - wartość startowa sekwencji, domyślnie wartość MINVALUE dla sekwencji rosnącej lub MAXVALUE dla malejącej.

Oto przykład definicji sekwencji która może służyć jako generator ID:

CREATE SEQUENCE Sales.SeqOrderIDs AS INT
MINVALUE 1
CYCLE;

Definicja zakłada minimalną wartość na 1 i zarazem wartość startową, ustawia również możliwość powtarzania cyklu. Aby pobrać następną wartość z sekwencji używamy instrukcji NEXT VALYE FOR <sequence name>.Przykładowo dla sekwencji zadeklarowanej powyżej to:

SELECT NEXT VALUE FOR Sales.SeqOrderIDs;

Funkcja ta może być wywoływana w wyrażeniach INSERT VALUES, INSERT SELECT, SEET w UPDATE, jako domyślna wartość DEFAULT i w innych miejscach. Nie można zmienić typu danych istniejącej sekwencji, ale można zmienić jej pozostałe właściwości poprzez użycie komendy ALTER SEQUENCE. Przykładowo tak wygląda zmiana aktualnej wartości na 1:

ALTER SEQUENCE Sales.SeqOrderIDs
RESTART WITH 1;

Aby zobaczyć jak działa wstawianie wartości sekwencji w czasie wstawiania wierszy odtworzymy tabelę Sales.MyOrders:

IF OBJECT_ID('Sales.MyOrders') IS NOT NULL DROP TABLE Sales.MyOrders;
GO
CREATE TABLE Sales.MyOrders
(
orderid INT NOT NULL
CONSTRAINT PK_MyOrders_orderid PRIMARY KEY,
custid INT NOT NULL
CONSTRAINT CHK_MyOrders_custid CHECK(custid > 0),
empid INT NOT NULL
CONSTRAINT CHK_MyOrders_empid CHECK(empid > 0),
orderdate DATE NOT NULL
);

Kolumna orderid celowo nie ma właściwości IDENTITY. Oto przykład wstawiania wartości z sekwencji w instrukcji INSERT VALUES:

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate) VALUES
(NEXT VALUE FOR Sales.SeqOrderIDs, 1, 2, '20120620'),
(NEXT VALUE FOR Sales.SeqOrderIDs, 1, 3, '20120620'),
(NEXT VALUE FOR Sales.SeqOrderIDs, 2, 2, '20120620');

Można również użyć sekwencji w instrukcji INSERT SELECT. W takiej sytuacji można opcjonalnie dodać klauzulę OVER z ORDER BY aby kontrolować kolejność wstawiania wartości sekwencji:

INSERT INTO Sales.MyOrders(orderid, custid, empid, orderdate)
SELECT
NEXT VALUE FOR Sales.SeqOrderIDs OVER(ORDER BY orderid),
custid, empid, orderdate
FROM Sales.Orders
WHERE custid = 1;

Można również użyć NEXT VALUE FOR w ograniczeniu DEFAULT i pozwolić na generowanie wartości automatycznie :

ALTER TABLE Sales.MyOrders
ADD CONSTRAINT DFT_MyOrders_orderid
DEFAULT(NEXT VALUE FOR Sales.SeqOrderIDs) FOR orderid;

INSERT INTO Sales.MyOrders(custid, empid, orderdate)
SELECT
custid, empid, orderdate
FROM Sales.Orders
WHERE custid = 2;

Tym razem wartości zostały wygenerowane automatycznie. Ta funkcjonalność jest rozszerzeniem standardu dzięki czemu można łatwo wprowadzić alternatywę dla IDENTITY. Jest to metoda bardziej elastyczna ponieważ generuje wartość domyślną tylko jeżeli taka nie została wprowadzona. Sekwencje tak samo jak IDENTITY nie gwarantują braku "dziur" między wartościami. Jeżeli w transakcji zostaną dodane nowe wartości sekwencji i transakcja ta się nie powiedzie to obiekt sekwencji nie jest przywracany do stanu poprzedniego. Sekwencje wspierają także buforowanie które kontroluje jak często wartości sekwencji są zapisywane na dysk w stosunku do zapisanych w pamięci. Przykładowo sekwencja z buforem 100 zapisuje na dysk raz na każde 100 wygenerowanych wartości. SQL Serwer trzyma dwie wartości w pamięci, aktualną wartość sekwencji oraz ile wartości zostało. Zaletą tego rozwiązania jest lepsza wydajność. Istnieje jednak ryzyko utraty tych danych prze nieplanowanym restarcie usługi . Używając buforowania uzyskujemy duży wzrost wydajności. Przy opcji NO CACHE, SQL Serwer musia zapisać na dysk każdą nową wartość pobraną z sekwencji. Buforując wartości wydajność jest lepsza, domyślnie bufor przechowuje 50 wartości lecz można zmienić tę wartość np:

ALTER SEQUENCE Sales.SeqOrderIDs
CACHE 100;

T-SQL wspiera także procedurę składowaną sp_sequence_get_range którą można użyć do alokacji zakresu wartości sekwencji. Wartość zakresu podajemy w parametrze @range_size i pobieramy pierwszą wartość przez parametr wyjściowy @range_first_value. Sekwencja modyfikowana jest dla takiej operacji tylko raz dodając do aktualnej wartości @range_size. Przegląd sekwencji w bazie danych możliwy jest poprzez użycie widoku sys.sequences. Zalety stosowania sekwencji są następujące:

Sekwencje nie są przypisane do konkretnej tabeli ani kolumny. Można je powiązać z kolumną poprzez pobranie wartości w ograniczeniu DEFAULT kolumny. Takie ograniczenie można w każdej chwili dodać lub usunąć.
Ponieważ sekwencja jest obiektem bazy danych to można użyć tej samej sekwencji aby generować klucz w wielu tabelach jednocześnie.
Można wygenerować wartość sekwencji przed jej użyciem np przechowywać w zmiennej.
Można zmieniać wartości kolumn których wartość została wygenerowana przez sekwencje.
Sekwencje mogą być cykliczne.
Instrukcja TRUNCATE nie resetuje wartości sekwencji

Ćwiczenia

Utworzenie sekwencji z właściwościami domyślnymi

Utwórz sekwencję o nazwie dbo.Seq1
```
CREATE SEQUENCE dbo.Seq1;
```

Uruchom zapytanie analizujące sekwencje zawarte w bazie TSQL2012:

SELECT TYPE_NAME(system_type_id) AS type
	,start_value
	,minimum_value
	,current_value
	,increment
	,is_cycling
FROM sys.sequences
WHERE object_id = OBJECT_ID('dbo.Seq1');

Utworzenie sekwencji z właściwościami

Zmień ustawienia sekwencji dbo.Seq1 tak aby posiadała typ INT, zaczynała się od 1 i umożliwiała cykliczność. Ponieważ nie można zmienić typu danych sekwencji to trzeba ją usunąć i utworzyć na nowo:
```
IF OBJECT_ID('dbo.Seq1') IS NOT NULL
	DROP SEQUENCE dbo.Seq1;

CREATE SEQUENCE dbo.Seq1 AS INT START
	WITH 1 CYCLE;
```

Podejrzyj właściwości sekwencji w widoku systemowym:

SELECT TYPE_NAME(system_type_id) AS type
	,start_value
	,minimum_value
	,current_value
	,increment
	,is_cycling
FROM sys.sequences
WHERE object_id = OBJECT_ID('dbo.Seq1');

Utwórz sekwencję która jest cykliczna i operuje tylko na liczbach dodatnich:

IF OBJECT_ID('dbo.Seq1') IS NOT NULL
	DROP SEQUENCE dbo.Seq1;

CREATE SEQUENCE dbo.Seq1 AS INT MINVALUE 1 CYCLE;

Podejrzyj właściwości sekwencji w widoku systemowym:

SELECT TYPE_NAME(system_type_id) AS type
	,start_value
	,minimum_value
	,current_value
	,increment
	,is_cycling
FROM sys.sequences
WHERE object_id = OBJECT_ID('dbo.Seq1');

Podsumowanie

SQL Serwer posiada dwie funkcjonalności pomagające generować sekwencje kluczy numerycznych: IDENTITY i SEQUENCE
IDENTITY jest właściwością kolumny i jest definiowana z wartością początkową i wartością inkrementacji. Wstawiając nowy wiersz do tabeli nie podaje się wartości dla kolumny z właściwością IDENTITY; zamiast tego SQL Serwer generuje wartość automatycznie.
Wygenerowaną przez IDENTITY wartość można odczytać za pomocą jednej z funkcji: SCOPE_IDENTITY, @@IDENTITY lub IDENT_CURRENT. Pierwsza pobiera ostatnią wartość wygenerowaną dla sesji i zasięgu, druga ostatnią wygenerowaną dla sesji a trzecia ostatnią bez względu na sesję lub zasięg.
Obiekt SEQUENCE jest niezależnym obiektem bazy danych. nie jest przywiązany do konkretnej kolumny czy tabeli.
Obiekt SEQUENCE pozwala na definicję wartości startowej, inkrementacji, wartości minimalnej i maksymalnej, cykliczności i buforowania.
Nową wartość z obiektu SEQUENCE pobieramy za pomocą funkcji NEXT VALUE FOR. Można używać jej w instrukcjach INSERT, UPDATE i ograniczeniach DEFAULT oraz przypisać do zmiennej.