Kilka słów o autoinkrementacji w SQL SERVER

AutoincrementInSQLServer_00

Autoinkrementacja w SQL Server jest jedną z bardzo popularnych funkcjonalności. Ale czy wiemy o niej wszystko?Wydaje mi się, że nie. W większości przypadków jej użycie sprowadza się do nadania klucza głównego do tabeli i wskazanie aby był auto numerowany. Dlatego też postanowiłem zebrać parę faktów na ten temat i umieścić w ramach niniejszego artykułu – zaczynajmy!

Pierwszym wartym odnotowania faktem jest to iż, samą funkcjonalność można wprowadzić do naszej bazy na dwa sposoby. Większość programistów zna tylko jedną z nich, a mianowicie poprzez użycie słowa kluczowego IDENTITY. Metoda ta jest bardzo prosta w użyciu i to właśnie o niej powiemy sobie dzisiaj. Jednak w tyle głowy miejmy na uwadze, że w SQL Server 2012 powstała swoistego rodzaju alternatywa, którą jest obiekt SEQUENCE, któremu poświęcę osobny artykuł (Możecie go znaleźć tutaj).

Tak więc na samym początku stwórzmy sobie kolumnę autonumerowaną przy pomocy IDENTITY. Jak zapewne większość z was wie możemy to osiągnąć w bardzo prosty sposób jako część zapytania tworzącego tabelę tj. CREATE TABLE tak jak zostało to przedstawione poniżej:

Tabela stworzona w ten sposób będzie zachowywała się w zamierzony sposób tzn. w momencie wstawienia nowej wartości do kolumny “name” otrzyma ona automatycznie kolejny numer porządkowy w kolumnie “id”.

Sama klauzula IDENTITY ma dwa opcjonalne parametry tzw. seed oraz increment value. Seed (ziarno) jest niczym innym jak wartością od jakiej auto-numerowanie ma się rozpocząć (domyślnie jest to 1),  increment value (wartość inkrementacji) jest to liczba o jaką każdy kolejny wiersz ma być powiększany. Dla przykładu taka definicja tabeli:

Po wstawieniu kolejnych wartości do kolumny “name” otrzymają one następujące identyfikatory:

1,6,11 i tak dalej..

Świetnie, ale co jeżeli chcemy bezpośrednio dodać wartość do kolumny z ustawionym IDENTITY ? Oczywiście możemy to zrobić ale najpierw musimy włączyć odpowiednie ustawienie tzw. IDENTITY_INSERT:

Po włączeniu tego ustawienia wstawianie wartości bezpośrednio do kolumny IDENTITY nie jest już problemem. Warto pamiętać, żeby po wstawieniu wartości ustawienie to wyłączyć aby uniknąć omyłkowego wstawienia wartości do pola.

Zdarza się również, że chcemy sprawdzić ostatnią nadaną wartość IDENTITY. Jak to często bywa w przypadku SQL Server możemy tą informację uzyskać na kilka sposobów.

Pierwszym z nich jest odczytanie wartości wbudowanej zmiennej tylko do odczytu o nazwie @@IDENTITY:

Możemy również użyć funkcji  IDENT_CURRENT przyjmującej jako argument nazwę tabeli z której IDENTITY chcemy odczytać:

Ostatnią możliwością jest użycie bezparametrowej funkcji SCOPE_IDENTITY()

W tym miejscu może pojawiać się zasadne pytanie – jaka jest różnica i kiedy użyć której? Odpowiedź jest stosunkowo prosta – wystarczy wiedzieć co robi każda z nich – tak więc:

@@IDENTITY zwraca ostatnio nadaną wartość auto-numerowaną w całej bazie danych

IDENT_CURRENT zwraca wartość auto-numerowaną z konkretnej tabeli

SCOPE_IDENTITY zwraca wartość auto-numerowaną dla bieżącego kontekstu.

Jak widać znaczenie każdej z nich jest stosunkow proste. Jedynym wartym wyjaśnienia pojęciem jest kontekst w SCOPE_IDENTITY. Najłatwiej będzie to wytłumaczyć na przykładzie. Wyobraźmy sobie, że mamy wyzwalacz typu AFTER, który wstawia wartości do tabeli B zaraz po tym gdy coś zostało wstawione do tabeli A (obie tabele mają kolumny auto-numerowane) wtedy też @@IDENTITY zwróci ostatnio wstawioną wartość (czyli z tabeli B) natomiast SCOPE_IDENTITY() zwróci wartość autonumerowaną z bieżącego kontekstu czyli z tabeli A.

