TechCodeview

Słowo kluczowe IDENTITY jest właściwością w SQL Server. Jeśli kolumna tabeli jest zdefiniowana z właściwością tożsamości, jej wartość będzie automatycznie generowaną wartością przyrostową . Wartość ta jest tworzona przez serwer automatycznie. Dlatego jako użytkownik nie możemy ręcznie wprowadzić wartości do kolumny tożsamości. Dlatego jeśli oznaczymy kolumnę jako tożsamość, SQL Server zapełni ją w sposób automatyczny.

Składnia

Poniżej przedstawiono składnię ilustrującą użycie właściwości IDENTITY w SQL Server:

 IDENTITY[(seed, increment)] 

Powyższe parametry składni wyjaśniono poniżej:

Nasionko:Wskazuje wartość początkową wiersza załadowanego do tabeli. Domyślnie jego wartość wynosi 1.Przyrost:Wskazuje wartość przyrostową, która jest dodawana do wartości identyfikacyjnej ostatniego załadowanego wiersza. Domyślnie jego wartość wynosi 1.

Wyjaśnijmy tę koncepcję na prostym przykładzie.

Załóżmy, że mamy ' Student ' stół i chcemy Legitymacja studencka być generowane automatycznie. Mamy legitymacja studencka rozpoczynająca 10 i chcę go zwiększać o 1 z każdym nowym identyfikatorem. W tym scenariuszu należy zdefiniować następujące wartości.

Nasionko: 10

Przyrost: 1

 CREATE TABLE Student ( StudentID INT IDENTITY(10, 1) PRIMARY KEY NOT NULL, ) 

UWAGA: Dozwolona jest tylko jedna kolumna identyfikacyjna na tabelę w SQL Server.

Przykład IDENTYFIKACJI SQL Server

Rozumiemy, jak możemy wykorzystać właściwość tożsamości w tabeli. Właściwość tożsamości w kolumnie można ustawić podczas tworzenia nowej tabeli lub po jej utworzeniu. Tutaj zobaczymy oba przypadki z przykładami.

IDENTITY z nową tabelą

Poniższa instrukcja utworzy nową tabelę z właściwością tożsamości w określonej bazie danych:

 CREATE TABLE person ( PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL, Fullname VARCHAR(100) NOT NULL, Occupation VARCHAR(50), Gender VARCHAR(10) NOT NULL ); 

Następnie wstawimy nowy wiersz do tej tabeli z an WYJŚCIE klauzula wyświetlająca automatycznie wygenerowany identyfikator osoby:

 INSERT INTO person(Fullname, Occupation, Gender) OUTPUT inserted.PersonID VALUES('Sara Jackson', 'HR', 'Female'); 

Wykonanie tego zapytania wyświetli poniższe dane wyjściowe:

To wyjście pokazuje, że pierwszy wiersz został wstawiony z wartością dziesięć w Identyfikator osoby kolumna określona w kolumnie identyfikacyjnej definicji tabeli.

Wstawmy kolejny wiersz do tabela osób jak poniżej:

 INSERT INTO person(Fullname, Occupation, Gender) OUTPUT inserted.* VALUES('Mark Boucher', 'Cricketer', 'Male'), ('Josh Phillip', 'Writer', 'Male'); 

To zapytanie zwróci następujące dane wyjściowe:

Dane wyjściowe pokazują, że w kolumnie PersonID wstawiono drugi wiersz o wartości 11 i trzeci wiersz o wartości 12.

IDENTITY z istniejącą tabelą

Wyjaśnimy tę koncepcję, usuwając najpierw powyższą tabelę i tworząc ją bez właściwości tożsamości. Wykonaj poniższą instrukcję, aby usunąć tabelę:

 DROP TABLE person; 

Następnie utworzymy tabelę za pomocą poniższego zapytania:

 CREATE TABLE person ( Fullname VARCHAR(100) NOT NULL, Occupation VARCHAR(50), Gender VARCHAR(10) NOT NULL ); 

Jeśli chcemy dodać nową kolumnę z właściwością tożsamości w istniejącej tabeli, musimy użyć polecenia ALTER. Poniższe zapytanie doda PersonID jako kolumnę tożsamości w tabeli osób:

 ALTER TABLE person ADD PersonID INT IDENTITY(10,1) PRIMARY KEY NOT NULL; 

Dodanie wartości do kolumny tożsamości jawnie

Jeśli dodamy nowy wiersz do powyższej tabeli, określając jawnie wartość kolumny tożsamości, SQL Server zgłosi błąd. Zobacz poniższe zapytanie:

 INSERT INTO person(Fullname, Occupation, Gender, PersonID) VALUES('Mary Smith', 'Business Analyst', 'Female', 13); 

