Autonumeracja wierszy w SQL Server 2005
Teoria i praktyka mówią, że w relacyjnych bazach danych w każdej tabeli powinien istnieć pewien zestaw kolumn, w którym kombinacja wartości w każdym wierszu jest unikalna. Zależy nam na tym, by każdy wiersz różnił się od innych wierszy w tej samej tabeli.
Zawartość strony
 | Wstęp |
| Właściwość IDENTITY |
| Funkcje i słowa kluczowe związane z IDENTITY |
| Zmiana wartości IDENTITY |
| Podsumowanie |
Wstęp
Z punktu widzenia projektanta, administratora czy programisty baz danych najlepiej jest, jeśli system zarządzania bazami danych oferuje mechanizmy służące do automatycznego generowania wartości w kolumnach obranych w tabelach jako identyfikatory wierszy (celowo nie używam określenia „klucz główny”, ponieważ nie zawsze identyfikator idzie w parze z ograniczeniem typu PRIMARY KEY). W niniejszym artykule przedstawiam pewne możliwości automatycznego generowania wartości w kolumnach, najczęściej obieranych jako identyfikatory wierszy w systemie Microsoft SQL Server 2005.
Właściwość IDENTITY
Typami danych najczęściej stosowanymi do tworzenia kolumn–identyfikatorów w tabelach są typy liczb całkowitych. Do automatycznego generowania wartości w takich kolumnach w SQL Server 2005 można użyć IDENTITY. IDENTITY jest właściwością, którą można określić dla tylko jednej kolumny w każdej tabeli. Typ danych takiej kolumny musi należeć do zbioru: int, bigint, smallint, tinyint, decimal (koniecznie o skali równej 0, czyli bez części niecałkowitej), numeric (także koniecznie o skali równej 0). Właściwość IDENTITY pozwala na ustawienie liczby Identity Seed wstawianej w kolumnę-identyfikator w pierwszym wierszu tabeli oraz liczby Identity Increment, o jaką kolejne wartości wstawiane w kolumnę-identyfikator będą zmieniane - niekoniecznie zwiększane, ponieważ Identity Increment może być także liczbą ujemną (nie może być zerem i oczywiście musi być liczbą całkowitą). Domyślną wartością obu liczb jest 1 (autonumeracja od 1 co 1 - czyli 1,2,3,4...).
Kolumna, dla której określamy właściwość IDENTITY, nie może akceptować wartości NULL. Jeśli definiując tabelę nie określimy charakterystyki NULL kolumny z właściwością IDENTITY, to kolumna ta nie będzie akceptowała wartości NULL (taka charakterystyka jest wymuszana przez właściwość IDENTITY). Jeśli używasz replikacji i nie chcesz, by wstawienia wykonywane przez agentów replikacji nie zmieniały wartości IDENTITY, w deklaracji tej właściwości musisz użyć opcji NOT FOR REPLICATION.
Podczas wstawiania lub modyfikacji danych należy pomijać kolumnę z ustawioną właściwością IDENTITY. Próba wstawienia lub modyfikacji danych w tę kolumnę kończy się błędem. Jedynym sposobem by zmienić takie domyślne zachowanie tabeli z ustawioną na kolumnie właściwością IDENTITY jest użycie przełącznika IDENTITY_INSERT. Wykonanie poniższego kodu spowoduje umożliwienie wstawiania i modyfikacji wartości w kolumnie z właściwością IDENTITY w tabeli HumanResources.Department (należy pamiętać, by po dokonaniu wstawienia, ponownie ustawić opcję IDENTITY_INSERT dla tabeli w stan OFF).
SET IDENTITY_INSERT HumanResources.Department ON
Podczas wykonywania operacji BULK INSERT można użyć przełącznika KEEPIDENTITY, by wstawiać dane również do kolumny z ustawioną właściwością IDENTITY. Jeśli opcja KEEPIDENTITY nie jest określona, dane dla kolumny są weryfikowane, ale nie są wstawiane (zamiast tego serwer generuje automatycznie wartości zgodnie z aktualnymi wartościami IDENTITY).
Przykład definicji tabeli z właściwością IDENTITY (numerowanie od 1 co 1):
CREATE TABLE dbo.Employee ( EmployeeID int IDENTITY(1,1) NOT NULL, FirstName nvarchar(20) NOT NULL, LastName nvarchar(50) NOT NULL, HireDate smalldatetime NULL )
Funkcje i słowa kluczowe związane z IDENTITY
Z właściwością IDENTITY związanych jest kilka funkcji Transact-SQL, dzięki którym programiści mogą uzyskiwać informacje na temat ustawień dotyczących tej właściwości w tabelach.
Funkcja @@IDENTITY zwraca wartość IDENTITY wygenerowaną w jakiejkolwiek tabeli przez ostatnio wykonaną, w kontekście bieżącej sesji na lokalnym serwerze, składnię INSERT, SELECT INTO lub bulk copy. Jeżeli składnia nie jest wykonywana na tabeli z właściwością IDENTITY, funkcja @@IDENTITY zwraca wartość NULL. Jeżeli składnia uruchamia trigger, który wstawia dane do tabeli z ustawioną właściwością IDENTITY, to funkcja zwraca ostatnią wstawioną przez trigger wartość IDENTITY. Jeśli składnia INSERT wstawiająca dane do tabeli z ustawioną właściwością IDENTITY uruchamia trigger, który wstawia dane do tabeli bez ustawionej właściwości IDENTITY, to funkcja @@IDENTITY zwraca wartość IDENTITY wstawioną przez składnię INSERT uruchamiającą trigger.
Funkcja SCOPE_IDENTITY działa podobnie do funkcji @@IDENTITY. Różnica polega na tym, że SCOPE_IDENTITY zwraca ostatnio wstawioną w ramach bieżącej sesji w bieżącym zasięgu wartość IDENTITY. Zasięg oznacza w tym przypadku: procedurę składowaną, trigger, funkcję albo wsad (przy czym nie chodzi o wsady ograniczone słowem GO w składniach Transact-SQL, a o wsady rozumiane jako porcjowane wstawianie danych do tabeli).
Sytuację, gdy wspomniane funkcje zwracają różne wartości, ilustruje poniższy przykład:
USE tempdb
GO
CREATE TABLE TableA
(
A_ID int IDENTITY(1,1) PRIMARY KEY,
A_Name varchar(20) NOT NULL
)
GO
INSERT INTO TableA VALUES ('Tom')
INSERT INTO TableA VALUES ('Jim')
INSERT INTO TableA VALUES ('Mark')
SELECT * FROM TableA
GO
CREATE TABLE TableB
(
B_ID int IDENTITY(100,5) PRIMARY KEY,
B_Name varchar(20) NOT NULL
)
GO
INSERT INTO TableB VALUES ('Lisa')
INSERT INTO TableB VALUES ('Anna')
INSERT INTO TableB VALUES ('Linda')
SELECT * FROM TableB
GO
CREATE TRIGGER ATrig
ON TableA FOR INSERT
AS
INSERT INTO TableB VALUES ('')
GO
INSERT INTO TableA VALUES ('Sam')
GO
SELECT @@IDENTITY AS [@@IDENTITY value], SCOPE_IDENTITY() AS [SCOPE_IDENTITY() value]Funkcja IDENT_CURRENT nie jest ograniczona przez zasięg lub sesję. Zwraca ostatnią wygenerowaną wartość IDENTITY w wybranej tabeli, (której nazwa podawana jest jako parametr funkcji) w ramach dowolnej sesji. Jeśli tabela, której nazwa zostanie przekazana jako parametr do funkcji IDENT_CURRENT, nie ma ustawionej właściwości IDENTITY na żadnej kolumnie, funkcja IDENT_CURRENT zwraca NULL.
Funkcje IDENT_SEED oraz IDENT_INCR służą do zwracania informacji o ustawieniach właściwości IDENTITY określonych w momencie tworzenia tabeli. Funkcja IDENT_SEED zwraca wartość początkową IDENTITY, zaś funkcja IDENT_INCR zwraca liczbę, o jaką zmieniana jest wartość IDENTIY dla kolejnych rekordów wstawianych do tabeli. Obie funkcje pobierają jako parametr nazwę tabeli. Dla tabeli, która nie ma ustawionej właściwości IDENTITY na żadnej kolumnie, obie funkcje zwracają NULL.
Funkcja IDENTITY pozwala na wygenerowanie kolumny z właściwością IDENTITY w tabeli tworzonej za pomocą składni SELECT ... INTO. Przykład użycia tej funkcji:
SELECT IDENTITY(int,1,1) AS EmployeeID, LastName, FirstName INTO dbo.SimpleEmployees FROM dbo.Employees
Funkcja OBJECTPROPERTY pozwala zwrócić wartość wybranej właściwości określonego obiektu w bieżącej bazie danych. Funkcja ta pozwala między innymi zweryfikować, czy wybrana tabela z bieżącej bazy danych zawiera kolumnę z ustawioną właściwością IDENTITY. Odpowiednie wywołanie funkcji zwraca 1, jeśli w tabeli istnieje kolumna z ustawioną właściwością IDENTITY, lub 0, jeśli takiej kolumny w tabeli nie ma. Przykład użycia tej funkcji:
USE AdventureWorks
GO
SELECT
OBJECTPROPERTY(
OBJECT_ID('HumanResources.Department'),
'TableHasIdentity'
) AS [HumanReources.Department has IDENTITY]Tu uwaga – próba odwołania się do obiektu z innej niż bieżąca bazy danych kończy się zwróceniem przez funkcję wartości NULL. Podobny efekt jest następstwem wystąpienia błędu, (np. podania nazwy nieistniejącej tabeli) lub braku uprawnień do obiektu. Analogicznie do funkcji OBJECTPROPERTY działa funkcja OBJECTPROPERTYEX (z tą różnicą, że funkcja ta oferuje możliwość zwrócenia wartości także zaawansowanych właściwości obiektów).
Funkcja COLUMNPROPERTY pozwala zwrócić wartość dowolnej właściwości wybranej kolumny z określonej tabeli w bieżącej bazie danych. Jedną z takich właściwości jest właściwość IsIdentity. Odpowiednie wywołanie funkcji COLUMNPROPERTY zwraca 1, jeśli kolumna ma ustawioną właściwość IDENTITY, lub 0, jeśli jest inaczej. Analogicznie, jak w przypadku funkcji OBJECTPROPERTY – próba odwołania się do obiektu z innej niż bieżąca bazy danych kończy się zwróceniem przez funkcję wartości NULL. Podobny efekt jest następstwem wystąpienia błędu (np. podania nazwy nieistniejącej tabeli lub nieistniejącej kolumny) lub braku uprawnień do obiektu. Przykład użycia tej funkcji:
USE AdventureWorks
GO
SELECT
COLUMNPROPERTY(
OBJECT_ID('HumanResources.Department'),
'DepartmentID',
'IsIdentity'
) AS [Column DepartmentID is IDENTITY]W poleceniach języka Transact-SQL można użyć słów kluczowych IDENTITYCOL lub $IDENTITY do wskazania kolumny z ustawioną właściwością IDENTITY. Przykład:
USE AdventureWorks GO SELECT $IDENTITY, Name FROM HumanResources.Department
Zmiana wartości IDENTITY
Na skutek wycofywania transakcji lub usuwania rekordów w kolumnie z ustawioną właściwością IDENTITY, mogą powstać nieciągłości lub aktualna wartość IDENTITY może być niepotrzebnie zbyt duża, albo też aktualna wartość IDENTITY może być mniejsza od największej już wstawionej w kolumnę wartości. Używając polecenia DBCC CHECKIDENT można zmienić aktualną wartość IDENTITY. Polecenie to ma szereg zastosowań:
| • | pozwala stwierdzić, czy aktualna wartość IDENTITY nie jest mniejsza od największej wartości istniejącej w kolumnie z właściwością IDENTITY, np.: DBCC CHECKIDENT('HumanResources.Department',NORESEED) |
| • | pozwala ustawić właściwość IDENTITY tak, by następną wstawioną wartością była wartość o 1 większa od największej wartości istniejącej w kolumnie z właściwością IDENTITY, np.: DBCC CHECKIDENT('HumanResources.Department')albo DBCC CHECKIDENT('HumanResources.Department',RESEED) |
| • | pozwala ustawić właściwość IDENTITY tak, by następną wstawioną wartością była wartość zdefiniowana przez użytkownika, np.: DBCC CHECKIDENT('HumanResources.Department',RESEED,1) |
Powyższy wiersz kodu będzie miał różne następstwa w zależności od stanu tabeli. Jeżeli w tabeli HumanResources.Department nie było nigdy operacji wstawiania wierszy lub wszystkie rekordy zostały właśnie usunięte przez wykonanie składni TRUNCATE TABLE, to następną wartością wstawioną w kolumnę IDENTITY będzie 1 (wartość z trzeciego parametru składni DBCC CHECKIDENT). Jeśli natomiast w tabeli są jakiekolwiek rekordy lub wszystkie rekordy zostały usunięte przez wykonanie polecenia DELETE, to następną wartością wstawioną w kolumnę IDENTITY będzie 2 (suma wartości z trzeciego parametru składni DBCC CHECKIDENT oraz liczby, o jaką zmieniane są wartości IDENTITY).
Jeśli na kolumnie z ustawioną właściwością IDENTITY założone jest ograniczenie wymuszające unikalność wartości (PRIMARY KEY lub UNIQUE), to należy uważać, by przez wykonanie składni DBCC CHECKIDENT nie doprowadzić do wystąpienia błędu wynikającego z próby wygenerowania przez IDENTITY istniejącej już w kolumnie wartości.
Inną możliwością powrotu wartości IDENTITY do stanu początkowego (stanu, gdy w tabeli nie było żadnego rekordu) jest wykonanie na tabeli składni TRUNCATE TABLE, np.:
TRUNCATE TABLE HumanResources.Department
Składnia TRUNCATE TABLE cechuje się tym, że działa szybciej niż polecenie DELETE, powoduje założenie mniejszej ilości blokad na zasobach oraz kończy się zwróceniem komunikatu błędu, jeżeli do tabeli odwołuje się klucz obcy. Po udanym wykonaniu tej składni, jako że nie jest to składnia w pełni logowana w dzienniku transakcji, dobrym nawykiem jest wykonanie pełnej kopii bazy danych.
Podsumowanie
Właściwość IDENTITY daje możliwość tworzenia w sposób automatyczny wartości w kolumnach liczbowych służących do identyfikacji rekordów. Znakomicie sprawdza się w parze z kluczem głównym ustawionym na tej samej kolumnie. Wówczas serwer wykorzystuje fakt, że wartości wstawiane w kolumnę IDENTITY są sekwencją (niekoniecznie rosnącą) uporządkowanych liczb. To daje możliwość wykonywania operacji modyfikacji i przechowywania stron danych w pamięci bez potrzeby zbędnego odczytu z dysku, ponieważ kolejne rekordy znajdują swoje miejsce na ogół na stronie, która już została przez serwer odczytana przy wstawianiu pierwszej wartości IDENTITY, jaka została umieszczona na stronie. Oczywistą zaletą IDENTITY jest również automatyzacja procesu kluczowania – nadawania unikalnych wartości kolumnom kluczy głównych. Zdecydowanie polecam stosowanie tej właściwości wszędzie tam, gdzie kolumną klucza lub identyfikatora rekordów jest liczba całkowita.
 | Paweł Potasiński (Microsoft Certified Trainer, Asseco Business Solutions S.A.) |