Informację o IDENTITY możemy również zdobyć w nieco inny sposób, a mianowicie poprzez wykonanie komendy DBCC:

Po jej wykonaniu następujący otrzymamy komunikat:

Oczywiście jest to informacja stricte użyteczna dla celów poglądowych – użycie jej w zautomatyzowanym obiekcie jak np. procedura składowana nie ma sensu. Jednak ta komenda DBCC ma jedną bardzo użyteczną cechę tj. dzięki niej możemy zmienić bieżącą wartość pola z IDENTITY. Sprawdźmy to na przykładzie. Wykonajmy poniższą komendę:

Po tej zmianie kolejny wstawiony wiersz otrzyma w kolumnie z IDENTITY wartość równą 11. W ramach tej komendy mamy również dodatkowy trzeci parametr WITH NO_INFOMSGS – jednak nie wnosi on zbyt wiele pozwala po prostu na to, że nie będzie wyświetlana żadna informacja po jego wykonaniu.

W przypadku omawianego tematu można powiedzieć, że Microsoft obdarzył nas całą gamą funkcji pozwalających odczytać bieżące ustawienia. Dlatego też aby odczytać ustawienia konkretnej tabeli możemy np. użyć funkcji IDENT_SEED(‘table_name’), IDENT_INCR(‘table_name’), które jak sama nazwa wskazuje w rezultacie zwracają bieżącą wartość Seed oraz increment value – warto je zapamiętać bo niejednokrotnie są one bardzo użyteczne w programowaniu procedur składowanych opierających się na tabelach z auto-inkrementacją.

Ostatnią kwestią jaką chciałbym poruszyć w ramach niniejszego artykułu jest wstawianie wartości do tabeli z IDENTITY. Jak możemy to zrobić? W bardzo prosty sposób – wystarczy, że po prostu ominiemy nazwę kolumny z IDENTITY w klauzuli INSERT tak jak zostało to przedstawione poniżej:

To by było na tyle. Mam nadzieję, że ten krótki post przekazał wam parę wskazówek na temat autoinkrementacji w SQL Server.

Follow me

Adrian Chodkowski

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

3 Comments

  1. AvatarGość

    Mnie trochę zdziwił i denerwował skok wartości autoinkrementacji po restarcie usługi SQL Server. Bo to jest jednak dziwne zachowanie. Na początku wartości ID zachowują się normalnie, czyli np. 1, 2, 3, …, a po restarcie serwera wyskakuje na przykład ID 102, 103, 104, … W sieci znalazłem informację, że można to ominąć stosując przełącznik “-t272” ustawiony w okienku parametrów startowych. To rozwiązuje problem, ale pozostaje pytanie, po co Microsoft zastosował takie dziwne skakanie wartości Identity?

    Reply
  2. Adrian ChodkowskiAdrian Chodkowski (Post author)

    To zachowanie rzeczywiście jest dziwne jednakże warto pamiętać o tym, że IDENTITY nie zapewnia ciągłości wartości – zachowanie to jest związane z mechanizmem cache’owania wartości autonumerowanych, które są tracone w momencie restartu. Mechanizm ten jest obligatoryjny w przypadku IDENTITY ze względów wydajnościowych. Aby mieć pewność ciągłości wartości należy użyć obiektu SEQUENCE z opcją NOCACHE – myślę, że to będzie dobry pomysł na kolejny post i zapisuje sobie jako “do napisania” 🙂

    Reply
    1. AvatarGość

      To ciekawe. O samym Identity w internecie napisano sporo, ale zwykle nie wspomina się o tym mechanizmie tracenia ciągłości numeracji. Po prostu stwierdza się że coś takiego jest, i podaje sposób ominięcia problemu. Na jakiejś stronie znalazłem komentarz, że Microsoft nie traktuje tego zachowania jako błąd, lecz raczej jako cechę serwera. Interesujące, że dwie wiodące firmy wymieniają się pomysłami. Microsoft wprowadził obiekt Sequence, a Oracle wprowadziło autonumerację Identity. Ale to w wersji 12c. Ja mam 11g Express, więc nie mogę sprawdzić czy działa identycznie jak w SQL Server.

      Reply

Leave a Comment

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