Wykonanie tego zapytania spowoduje następujący błąd:

Aby jawnie wstawić wartość kolumny tożsamości, musimy najpierw ustawić wartość IDENTITY_INSERT na ON. Następnie wykonaj operację wstawiania, aby dodać nowy wiersz do tabeli, a następnie ustaw wartość IDENTITY_INSERT na OFF. Zobacz poniższy skrypt kodu:

 SET IDENTITY_INSERT person ON /*INSERT VALUE*/ INSERT INTO person(Fullname, Occupation, Gender, PersonID) VALUES('Mary Smith', 'Business Analyst', 'Female', 14); SET IDENTITY_INSERT person OFF SELECT * FROM person; 

IDENTITY_INSERT WŁ pozwala użytkownikom umieszczać dane w kolumnach tożsamości, podczas gdy IDENTITY_INSERT WYŁ uniemożliwia im dodanie wartości do tej kolumny.

Wykonanie skryptu kodu spowoduje wyświetlenie poniższych danych wyjściowych, w których możemy zobaczyć, że identyfikator PersonID o wartości 14 został pomyślnie wstawiony.

Funkcja TOŻSAMOŚĆ

SQL Server udostępnia pewne funkcje tożsamości do pracy z kolumnami IDENTITY w tabeli. Te funkcje tożsamości są wymienione poniżej:

1. Funkcja @@TOŻSAMOŚĆ
2. Funkcja SCOPE_IDENTITY().
3. Funkcja IDENT_CURRENT
4. Funkcja TOŻSAMOŚĆ

Przyjrzyjmy się funkcjom TOŻSAMOŚĆ na kilku przykładach.

Funkcja @@TOŻSAMOŚĆ

@@IDENTITY to funkcja zdefiniowana przez system, która wyświetla ostatnią wartość tożsamości (maksymalna używana wartość tożsamości) utworzona w tabeli dla kolumny IDENTITY w tej samej sesji. Ta kolumna funkcji zwraca wartość tożsamości wygenerowaną przez instrukcję po wstawieniu nowego wpisu do tabeli. Zwraca a ZERO wartość, gdy wykonujemy zapytanie, które nie tworzy wartości IDENTITY. Zawsze działa w ramach bieżącej sesji. Nie można z niego korzystać zdalnie.

Przykład

Załóżmy, że bieżąca maksymalna wartość tożsamości w tabeli osób wynosi 13. Teraz dodamy jeden rekord w tej samej sesji, co zwiększy wartość tożsamości o jeden. Następnie użyjemy funkcji @@IDENTITY, aby uzyskać ostatnią wartość tożsamości utworzoną w tej samej sesji.

Oto pełny skrypt kodu:

 SELECT MAX(PersonID) AS maxidentity FROM person; INSERT INTO person(Fullname, Occupation, Gender) VALUES('Brian Lara', 'Cricket', 'Male'); SELECT @@IDENTITY; 

Wykonanie skryptu zwróci następujące dane wyjściowe, w których maksymalna używana wartość tożsamości wynosi 14.

Funkcja SCOPE_IDENTITY().

SCOPE_IDENTITY() jest funkcją zdefiniowaną przez system wyświetlić najnowszą wartość tożsamości w tabeli w ramach bieżącego zakresu. Ten zakres może być modułem, wyzwalaczem, funkcją lub procedurą składowaną. Jest podobna do funkcji @@IDENTITY(), z tą różnicą, że ta funkcja ma ograniczony zakres. Funkcja SCOPE_IDENTITY zwraca NULL, jeśli wykonamy ją przed operacją wstawiania, która generuje wartość w tym samym zakresie.

Przykład

Poniższy kod używa funkcji @@IDENTITY i SCOPE_IDENTITY() w tej samej sesji. Ten przykład najpierw wyświetli ostatnią wartość tożsamości, a następnie wstawi jeden wiersz do tabeli. Następnie wykonuje obie funkcje tożsamości.

 SELECT MAX(PersonID) AS maxid FROM person; INSERT INTO person(Fullname, Occupation, Gender) VALUES('Jennifer Winset', 'Actoress', 'Female'); SELECT SCOPE_IDENTITY(); SELECT @@IDENTITY; 

Wykonanie kodu spowoduje wyświetlenie tej samej wartości w bieżącej sesji i podobnym zakresie. Zobacz poniższy obraz wyjściowy:

