Ten artykuł opisuje funkcję tożsamości w SQL Server z przykładami i różnicami między tymi funkcjami.
przegląd kolumn tożsamości
w SQL Server tworzymy kolumnę tożsamości, aby automatycznie generować wartości przyrostowe. Generuje wartości na podstawie predefiniowanych wartości seed (wartość początkowa) i step(przyrost). Załóżmy na przykład, że mamy tabelę pracowników i chcemy wygenerować EmployeeID automatycznie., Mamy początkowy identyfikator pracownika 100 i dalej chcemy zwiększyć każdy nowy EmpID o jeden. W tym przypadku musimy zdefiniować następujące wartości.
- Seed: 100
- Krok: 1
wstawmy kilka rekordów w tej tabeli i wyświetlmy rekordy.
widać, że pierwszy pracownik otrzymuje wartość ID 100, a każda nowa wartość ID rekordów otrzymuje przyrost o jeden.
mamy kilka przydatnych funkcji tożsamości w SQL Server do pracy z kolumnami tożsamości w tabeli. Przyjrzyjmy się poniższym funkcjom tożsamości z przykładami.,
- SQL @@IDENTITY Function
- SQL SCOPE_IDENTITY() Function
- SQL IDENT_CURRENT Function
- SQL IDENTITY Function
SQL @@IDENTITY Function
używamy funkcji systemowej @@IDENTITY, aby zwrócić maksymalną wartość używanej tożsamości w tabeli kolumny IDENTITY w bieżącej sesji. Po wstawieniu wiersza w tabeli kolumna funkcji @ @ IDENTITY podaje wartość IDENTITY wygenerowaną przez instrukcję. Jeśli uruchomimy jakiekolwiek zapytanie, które nie wygenerowało wartości tożsamości, otrzymamy wartość NULL na wyjściu., Tożsamość SQL @ @ działa w ramach bieżącej sesji. Nie możemy go używać na serwerze zdalnym lub połączonym.
zrozummy to poniższym przykładem.
- Krok 1: mamy aktualną maksymalną wartość tożsamości 110 w tabeli EmployeeData
- Krok 2: w bieżącej sesji wstawiamy rekord w tabeli EmployeeData., Zwiększa wartość tożsamości o jeden
- Krok 3: możemy sprawdzić, czy maksymalna używana wartość tożsamości to 111
- Krok 4: używamy funkcji SELECT @ @ IDENTITY w bieżącej sesji, aby uzyskać wartość tożsamości wygenerowaną w tej sesji
wstawimy wiele rekordów w tej tabeli i wykonamy SELECT @@IDENTITY.
jeśli wstawimy wiele rekordów w sesji, SELECT @ @ Identity Zwraca maksymalną wartość tożsamości wygenerowaną w tej sesji., W poniższym przykładzie wstawiamy trzy rekordy, które zwiększają wartości tożsamości o trzy z bieżącej wartości tożsamości 111.
jeśli instrukcja nie wygenerowała żadnych wartości identyfikacyjnych, zwraca wartości NULL na wyjściu.
funkcja SQL SCOPE_IDENTITY ()
używamy funkcji SCOPE_IDENTITY (), aby zwrócić ostatnią wartość tożsamości w tabeli pod bieżącym zakresem. Zakres może być modułem, wyzwalaczem, funkcją lub procedurą składowaną., Możemy rozważyć funkcję SQL SCOPE_IDENTITY() podobną do funkcji @ @ IDENTITY, ale jest ona ograniczona do określonego zakresu. Zwraca wartość NULL, jeśli ta funkcja jest zaangażowana, zanim instrukcja insert wygeneruje wartość w tym samym zakresie.
w poniższym przykładzie widzimy, że zarówno @ @ IDENTITY, jak i SCOPE_IDENTITY () zwracają tę samą wartość w bieżącej sesji i podobny zakres.
zrozummy różnicę między scope_identity() i @@IDENTITY z innym przykładem.,
Załóżmy, że mamy dwie tabele: tabelę pracowników i tabelę działów. Tworzymy INSERT trigger w tabeli EmployeeData. Po wstawieniu dowolnego wiersza na EmployeeData wywołuje zdefiniowany wyzwalacz do wstawiania wiersza w działach.
w poniższym zapytaniu tworzymy wyzwalacz do wstawienia domyślnej wartości ” IT ” w tabeli działów dla każdej wstawki w tabeli EmployeeData.,ф7″>
|
1
2
3
4
5
6
|
utworzyć wyzwalacz T_INSERT_DEPARTMENT
w na employeedata książki
, aby wstawić stanu
start
wstaw działów wartości (’to')
koniec;
|
przyjrzyjmy się wstawić wartości w tabeli danych i wyświetlania danych wyjściowych jak @@Identity i scope_identity() funkcji.,
w bieżącej sesji wstawiliśmy dane do tabeli EmployeeData. Generuje tożsamość w tej tabeli. Identity seed value to 1 for the EmployeeData table.
po wstawieniu wartości w tabeli EmployeeData, uruchamia się wyzwalacz do wstawiania wartości w tabeli Departments. Wartość seed tożsamości wynosi 100 dla tabeli departamentów.
- otrzymujemy wyjście 100 dla funkcji SELECT @ @ IDENTITY
- funkcja SCOPE_IDENTITY Zwraca wartość identity tylko w bieżącym zakresie., Daje wyjście 1 dla tej funkcji
funkcja SQL IDENT_CURRENT ()
używamy funkcji IDENT_CURRENT, aby zwrócić ostatnią wartość tożsamości wygenerowaną dla określonej tabeli pod dowolnym połączeniem. Nie uwzględnia zakresu zapytania SQL, które generuje wartość tożsamości. Musimy określić tabelę, dla której chcemy sprawdzić wartość tożsamości.
na poniższym screenie widzimy, że wstawiamy dane do ID sesji 64 i generujemy wartość identity 2 w tabeli EmployeeData.,
możemy sprawdzić w innym oknie Połączenia bieżącą wartość identity dla tabeli EmployeeData i uzyskać taki sam wynik jak identity value 2.,
|
1
|
SELECT IDENT_CURRENT(’EmployeeData') AS IdentityValue
|
SQL IDENTITY Function
In my previous article, we explored SQL SELECT INTO Statement, to create a new table and inserted data into it from the existing table., Za pomocą funkcji SQL IDENTITY możemy wstawić do instrukcji wartości identity w tabeli utworzonej przez SQL SELECT.
domyślnie, jeśli tabela źródłowa zawiera kolumnę IDENTITY, to tabela utworzona przy użyciu polecenia SELECT INTO dziedziczy ją. Rozważ scenariusz, w którym chcesz utworzyć tabelę za pomocą instrukcji SELECT INTO z wyjścia widoku lub połączenia z wielu tabel. W takim przypadku chcesz również utworzyć kolumnę tożsamości w nowej tabeli.,
- Uwaga: funkcja tożsamości SQL różni się od właściwości tożsamości, której używamy podczas tworzenia dowolnej tabeli.
musimy określić typ danych dla kolumny, aby korzystać z funkcji tożsamości SQL. Musimy również określić wartości SEED i Step, aby zdefiniować konfigurację tożsamości.
nie możemy użyć funkcji SQL IDENTITY w instrukcji Select. Otrzymujemy następujący komunikat o błędzie.
Msg 177, Poziom 15, Stan 1, linia 2funkcja tożsamości może być używana tylko wtedy, gdy instrukcja SELECT ma klauzulę INTO.,
utwórzmy tabelę używając instrukcji SQL SELECT INTO z następującym zapytaniem.
Po wykonaniu polecenia sprawdź właściwości tabeli za pomocą polecenia sp_help.
|
1
|
sp_help 'temptable'
|
możesz zobaczyć kolumnę tożsamości we właściwościach temptable zgodnie z określonymi warunkami.,
spójrzmy na inny przykład. Wykonaj następujące polecenie.,
|
1
2
3
4
5
|
wybierz identyfikator( typu int, 100, 2) jako zmiennej new_id,
id
nazwa
w temp2
od employeedata książki;
|
już mamy kolumnę identyfikatorów w tabeli employeedata książki. Nowa tabela temp2 dziedziczy również kolumnę IDENTITY., Nie możemy mieć wielu kolumn tożsamości w tabeli. Z tego powodu otrzymujemy następujący komunikat o błędzie.
Msg 8108, Level 16, State 1, Line 1cannot Dodaj kolumnę tożsamości, używając instrukcji SELECT INTO, do tabeli 'temp2′, która ma już kolumnę 'ID', która dziedziczy właściwość tożsamości.
podsumowanie
- funkcje tożsamości SQL SCOPE_IDENTITY, IDENT_CURRENT i @@IDENTITY zwracają podobne wyniki dla kolumn tożsamości w bieżącej sesji
- SCOPE_IDENTITY zwraca wartości tylko w bieżącym zakresie., Możemy zdefiniować zakres jako moduł, procedurę składowaną, wyzwalacz lub funkcję
- funkcja IDENT_CURRENT Zwraca wartość tożsamości dla określonej tabeli dotyczącej połączenia, które zmodyfikowało wartość
- możemy użyć funkcji SQL IDENTITY do zdefiniowania tożsamości w tabeli utworzonej przy użyciu polecenia SQL SELECT do
wnioski
w tym artykule zbadaliśmy funkcje tożsamości w SQL Server. Powinniśmy być zaznajomieni z funkcjami używanymi do pisania zapytań t-SQL. Jeśli masz uwagi lub pytania, zostaw je w komentarzach poniżej.,
- Autor
- Ostatnie posty
jest twórcą jednego z największych darmowych internetowych zbiorów artykułów na jeden temat, z jego 50-częściową serią na SQL Server Always On Availability Groups. W oparciu o swój wkład w społeczność SQL Server został wyróżniony różnymi nagrodami, w tym prestiżowym „najlepszym autorem roku” nieprzerwanie w 2020 i 2021 w sqlshack.,
Raj jest zawsze zainteresowany nowymi wyzwaniami, więc jeśli potrzebujesz pomocy Konsultacyjnej na dowolny temat poruszany w jego pismach, można do niego dotrzeć w rajendra.gupta16@gmail.,com
Zobacz wszystkie posty Rajendra Gupta
- Wykonywanie mniejszych i większych aktualizacji wersji dla AWS RDS SQL Server – 29 stycznia 2021 li
- wdrażanie instancji AWS RDS PostgreSQL – 27 stycznia 2021
- migracja lokalnych baz danych SQL do AWS RDS SQL Server za pomocą AWS DMS-25 stycznia 2021