Spis treści:

Kategoria:IdentitySQL Server


Identity jako identyfikator

Zauważyłem, że dla wielu, zwłaszcza początkujących adeptów baz danych i SQL Server słowo kluczowe IDENTITY służy tylko i wyłącznie do tworzenia identyfikatorów. Przyznam, że w zdecydowanej większości przypadków na tym zadanie IDENTITY się kończy. Są jednak przypadki, w których IDENTITY może bardzo utrudniać lub wręcz uniemożliwiać wykonanie pewnych operacji w standardowy sposób. Przyjrzyjmy się zatem dokładniej IDENTITY i dowiedzmy się, jak radzić sobie w wielu przypadkach i na co uważać.

Zadanie podstawowe - tworzenie sekwencji

Wiem dobrze, że akurat ten sposób korzystania z IDENTITY nie stwarza żadnych problemów. Aby jednak temat był przedstawiony spójnie, pokażę o co chodzi. Ci, którzy znają IDENTITY z tej strony, mogą od razu przejść do dalszej części. Słowo kluczowe IDENTITY umieszcza się tuż po definicji kolumny w tabeli, to jest po nazwie zmiennej i nazwie jej typu. IDENTITY przyjmuje dwa parametry - pierwszy to wartość początkowa, a drugi to wartość, o jaką zwiększany będzie licznik wartości początkowej. Zwykle przekazuje się tam (1,1).

IDENTITY może być użyte również bez wskazywania parametrów. W takim przypadku IDENTITY będzie równoważne z zapisem IDENTITY(1,1).

Rzućmy okiem na listing zaprezentowany poniżej:

--tworzymy tabelę
CREATE TABLE Identity_Test
(
--zaczynamy od 1, każda następna wartość będzie
--większa od poprzedniej o 1. Kolejnymi wartościami będą
--zatem 2, 3, 4, 5 itd.
ID int IDENTITY(1,1),
Value int
)

--wstawiając dane do tabeli pomijamy wartość oznaczoną
--IDENTITY, jest ona wstawiana automatycznie
INSERT INTO Identity_Test VALUES (10)
INSERT INTO Identity_Test VALUES (20)
INSERT INTO Identity_Test VALUES (30)

Przykład nie wymaga chyba dalszych wyjaśnień. Aby przekonać się, że to działa, skorzystajmy z instrukcji SELECT:

SELECTFROM Identity_Test

Przyjrzyjmy się jeszcze wynikom:

IDValue
110
220
330

Wszystko działa zgodnie z planem. To właśnie w taki sposób identity będzie najczęściej wykorzystywane. Sprawdźmy co się stanie, gdy przekażemy inne parametry IDENTITY. Pokazuje to poniższy przykład:

--usuwamy tabelę z poprzedniego przykładu
DROP TABLE Identity_Test

--tworzymy nową tabelę
CREATE TABLE Identity_Test
(
--zaczynamy od -35, każda następna wartość będzie
--większa od poprzedniej o 2. Kolejnymi wartościami będą
--zatem -33, -31, -29, -27 itd.
ID int IDENTITY(-35,2),
Value int
)

--wstawiając dane do tabeli pomijamy wartość oznaczoną
--identity, jest ona wstawiana automatycznie
INSERT INTO Identity_Test VALUES (10)
INSERT INTO Identity_Test VALUES (20)
INSERT INTO Identity_Test VALUES (30)

SELECTFROM Identity_Test

Wynikiem działania przedstawionego skryptu będzie taki oto zbiór danych:

IDValue
-3510
-3320
-3130

Wspomniałem wcześniej, że wartość identity wstawiana jest automatycznie. Sprawdźmy, czy da się tam wstawić to, co my chcemy, a nie to, co chce IDENTITY. Spróbujmy wykonać następującą instrukcję:

INSERT INTO Identity_Test
VALUES (4, 40)

Otrzymamy następujący komunikat:

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'Identity_Test' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Tłumacząc komunikat na język polski dowiadujemy się, że można jawnie wstawić do kolumny IDENTITY naszą wartość, ale tylko wtedy, gdy użyta jest lista kolumn i włączona jest opcja IDENTITY_INSERT. Dla niektórych może to być nieco tajemniczy zapis. O co chodzi?

Ręczne wstawianie wartości IDENTITY