Teraz na przykładzie zobaczymy, czym różnią się obie funkcje. Najpierw utworzymy dwie tabele o nazwach dane_pracownika I dział używając poniższego stwierdzenia:

 CREATE TABLE employee_data ( emp_id INT IDENTITY(1, 1) PRIMARY KEY NOT NULL, fullname VARCHAR(20) NULL ) GO CREATE TABLE department ( department_id INT IDENTITY(100, 5) PRIMARY KEY, department_name VARCHAR(20) NULL ); 

Następnie tworzymy wyzwalacz INSERT w tabeli dane_pracownika. Ten wyzwalacz jest wywoływany w celu wstawienia wiersza do tabeli działu za każdym razem, gdy wstawimy wiersz do tabeli dane_pracownika.

Poniższe zapytanie tworzy wyzwalacz wstawiania wartości domyślnej 'TO' w tabeli działów przy każdym zapytaniu wstawiającym w tabeli dane_pracownika:

ogólny błąd ochrony

 CREATE TRIGGER Insert_Department ON employee_data FOR INSERT AS BEGIN INSERT INTO department VALUES ('IT') END; 

Po utworzeniu wyzwalacza wstawimy jeden rekord do tabeli dane_pracownika i zobaczymy wynik funkcji @@IDENTITY i SCOPE_IDENTITY().

 INSERT INTO employee_data VALUES ('John Mathew'); 

Wykonanie zapytania spowoduje dodanie jednego wiersza do tabeli dane_pracownika i wygenerowanie wartości tożsamości w tej samej sesji. Po wykonaniu zapytania wstawiającego w tabeli dane_pracownika automatycznie wywołuje ono wyzwalacz w celu dodania jednego wiersza w tabeli działów. Wartość początkowa tożsamości wynosi 1 dla work_data i 100 dla tabeli działów.

Na koniec wykonujemy poniższe instrukcje, które wyświetlają wynik 100 dla funkcji SELECT @@IDENTITY i 1 dla funkcji SCOPE_IDENTITY, ponieważ zwracają wartość tożsamości tylko w tym samym zakresie.

 SELECT MAX(emp_id) FROM employee_data SELECT MAX(department_id) FROM department SELECT @@IDENTITY SELECT SCOPE_IDENTITY() 

Oto wynik:

Funkcja IDENT_CURRENT().

IDENT_CURRENT jest funkcją zdefiniowaną przez system wyświetlić najnowszą wartość IDENTITY generowane dla danej tabeli w ramach dowolnego połączenia. Ta funkcja nie uwzględnia zakresu zapytania SQL, które tworzy wartość tożsamości. Ta funkcja wymaga nazwy tabeli, dla której chcemy uzyskać wartość identyfikacyjną.

Przykład

Możemy to zrozumieć, otwierając najpierw dwa okna połączeń. W pierwszym oknie wstawimy jeden rekord, który wygeneruje wartość tożsamości 15 w tabeli osób. Następnie możemy zweryfikować tę wartość tożsamości w innym oknie połączenia, w którym możemy zobaczyć te same dane wyjściowe. Oto pełny kod:

 1st Connection Window INSERT INTO person(Fullname, Occupation, Gender) VALUES('John Doe', 'Engineer', 'Male'); GO SELECT MAX(PersonID) AS maxid FROM person; 2nd Connection Window SELECT MAX(PersonID) AS maxid FROM person; GO SELECT IDENT_CURRENT('person') AS identity_value; 

Wykonanie powyższych kodów w dwóch różnych oknach wyświetli tę samą wartość identyfikacyjną.

Funkcja TOŻSAMOŚĆ().

Funkcja IDENTITY() jest funkcją zdefiniowaną przez system używany do wstawiania kolumny tożsamości do nowej tabeli . Ta funkcja różni się od właściwości IDENTITY, której używamy w instrukcjach CREATE TABLE i ALTER TABLE. Funkcji tej możemy użyć jedynie w instrukcji SELECT INTO, która wykorzystywana jest podczas przenoszenia danych z jednej tabeli do drugiej.

Poniższa składnia ilustruje użycie tej funkcji w SQL Server:

 IDENTITY (data_type , seed , increment) AS column_name 

Jeśli tabela źródłowa zawiera kolumnę IDENTITY, tabela utworzona za pomocą polecenia SELECT INTO domyślnie ją dziedziczy. Na przykład , wcześniej utworzyliśmy tabelę osoba z kolumną tożsamości. Załóżmy, że tworzymy nową tabelę, która dziedziczy tabelę osób, używając instrukcji SELECT INTO z funkcją IDENTITY(). W takim przypadku otrzymamy błąd, ponieważ tabela źródłowa zawiera już kolumnę tożsamości. Zobacz poniższe zapytanie:

 SELECT IDENTITY(INT, 100, 2) AS NEW_ID, PersonID, Fullname, Occupation, Gender INTO person_info FROM person; 

