Kilka słów o autoinkrementacji w SQL SERVER

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:

CREATE TABLE IdentityTable
(
id INT IDENTITY, 
name VARCHAR(20)
)

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:

CREATE TABLE IdentityTable2

(
id INT IDENTITY(1,5),
name VARCHAR(20)
)

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:

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

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:

SELECT @@IDENTITY

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

SELECT IDENT_CURRENT(table_name)

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

select 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:

dbcc checkident(table_name)

Po jej wykonaniu następujący otrzymamy komunikat:

Checking identity information: current identity value '1', current column value '1'.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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ę:

dbcc checkident(table_name,RESEED, 10)

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:

INSERT INTO test(comment, remark) VALUES('test comment','test remark')

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