W zdecydowanej większości przypadków pozostawimy IDENTITY możliwość zarządzania wartościami kolumny, ale trzeba wiedzieć, jak radzić sobie w tych pozostałych. Po piersze, należy włączyć opcję IDENTITY_INSERT. Przyjrzyjmy się poniższemu listingowi

:
--Włączamy opcję IDENTITY_INSERT
SET IDENTITY_INSERT [NazwaTabeli] ON
--Wyłączamy opcję IDENTITY_INSERT
SET IDENTITY_INSERT [NazwaTabeli] OFF

Pierwsza z zaprezentowanych instrukcji włącza, natomiast druga wyłącza opcję IDENTITY_INSERT. W środku umieszczamy instrukcje, których zadaniem jest jawne ustawienie wartości kolumny IDENTITY (wraz z wartościami innych kolumn). Spróbujmy zatem wykonać instrukcję INSERT:

SET IDENTITY_INSERT Identity_Test ON
INSERT INTO Identity_Test VALUES (4, 40)
SET IDENTITY_INSERT Identity_Test OFF

Próba wykonania tego skryptu zakończy się niepowodzeniem, a komunikat będzie dokładnie taki sam: An explicit value ...

Ustawiliśmy opcję IDENTITY_INSERT, ale jest jeszcze coś - być może nie użyliśmy listy kolumn. Cóż to takiego? Nazwa jest tak ogólna, że trudno się domyślić. Popatrzmy zatem na przykład i wszystko powinno się wyjaśnić:

SET IDENTITY_INSERT Identity_Test ON
INSERT INTO Identity_Test(ID, Value) VALUES (4, 40)
SET IDENTITY_INSERT Identity_Test OFF

Ta tajemnicza lista kolumn została pogrubiona. Jawne podawanie listy kolumn jest dość męczące. Tutaj kolumny są dwie, ale gdyby tych kolumn było kilkanaście, co nie jest szczególnie rzadkim przypadkiem, musielibyśmy je po kolei wpisywać (oczywiście tylko te, które chcemy wstawić).

Przykład przeniesienia całej tabeli z kolumną IDENTITY do innej, z taką samą strukturą, pokazany jest tutaj: Jak skopiować zawartość tabeli w SQL Server.

Teraz coś, co można już nazwać drążeniem tematu. Najczęściej stosowanie opcji IDENTITY_INSERT sprowadza się do trzech etapów: włączamy opcję, wykonujemy operacje, wyłączamy opcję. Domyślnie opcja jest wyłączona. A gdyby tak ją włączyć? Przekonajmy się zatem wykonując poniższy skrypt:

SET IDENTITY_INSERT Identity_Test ON
INSERT INTO Identity_Test VALUES (50)

Szybko nasze zapały gasną. Pojawia się błąd następującej treści:

Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table 'Identity_Test' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.

Komunikat tej treści powinien uświadomić nas, że najprawdopodobniej gdzieś zapomnieliśmy o wyłączeniu opcji IDENTITY_INSERT.

Wpływ ręcznie wstawianych wartości na ciągłość sekwencji

Przed przystąpieniem do kolejnych eksperymentów należy usunąć wcześniej utworzone tabele. Tytuł sekcji każe nam się zastanowić, czy ręczne wstawianie wartości kolumny IDENTITY ma wpływ na bieżącą wartość sekwencji. Podchodząc do tego zagadnienia analitycznie, ciężko wskazać jak się to powinno zachować. Skoro ciężko wskazać - sprawdźmy. Pierwszy skrypt testowy pokazany jest poniżej:

CREATE TABLE Identity_Test
(
ID int IDENTITY,
Value int
)

INSERT INTO Identity_Test VALUES (10)
INSERT INTO Identity_Test VALUES (20)

SET IDENTITY_INSERT Identity_Test ON
INSERT INTO Identity_Test(ID, Value) VALUES (3,50)
SET IDENTITY_INSERT Identity_Test OFF

INSERT INTO Identity_Test VALUES (30)

SELECTFROM Identity_Test

Przyjrzyjmy się teraz rezultatom powyższych instrukcji:

IDValue
110
220
350
430