Wykonanie powyższej instrukcji spowoduje zwrócenie następującego komunikatu o błędzie:

Utwórzmy nową tabelę bez właściwości tożsamości, korzystając z poniższej instrukcji:

 CREATE TABLE student_data ( roll_no INT PRIMARY KEY NOT NULL, fullname VARCHAR(20) NULL ) 

Następnie skopiuj tę tabelę za pomocą instrukcji SELECT INTO zawierającej funkcję IDENTITY w następujący sposób:

 SELECT IDENTITY(INT, 100, 1) AS student_id, roll_no, fullname INTO temp_data FROM student_data; 

Po wykonaniu instrukcji możemy ją zweryfikować za pomocą metody sp_pomoc polecenie wyświetlające właściwości tabeli.

Kolumnę TOŻSAMOŚĆ możesz zobaczyć w pliku Kuszące właściwości zgodnie z określonymi warunkami.

Jeśli użyjemy tej funkcji z instrukcją SELECT, SQL Server wyświetli następujący komunikat o błędzie:

Msg 177, poziom 15, stan 1, wiersz 2 Funkcji IDENTITY można używać tylko wtedy, gdy instrukcja SELECT zawiera klauzulę INTO.

Ponowne użycie wartości IDENTITY

Nie możemy ponownie użyć wartości tożsamości w tabeli SQL Server. Gdy usuniemy dowolny wiersz z tabeli kolumn tożsamości, w kolumnie tożsamości powstanie luka. Ponadto SQL Server utworzy lukę, gdy wstawimy nowy wiersz do kolumny tożsamości, a instrukcja nie powiedzie się lub zostanie wycofana. Luka wskazuje, że wartości tożsamości zostały utracone i nie można ich ponownie wygenerować w kolumnie IDENTITY.

Rozważ poniższy przykład, aby zrozumieć to praktycznie. Mamy już tabelę osób zawierającą następujące dane:

Następnie utworzymy dwie kolejne tabele o nazwie 'pozycja' , I ' pozycja_osoby ', używając następującej instrukcji:

 CREATE TABLE POSITION ( PositionID INT IDENTITY (1, 1) PRIMARY KEY, Position_name VARCHAR (255) NOT NULL ); CREATE TABLE person_position ( PersonID INT, PositionID INT, PRIMARY KEY (PersonID, PositionID), FOREIGN KEY (PersonID) REFERENCES person (PersonID), FOREIGN KEY (PositionID) REFERENCES POSITION (PositionID) ); 

Następnie staramy się wstawić nowy rekord do tabeli person i przypisać jej pozycję dodając nowy wiersz do tabeli person_position. Zrobimy to za pomocą zestawienia transakcji jak poniżej:

 BEGIN TRANSACTION BEGIN TRY -- insert a new row into the person table INSERT INTO person (Fullname, Occupation, Gender) VALUES('Joan Smith', 'Manager', 'Male'); -- assign a position to a new person INSERT INTO person_position (PersonID, PositionID) VALUES(@@IDENTITY, 10); END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH IF @@TRANCOUNT > 0 COMMIT TRANSACTION; 

Powyższy skrypt kodu transakcji pomyślnie wykonuje pierwszą instrukcję wstawiania. Ale druga instrukcja nie powiodła się, ponieważ w tabeli pozycji nie było pozycji o identyfikatorze 10. Dlatego cała transakcja została wycofana.

Ponieważ maksymalna wartość tożsamości w kolumnie PersonID wynosi 16, pierwsza instrukcja wstawiania pochłonęła wartość tożsamości 17, a następnie transakcja została wycofana. Dlatego jeśli wstawimy kolejny wiersz w tabeli Person, następną wartością tożsamości będzie 18. Wykonaj poniższą instrukcję:

 INSERT INTO person(Fullname, Occupation, Gender) VALUES('Peter Drucker',' Writer', 'Female'); 

Po ponownym sprawdzeniu tabeli osób widzimy, że nowo dodany rekord zawiera wartość tożsamości 18.

Dwie kolumny IDENTITY w jednej tabeli

Technicznie rzecz biorąc, nie jest możliwe utworzenie dwóch kolumn tożsamości w jednej tabeli. Jeśli to zrobimy, SQL Server zgłosi błąd. Zobacz następujące zapytanie:

 CREATE TABLE TwoIdentityTable ( ID1 INT IDENTITY (10, 1) NOT NULL, ID2 INT IDENTITY (100, 1) NOT NULL ) 

