Tym razem krótko i na temat. Ostatnio kolejny raz spotkałem się z oczekiwaniem przez programistę, że wartości kolumny typu identity będą zachowywać ciągłość. Pomyślałem więc, że po prostu zrobię na ten temat wpis i do niego będę ewentualnie odsyłał niezorientowanych w temacie.

Serwer SQL jest środowiskiem współdzielonym, w którym większość operacji dzieje się jednocześnie. Trudno więc – mają tego świadomość – oczekiwać, że w jakiś magiczny sposób autoinkrementująca kolumna będzie w stanie zachować ciągłość wartości. Wystarczy spróbować sobie wyobrazić własną implementacje takiego rozwiązania. Dość szybko okazuje się, że nie jest to trywialne zagadnienie. Ten typ (nomen omen 😉 ) tak ma – będą w nim dziury i trzeba mieć tego świadomość. Na poparcie poniższy przykład:

-----------------------------------------------------------
-- jak zachowuje się Identity przy transakcjach
-----------------------------------------------------------
if object_id('tempdb..#T') is not null
  drop table #T;
create table #T (id int identity(1, 1), v varchar(50));
-- pierwsze wstawienie
insert into #T values ('start');
-- jak to wygląda?
select top 3 * from #T order by id desc;
-- seria danych
insert into #T
  select name from sys.databases;
-- jak to wygląda?
select top 3 * from #T order by id desc;
-- seria danych w transakcji
begin transaction
insert into #T
  select name from sys.databases;
-- cofnięcie transakcji
rollback
-- jak to wygląda?
select top 3 * from #T order by id desc;
-- ostatnie wstawienie
insert into #T values ('stop');
-- jak to wygląda?
select top 3 * from #T order by id desc;

Wykonanie powyższego skryptu jasno pokazuje, że zrobienie dziury w wartościach identity jest banalne.

Czy w takim razie trzeba żyć z takimi dziurami? Czy można jednak jakoś im zapobiec lub chociaż zasklepić? Jeśli komuś dziury przeszkadzają lub ilość dodawanych danych jest znacząca i zaczyna brakować wolnych numerów (wyczerpuje się zakres liczba całkowitych) to można je zasklepiać lub pracować na tabelach tymczasowych i dopiero po stuprocentowym upewnieniu się, że transakcji nie trzeba będzie wycofywać – dopisać do tabeli. Samo zasklepianie dziur wymaga niestety stworzenia kopii tabeli (co nie jest banalne jeżeli istnieją klucze obce lub więzy integralności) i wypełnienia kolumny identity ręcznie w sposób ciągły. Służy do tego polecenie set identity_insert, dokładna składnia to: set identity_insert <tabela> {on|off}. On włącza możliwość wypełniania kolumny identity swoimi wartościami, off ją wyłącza. Bezwzględnie konieczne jest też wymienienie wszystkich pól tabeli i ich stosowne wypełnienie, np. tak:

set identity_insert #T on;
insert into #T (id, v) values (1001, 'start'), (1002, 'pomiędzy'), (1003, 'stop');
set identity_insert #T off;

Uwaga! Przypominam – nie da się zastosować polecenia update dla wartości identity (stąd w poleceniu podano konkretny tryb działania identity_insert, nie ma analogicznego identity_update).