Co się stało? Okazuje się, że ręczne wstawianie rekordu również zwiększa licznik IDENTITY. Wstawiona wartość 30 otrzymała numer 4, a nie 3, jak wynikałoby z bieżącej wartości licznika. Czy zatem każda operacja INSERT zwiększa wskazanie licznika IDENTITY? Wątpliwości trochę powiny rozwiać kolejne instrukcje:

--Kontynuujemy wstawianie
SET IDENTITY_INSERT Identity_Test ON
INSERT INTO Identity_Test(ID, Value) VALUES (10,11)
SET IDENTITY_INSERT Identity_Test OFF

INSERT INTO Identity_Test VALUES (12)

SELECTFROM Identity_Test

Popatrzmy teraz na wyniki:

IDValue
110
220
350
430
1011
1112

Widać, że to nie INSERT zwiększa wartość IDENTITY. Czyżby działało to tak, że wartość, która została wstawiona jako ostatnia w miejsce IDENTITY staje się aktualną pozycją w sekwencji? Pójdźmy za ciosem i popatrzmy na kolejny fragment. Jest to dalszy ciąg badań w zakresie zachowań kolumny IDENTITY.

--Kontynuujemy wstawianie
SET IDENTITY_INSERT Identity_Test ON
INSERT INTO Identity_Test(ID, Value) VALUES (10,11)
SET IDENTITY_INSERT Identity_Test OFF

INSERT INTO Identity_Test VALUES (12)

SELECTFROM Identity_Test

Popatrzmy na wynik (istniejące wcześniej rekordy zostały pominięte):

IDValue
......
621
1222

Sprawa zaczyna się wyjaśniać. Widać, że po wyłączeniu opcji IDENTITY_INSERT bieżąca wartość ustawiana jest na maksymalną w ramach wszystkich już istniejących rekordów. Żeby oczywiście nie było tak różowo, takie zjawisko ma miejsce tylko w przypadku dodatniego przyrostu. Zanim przedstawię dokładnie algorytm generowania wartości w kolumnach IDENTITY, przyjrzyjmy się ostatniemu przykładowi z tej sekcji (przetestowanie skryptu wymaga usunięcia poprzedniej tabeli Identity_Test):

CREATE TABLE Identity_Test
(
ID int IDENTITY(0,-2),
Value int
)

INSERT INTO Identity_Test VALUES (0)

SET IDENTITY_INSERT Identity_Test ON
INSERT INTO Identity_Test(ID, Value) VALUES (-1,-1)
INSERT INTO Identity_Test(ID, Value) VALUES (3,3)
SET IDENTITY_INSERT Identity_Test OFF

INSERT INTO Identity_Test VALUES (-3)

SELECTFROM Identity_Test

A teraz czas na ostatnie wyniki:

IDValue
00
-1-1
33
-3-3

Bardziej spostrzegawcze osoby już pewnie znalazły regułę kierującą generowaniem kolejnych wartości dla kolumny IDENTITY. Działa ona tak:

  1. Jeżeli rekordy wstawiane są automatycznie, do wartości bieżącej (na początku to pierwszy parametr) dodawana jest wartość zmiany (drugi parametr)
  2. Jeżeli wartość wstawiania jest ręcznie, wtedy trafia ona taka, jaką podamy
  3. W momencie wyłączania opcji IDENTITY_INSERT:
    • Jeżeli wartość zmiany jest dodatnia, wyszukiwany jest element największy i on staje się wartością bieżącą
    • Jeżeli wartość zmiany jest ujemna, wyszukiwany jest element najmniejszy i on staje się wartością bieżącą

Algorytm nie jest szczególnie skomplikowany. Pojawia się pytanie - po co tak gmatwać to całe zagadnienie? Po co to wyliczanie wartości maksymalnych i minimalnych? Argument jest jasny, choć nie tak oczywisty, żeby wszyscy go dostrzegli. Trzeba wiedzieć, że na kolumnie IDENTITY często zakłada się klucz główny, który z natury powinien być unikalny. Gdybyśmy za bardzo pomieszali z wartościami sekwencji, w pewnym momencie moglibyśmy zderzyć się z problemem powtórzenia klucza. Wystarczyłoby, żebyśmy raz cofnęli się w numeracji, aby uzupełnić dziury i nieszczęście gotowe. Algorytm przechodzenia do wartości maksymalnej/minimalnej jest prosty, ale likwiduje całe niebezpieczeństwo naruszenia więzów unikalności klucza.