Kiedy wykonamy ten kod, zobaczymy następujący błąd:

Możemy jednak utworzyć dwie kolumny tożsamości w jednej tabeli, używając kolumny obliczeniowej. Poniższe zapytanie tworzy tabelę z kolumną obliczoną, która korzysta z oryginalnej kolumny tożsamości i zmniejsza ją o 1.

 CREATE TABLE TwoIdentityTable ( ID1 INT IDENTITY (10, 1) NOT NULL, SecondID AS 10000-ID1, Descriptions VARCHAR(60) ) 

Następnie dodamy trochę danych do tej tabeli za pomocą poniższego polecenia:

 INSERT INTO TwoIdentityTable (Descriptions) VALUES ('Javatpoint provides best educational tutorials'), ('www.javatpoint.com') 

Na koniec sprawdzamy dane tabeli za pomocą instrukcji SELECT. Zwraca następujące dane wyjściowe:

Na obrazku widzimy, jak kolumna SecondID działa jak druga kolumna tożsamości, zmniejszając się o dziesięć od wartości początkowej wynoszącej 9990.

Błędne przekonania dotyczące kolumny IDENTITY programu SQL Server

Użytkownik DBA ma wiele błędnych przekonań dotyczących kolumn tożsamości SQL Server. Poniżej znajduje się lista najczęstszych błędnych przekonań dotyczących kolumn tożsamości, które można zobaczyć:

Kolumna IDENTITY jest UNIKALNA: Zgodnie z oficjalną dokumentacją SQL Server właściwość tożsamości nie może zagwarantować, że wartość kolumny będzie unikalna. Aby wymusić niepowtarzalność kolumny, musimy użyć klucza podstawowego, ograniczenia UNIQUE lub indeksu UNIQUE.

Kolumna IDENTITY generuje kolejne liczby: Oficjalna dokumentacja wyraźnie stwierdza, że ​​przypisane wartości w kolumnie tożsamości mogą zostać utracone w przypadku awarii bazy danych lub ponownego uruchomienia serwera. Może to powodować luki w wartości identyfikacyjnej podczas wstawiania. Lukę można również utworzyć, gdy usuniemy wartość z tabeli lub wycofamy instrukcję wstawiania. Wartości generujące luki nie mogą być dalej wykorzystywane.

Kolumna IDENTITY nie może automatycznie wygenerować istniejących wartości: Kolumna tożsamości nie może automatycznie wygenerować istniejących wartości, dopóki właściwość tożsamości nie zostanie ponownie zapełniona za pomocą komendy DBCC CHECKIDENT. Pozwala nam dostosować wartość początkową (wartość początkową wiersza) właściwości tożsamości. Po wykonaniu tego polecenia SQL Server nie będzie sprawdzał nowo utworzonych wartości, które już znajdują się w tabeli, czy też nie.

Kolumna IDENTITY jako KLUCZ PODSTAWOWY wystarczy do zidentyfikowania wiersza: Jeśli klucz podstawowy zawiera kolumnę tożsamości w tabeli bez żadnych innych unikalnych ograniczeń, kolumna może przechowywać zduplikowane wartości i zapobiegać niepowtarzalności kolumny. Jak wiemy, klucz podstawowy nie może przechowywać zduplikowanych wartości, ale kolumna tożsamości może przechowywać duplikaty; zaleca się, aby nie używać klucza podstawowego i właściwości tożsamości w tej samej kolumnie.

Użycie niewłaściwego narzędzia do odzyskania wartości tożsamości po wstawieniu: Powszechnym błędnym przekonaniem jest także nieświadomość różnic pomiędzy funkcjami @@IDENTITY, SCOPE_IDENTITY(), IDENT_CURRENT i IDENTITY() polegającymi na pobieraniu wartości tożsamości bezpośrednio z właśnie wykonanej instrukcji.

Różnica między SEKWENCJĄ a TOŻSAMOŚCIĄ

Do generowania numerów automatycznych używamy zarówno SEKWENCJI, jak i TOŻSAMOŚCI. Ma jednak pewne różnice, a główna różnica polega na tym, że tożsamość zależy od tabeli, podczas gdy sekwencja nie. Podsumujmy ich różnice w formie tabelarycznej:

Wniosek

W tym artykule przedstawiono pełny przegląd właściwości IDENTITY w SQL Server. Dowiedzieliśmy się tutaj, jak i kiedy wykorzystywana jest właściwość tożsamości, jakie są jej różne funkcje, jakie są błędne przekonania oraz czym różni się ona od sekwencji.