Najlepszy sposób na wstawienie ostatniej tożsamości do tabeli

Która z nich jest najlepszą opcją uzyskania wartości tożsamości, którą właśnie wygenerowałem przez wstawkę? Jaki jest wpływ tych stwierdzeń na wydajność?

1. SCOPE_IDENTITY()
2. Funkcja agregująca MAX()
3. WYBIERZ TOP 1IdentityColumn FROM TableNameORDER BY IdentityColumn DESC

Użyj,SCOPE_IDENTITY() jeśli wstawiasz pojedynczy wiersz i chcesz odzyskać wygenerowany identyfikator.

CREATE TABLE #a(identity_column INT IDENTITY(1,1), x CHAR(1));

INSERT #a(x) VALUES('a');

SELECT SCOPE_IDENTITY();

Wynik:

----
1

Użyj OUTPUTklauzuli, jeśli wstawiasz wiele wierszy i potrzebujesz pobrać zestaw wygenerowanych identyfikatorów.

INSERT #a(x) 
  OUTPUT inserted.identity_column 
  VALUES('b'),('c');

Wynik:

----
2
3

i dlaczego jest to najlepsza szybsza opcja?

Pomijając wydajność, są to jedyne, które z pewnością są poprawne na domyślnym poziomie izolacji i / lub z wieloma użytkownikami. Nawet jeśli zignorujesz aspekt poprawności, SQL Server przechowuje wstawioną wartość w SCOPE_IDENTITY()pamięci, więc naturalnie będzie to szybsze niż uruchomienie i uruchomienie własnego izolowanego zapytania względem tabeli lub tabel systemowych.

Ignorowanie aspektu poprawności jest jak mówienie listonoszowi, że wykonał dobrą robotę dostarczając dzisiejszą pocztę - zakończył swoją trasę o 10 minut szybciej niż przeciętny czas, problem polega na tym, że żadna poczta nie została dostarczona do właściwego domu.

Nie używaj żadnego z poniższych:

• @@IDENTITY - ponieważ nie można tego użyć we wszystkich scenariuszach, na przykład gdy tabela z kolumną tożsamości ma wyzwalacz, który wstawia również do innej tabeli z własną kolumną tożsamości - odzyskasz niewłaściwą wartość.
• IDENT_CURRENT()- Wchodzę tutaj szczegółowo w to , a komentarze są również przydatne do czytania, ale zasadniczo, w przypadku współbieżności, często otrzymasz błędną odpowiedź.
• MAX()lub TOP 1- musisz zabezpieczyć te dwa oświadczenia za pomocą szeregowalnej izolacji, aby mieć pewność, że MAX()nie dostaniesz się od kogoś innego. Jest to o wiele droższe niż zwykłe używanie SCOPE_IDENTITY().

Te funkcje również zawodzą, gdy wstawisz dwa lub więcej wierszy i potrzebujesz wygenerować wszystkie wartości tożsamości - twoją jedyną opcją jest OUTPUTklauzula.

Oprócz wydajności wszystkie mają raczej różne znaczenia.

SCOPE_IDENTITY()poda ostatnią wartość tożsamości wstawioną do dowolnej tabeli bezpośrednio w bieżącym zakresie (zakres = partia, procedura składowana itp., ale nie w ramach, powiedzmy, wyzwalacza uruchomionego przez bieżący zakres).

IDENT_CURRENT()poda ostatnią wartość tożsamości wstawioną do określonej tabeli z dowolnego zakresu przez dowolnego użytkownika.

@@IDENTITYdaje ostatnią wartość tożsamości wygenerowaną przez najnowszą instrukcję INSERT dla bieżącego połączenia, niezależnie od tabeli lub zakresu. (Uwaga dodatkowa: Access korzysta z tej funkcji, a zatem ma pewne problemy z wyzwalaczami, które wstawiają wartości do tabel z kolumnami tożsamości).

Użycie MAX()lub TOP 1może dać całkowicie błędne wyniki, jeśli tabela ma negatywny krok identyczności lub w wierszu wstawiono wiersze SET IDENTITY_INSERT. Oto skrypt demonstrujący wszystkie te:

CREATE TABLE ReverseIdent (
    id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
    data char(4)
)

INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')

SELECT * FROM ReverseIdent

SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000

SET IDENTITY_INSERT ReverseIdent ON

INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')

SET IDENTITY_INSERT ReverseIdent OFF

SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005

Podsumowanie: kij z SCOPE_IDENTITY(), IDENT_CURRENT()lub @@IDENTITY, i upewnij się, że używasz ten, który powraca co rzeczywiście potrzebne.