IDENTITY na innych typach danych

Stosowanie IDENTITY z typem int jest dość powszechne. Tak powszechne, że nikt nie zastanawia się, jakie są alternatywy. Nie jest ich wiele, ale nalezałoby o nich wspomnieć. Przyjrzyjmy się następującemu fragmentowi skryptu SQL:

CREATE TABLE Identity_Test
(
Identity_column char(5) IDENTITY
)

Po takim zabiegu dość szybko możemy się przekonać, na czym można założyć IDENTITY. Są to typy int, bigint, smallint, tinyint, decimal lub numeric bez miejsc po przecinku. Poza tym na żadnej z takich kolumn nie może być dopuszczona wartość NULL. Wyjaśnia to taki oto komunikat:

Msg 2749, Level 16, State 2, Line 1
Identity column 'Identity_column' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

W związku z powyższym, prawidłowa będzie instrukcja:

CREATE TABLE Identity_Test
(
Identity_column numeric IDENTITY
)

Nie ma natomiast możliwości stworzenia kolumny jednocześnie z opcją NULL i IDENTITY. Nieprawidłowa będzie zatem taka instrukcja:

CREATE TABLE Identity_Test
(
Identity_column numeric IDENTITY NULL
)

Próba wykonania takiej instrukcji zakończy się takim oto komunikatem:

Msg 8147, Level 16, State 1, Line 1
Could not create IDENTITY attribute on nullable column 'Identity_column', table 'Identity_Test'.

Przyjrzyjmy się teraz, jak zachowuje się kolumna IDENTITY w przypadku usuwania danych z tabel.

DELETE i TRUNCATE - czyszczenie tabel IDENTITY

Czy usuwanie rekordów z bazy ma wpływ na wartość IDENTITY? Ma to z pewnością jakiś sens. Po co bowiem zostawiać jakieś dziury w sekwencji? W praktyce takie rozwiązanie jest to do zrealizowania tylko wtedy, gdy rekordy usuwane są z końca. Zarządzanie dziurami w środku byłoby zbyt kosztowne. Nie tylko ze względu na zastosowany algorytm, ale także na konieczność przebudowy indeksów, które często zakładane są na kolumnach IDENTITY. Sprawdzanie, czy rekordy usuwane są na końcu też nie jest realizowane przez SQL Server. Po części również z powodu wydajności, ale także dla uproszczenia sposobu zarządzania danymi. Przypomnijmy sobie, że IDENTITY może mieć różny przyrost, w środku sekwencji mogą się znaleźć ręcznie wstawiane wartości. Zastanówmy się jeszcze nad jednym zagadnieniem. Wyobraźmy sobie, że w bazie jest rekord zamówienia o identyfikatorze 2564. Sprytny sprzedawca może wpaść na genialny pomysł - nie muszę pamiętać szczegółów całego zamówienia - myśli - zapamiętam tylko identyfikator i potem sobie go sprawdzę. Doświadczenie podpowiada, że jest to powszechna praktyka. A gdyby tak usunąć i na to miejsce wstawić nowy rekord (sam by się wstawił, gdyby licznik IDENTITY się cofnął).

Powyższe rozważania doprowadziły do tego, że IDENTITY nigdy samo się nie cofa przy usuwaniu rekordów instrukcją DELETE. Nawet wtedy, gdy usuwane są wszystkie rekordy. Przeanalizujmy poniższy skrypt i przyjrzyjmy się rezultatom:

CREATE TABLE Identity_Test
(
Identity_column int IDENTITY,
Value int
)

INSERT INTO Identity_Test VALUES(1001)
INSERT INTO Identity_Test VALUES(1002)
DELETE FROM Identity_Test
INSERT INTO Identity_Test VALUES(1003)

SELECTFROM Identity_Test

Wynik będzie następujący:

Identity_ColumnValue
31003

Jak napisałem wcześniej, wartość licznika IDENTITY nie uległa zmianie.

Bardziej doświadczone osoby wiedzą o istnieniu innej instrukcji do usuwania wszystkich danych z tabeli - TRUNCATE TABLE. Czym się ona różni od użytej przed chwilą instrukcji DELETE FROM Identity_Test? Oprócz tego, że nie da się na nią nałożyć żadnych warunków, wpływa ona na IDENTITY. Przyjrzyjmy się teraz nieco zmodyfikowanej wersji poprzedniego skryptu. Zamienimy DELETE FROM na TRUNCATE TABLE.

CREATE TABLE Identity_Test
(
Identity_column int IDENTITY,
Value int
)

INSERT INTO Identity_Test VALUES(1001)
INSERT INTO Identity_Test VALUES(1002)
TRUNCATE TABLE Identity_Test
INSERT INTO Identity_Test VALUES(1003)

SELECTFROM Identity_Test

Wynik wykonania skryptu będzie następujący:

Identity_ColumnValue
11003

Łatwo zauważyć, że w wyniku instrukcji TRUNCATE TABLE IDENTITY zostało przywrócone do stanu początkowego.

Skoro zwykłe usuwanie nie działa na IDENTITY, to czy istnieje jakaś metoda sterowania wartością licznika?

Sterowanie IDENTITY

Zanim przystąpimy do przestawiania wskaźnika IDENTITY, warto poznać metody pobierania bieżących ustawień.

Pobieranie informacji o pozycji IDENTITY

Źródłem informacji o wszystkich kolumnach IDENTITY jest widok systemowy sys.identity_columns. Aby pobrać informacje o wszystkich tego typu kolumnach można skorzyystać z następującej instrukcji:

SELECT OBJECT_NAME(object_id) Tabela, name Kolumna,
seed_value Początek, increment_value Przyrost,
last_value Wartość
FROM sys.identity_columns

Jeżeli wykonaliśmy test z instrukcją TRUNCATE TABLE, otrzymamy rezultat podobny do poniższego:

TabelaKolumnaPoczątekPrzyrostWartość
...............
Identity_TestIdentity_Column111
...............

Wśród innych kolumn IDENTITY, będzie i nasza.

Wartość w kolumnie Wartość to ostatnio wstawiony klucz. Jeżeli do tabeli nie wstawiono nic, lub przed chwilą wykonaliśmy instrukcję TRUNCATE TABLE, będzie tam NULL.

Sam widok zawiera oczywiście znacznie więcej informacji, między innymi o typie kolumny, precyzji i wartości dla typów numeric i decimal, ale najczęściej nie są one potrzebne. Zainteresowanych zachęcam do szczegółowego zbadania widoku sys.identity_columns.

Inną metodą na pobranie bieżących parametrów IDENTITY jest skorzystanie z jednej z trzech funkcji: IDENT_CURRENT, IDENT_INCR i IDENT_SEEK. Pierwsza z nich zwraca ostatnio użytą wartość IDENTITY, druga zwraca wartość przyrostu, trzecia natomiast początkową wartość IDENTITY. Przykład użycia funkcji pokazane jest na poniższym listingu:

SELECT IDENT_CURRENT('Identity_Test') Wartość,
IDENT_INCR('Identity_Test') Przyrost,
IDENT_SEED('Identity_Test') Początek

Przykładowy rezultat takiej operacji mógłby wyglądać następująco:

WartośćPrzyrostPoczątek
411

Jest jeszcze inna możliwość pobrania informacji o IDENTITY. Polega ona na użyciu instrukcji DBCC CHECKIDENT. Wywołanie instrukcji przyjmuje najczęściej takie oto formy:

--Pobierz informacje o IDENTITY i ustaw licznik na
--maksymalną wartość kolumny (patrz algorytm SET IDENTITY_INSERT
DBCC CHECKIDENT ('Identity_Test')

--Pobierz informacje o IDENTITY i nic więcej nie rób
DBCC CHECKIDENT ('Identity_Test',NORESEED)

Wynikiem powyższych instrukcji będzie komunikat podobny do poniższego:

Checking identity information: current identity value '4', current column value '4'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Przedstawione instrukcje nie działają dokładnie tak samo. Pierwsza z nich, oprócz pokazania informacji, próbuje naprawić ustawienia IDENTITY zgodnie z algorytmem zaprezentowanym przy omawianiu SET IDENTITY_INSERT. Wniosek z tego jest następujący: coś musi psuć ustawienia.

Dowiedzieliśmy się, jak pobierać informację o kolumnie IDENTITY. Teraz czas na modyfikowanie bieżących ustawień. To są właśnie te operacje, które mogą wszystko zepsuć, ale dają nam też pewną elastyczność i swobodę.

Przestawianie bieżącej pozycji IDENTITY

Aby przestawić bieżącą wartość IDENTITY należy skorzystać z tej samej funkcji, której użyliśmy do pobierania wartości i naprawiania sekwencji. Instrukcja będzie miała następującą postać:

--ustaw nową wartość kolumny IDENTITY na 2
DBCC CHECKIDENT ('Identity_Test',RESEED,2)

Ustawiając IDENTITY w ten sposób trochę ryzykujemy. Nie ma gwarancji, że sekwencja będzie nam generować unikatowe wartości. Przyjrzyjmy się poniżemu skryptowi. Po każdej ważnej instrukcji DBCC CHECKIDENT zamieściłem komunikaty zwracane przez SQL Server Management Studio.

TRUNCATE TABLE Identity_Test
INSERT INTO Identity_Test VALUES(1)
INSERT INTO Identity_Test VALUES(2)
DBCC CHECKIDENT ('Identity_Test',NORESEED)
--Komunikat:
--Checking identity information: current identity value '2', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKIDENT ('Identity_Test',RESEED,1)
--Komunikat:
--Checking identity information: current identity value '2', current column value '1'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKIDENT ('Identity_Test',NORESEED)
--Komunikat:
--Checking identity information: current identity value '1', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Po dwóch instrukcjach INSERT ostatnia wartość IDENTITY wynosi 2, czyli tyle, ile maksymalna wartość w kolumnie. Teraz ustawiamy IDENTITY na 1. Pierwszy komunikat jest trochę mylący, bo wskazuje, że IDENTITY dalej wynosi 2. Zmienia się natomiast wskazywana wartość w kolumnie. Według mnie jest to trochę mylące, bo przecież przestawiamy IDENTITY, a nie wartość w kolumnie. Aby to dobrze zrozumieć, należy przyjąć takie oto rozumowanie: sami przekazujemy wartość kolumny i każemy IDENTITY przestawić się. IDENTITY widząc, że wartość kolumny wynosi 1, przestawia IDENTITY na 1. O tym jednak dowiadujemy się dopiero po wykonaniu dodatkowej instrukcji. Nie ważne jak na to patrzymy, rezultat końcowy jest zadowalający, o czym informuje nas kolejna instrukcja DBCC CHECKIDENT. Tam już mamy to, co chcieliśmy: IDENTITY ustawione na 1, a wartość w kolumnie 2. Jakie są konsekwencje takiego stanu rzeczy? Popatrzmy na kolejne instrukcje:

INSERT INTO Identity_Test VALUES(10)
DBCC CHECKIDENT ('Identity_Test',NORESEED)
--Komunikat:
--Checking identity information: current identity value '2', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SELECTFROM Identity_Test

Po wstawieniu nowego rekordu okazuje się, że została mu przydzielona wartość IDENTITY równa 2. Tu mogą pojawić się problemy. Rekordy nie mogą być już jednoznacznie wskazane przy pomocy kolumny IDENTITY, a o to najczęściej chodzi. Jeżeli na kolumnie ustawiony jest klucz główny lub sprawdzanie unikatowości, instrukcja zakończy się błędem. Dane w tabeli będą wyglądać następująco:

Identity_ColumnValue
11
22
210

Popatrzmy jeszcze na sposób naprawiania IDENTITY. Zostanie to pokazane przy pomocy następującego skryptu:

DBCC CHECKIDENT ('Identity_Test',RESEED,1)
--Komunikat:
--Checking identity information: current identity value '2', current column value '1'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKIDENT ('Identity_Test',NORESEED)
--Komunikat:
--Checking identity information: current identity value '1', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.

--Naprawiamy IDENTITY
DBCC CHECKIDENT ('Identity_Test')
--Komunikat:
--Checking identity information: current identity value '1', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC CHECKIDENT ('Identity_Test',NORESEED)
--Komunikat:
--Checking identity information: current identity value '2', current column value '2'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
INSERT INTO Identity_Test VALUES(11)
DBCC CHECKIDENT ('Identity_Test',NORESEED)
--Komunikat:
--Checking identity information: current identity value '3', current column value '3'.
--DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SELECTFROM Identity_Test

Tym razem po ustawieniu IDENTITY na 1 wykonujemy instrukcję naprawiającą wskazania. Jak można się przekonać, IDENTITY po naprawie działa znakomicie - przestawia się na maksymalną wartość. Potwierdza to też test z instrukcją INSERT i wynik instrukcji SELECT:

Identity_ColumnValue
11
22
210
311

Widać wyraźnie, że wartości 11 przydzielony został numer 3, czyli kolejna, niepowtarzalna wartość z sekwencji liczb naturalnych.

Mogliśmy się już wielokrotnie przekonać, że numer przydzielony przez IDENTITY jest wstawiany automatycznie. Pozostaje nam do rozpatrzenia jeden z najważniejszych problemów związanych z IDENTITY - pobieranie ostatnio przydzielonej wartości.

Pobieranie ostatnio wstawionej wartości IDENTITY

Wyobraźmy sobie, że wykonaliśmy instrukcję INSERT. Teraz chcemy wstawić drugi rekord, który jest związany kluczem obcym z tym pierwszym. Należy zatem ustawić ten klucz obcy na wartość wygenerowaną przez IDENTITY. Można to oczywiście rozwiązać przy pomocy jednej z zaprezentowanych metod do pobierania bieżącej wartości IDENTITY, ale nie jest to rozsądne rozwiązanie. Przede wszystkim dlatego, że SQL Server jest wielozadaniowy. Jeżeli instrukcje wywoływane są w jednej paczce, w jednej transakcji, wtedy problem jest mniejszy, bo najprawdopodobniej tabela jest zablokowana. Jeżeli są to kolejne instrukcje wykonywane niezależnie, to nie mamy gwarancji, że inny użytkownik w międzyczasie nie wstawił nowego rekordu i nie zmienił nam wskazania IDENTITY. W takich przypadkach należy skorzystać z innych metod. Jedną z nich jest użycie funkcji sytemowej @@IDENTITY. Popatrzmy na listing zaprezentowany poniżej:

CREATE TABLE Parent
(
ID int IDENTITY,
Nazwa varchar(8),
CONSTRAINT PK_ID_Parent PRIMARY KEY (ID)
)
CREATE TABLE Child
(
ID int IDENTITY,
ID_Parent int,
Nazwa varchar(8),
CONSTRAINT PK_ID_Child PRIMARY KEY (ID),
CONSTRAINT FK_Child_Parent FOREIGN KEY
(ID_Parent) REFERENCES Parent(ID)
)

INSERT INTO Parent VALUES('Parent1')
--Można użyć @@IDENTITY bezpośrednio w instrukcji
INSERT INTO Child VALUES(@@IDENTITY'Child1_1')

INSERT INTO Parent VALUES('Parent2')
--Zapamiętujemy IDENTITY, aby użyć tej wartości dwukrotnie
DECLARE @idParent int@@IDENTITY
INSERT INTO Child VALUES(@idParent, 'Child1_2')
--Każda operacja INSERT zmienia @@IDENTITY
PRINT @@IDENTITY --2
INSERT INTO Child VALUES(@idParent, 'Child2_2')
PRINT @@IDENTITY --3

SELECTFROM Parent
SELECTFROM Child

Przykład trochę bardziej rozbudowany, ale myślę, że analiza nie będzie stwarzała problemów. Tworzymy sobie tabelę-rodzica Parent, i tabelę-dziecko Child. Obie tabele mają kolumnę IDENTITY, pierwsza z nich ma klucz główny na kolumnie IDENTITY, druga natomiast klucz obcy wskazujący na kolumnę IDENTITY w pierwszej tabeli. Jak wygląda uzupełnianie danych?

Dla ułatwienia przyjmijmy, że @@IDENTITY to zmienna globalna ustawiana po każdej operacji INSERT, bo generalnie tak się ta funkcja zachowuje. Po wstawieniu rekordu rodzica wstawiamy rekord dziecka, przekazując do kolumny klucza obcego wartość @@IDENTITY. Po wstawieniu rekordu dziecka wartością @@IDENTITY staje się wartość IDENTITY dziecka. Jeżeli wstawiamy dwa rekordy wskazujące na tego samego rodzica, należy wartość @@IDENTITY gdzieś zapamiętać. Pokazane jest to dla drugiego rekordu rodzica. Wśród różnych instrukcji wstawiłem dwie operacje PRINT. Można się przekonać, że rzeczywiście każda operacja INSERT zmienia naszą globalną wartość @@IDENTITY. Pierwsza instrukcja PRINT wypisze 2, a druga 3.

Jest też inna metoda, w wielu przypadkach bezpieczniejsza. Polega ona na użyciu funkcji systemowej SCOPE_IDENTITY(). Zdarza się, że wywołanie operacji INSERT pociągnie za sobą wywołanie innych zdarzeń, na przykład uruchomienie wyzwalacza lub mechanizmu replikacji. Wyzwalacz lub replikacja mogą wykonywać jakieś własne operacje INSERT bezpośrednio wpływając na wartość @@IDENTITY. Funkcja SCOPE_IDENTITY() odporna jest na takie zawirowania. Rozpoznaje ona kontekst wywołania i zwraca ostatnio wygenerowane IDENTITY w ramach tego kontekstu. Wyzwalacz (trigger) i replikacja dostają swój kontekst i nie wpływają na wynik SCOPE_IDENTITY() naszej operacji. Przyjrzyjmy się następującemu przykładowi:

CREATE TABLE IdentityA
(
ID int IDENTITY,
Nazwa varchar(8)
)
CREATE TABLE IdentityB
(
ID int IDENTITY (5,1),
Nazwa varchar(8)
)
GO
CREATE TRIGGER ForInsert
ON IdentityA
FOR INSERT AS
INSERT INTO IdentityB
SELECT Nazwa
FROM inserted
GO
INSERT INTO IdentityA VALUES('Tekst 1')
SELECT @@IDENTITY [Identity],
       SCOPE_IDENTITY() [Scope identity]

Tworzymy sobie dwie tabele z kolumnami IDENTITY oraz wyzwalacz, który zadziała dla operacji INSERT. W tym wyzwalaczu również wykonujemy instrukcję INSERT, która, tak jak pierwotna operacja, wymusi działanie IDENTITY. Warto zwrócić uwagę na początkowe wartości IDENTITY: 1 w przypadku pierwszej tabeli i 5 w przypadku tabeli używanej przez wyzwalacz. Wynikiem działania powyższego skryptu będzie taka tabela:

IdentityScope identity
51

Widać wyraźnie, że @@IDENTITY przejęło wartość licznika z wyzwalacza, natomiast SCOPE_IDENTITY() zwróciło wartość właściwą.

IDENTITY i wstawianie wielu wartości jednocześnie

Wszystkie metody zaprezentowane do tej pory mają jedną wadę - żadna z nich nie nadaje się do pobierania IDENTITY w przypadku wstawiania wielu wartości jednocześnie. W takich przypadkach należy skorzystać z frazy OUTPUT. Z doświadczenia wiem, że jest to mało znana, choć wyjątkowo przydatna funkcja SQL Server. Przyjrzyjmy się przykładowemu listingowi zaprezentowanemu poniżej:

CREATE TABLE Identity_Test
(
  ID int IDENTITY,
  Val int
)

--Wstaw jeden rekord i pobierz go
INSERT INTO Identity_Test
OUTPUT Inserted.*
VALUES (1)

--Wstaw dwa rekordy i pobierz ich IDENTITY
INSERT INTO Identity_Test
OUTPUT Inserted.ID
VALUES (11),(12)

Wynikiem działania powyższego skryptu będą dwa zbiory danych. Zaprezentowano je poniżej:

IDVal
11
 
ID
2
3

Technika z użyciem frazy OUTPUT jest, co naturalne, odporna na zmiany kontekstu. Jedyna wada tego rozwiązania jest taka, że nie działa w wersjach wcześniejszych niż SQL Server 2005.

Podsumowanie IDENTITY

Zaprezentowane w tym artykule techniki powinny być wystarczające w zdecydowanej większości przypadków. Jeżeli jest inaczej, proszę o informację w komentarzach.

Kategoria:IdentitySQL Server

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
Dzieki za rozjasnienie zagadnienia upsert. wlasnie sie ucze programowania :).
Co się stanie gdy spróbuję wyszukać:
SELECT * FROM NV_Airport WHERE Code='SVO'
SELECT * FROM V_Airport WHERE Code=N'SVO'
(odwrotnie są te N-ki)
Będzie konwersja czy nie znajdzie żadnego rekordu?