Generowanie wartości automatycznych

  • 3 min

Może być konieczne automatyczne generowanie wartości sekwencyjnych dla jednej kolumny w określonej tabeli. Język Transact-SQL zapewnia dwa sposoby, aby to zrobić: użyj właściwości IDENTITY z określoną kolumną w tabeli lub zdefiniuj obiekt SEQUENCE i użyj wartości wygenerowanych przez ten obiekt.

Właściwość IDENTITY

Aby użyć właściwości IDENTITY, zdefiniuj kolumnę przy użyciu typu danych liczbowych o skali 0 (czyli tylko liczby całkowite) i dołącz słowo kluczowe IDENTITY. Dozwolone typy obejmują wszystkie typy całkowite i typy dziesiętne, w których jawnie nadajesz skalę 0.

Można również określić opcjonalną inicjację (wartość początkową) i przyrost (wartość kroku). Pozostawienie nasion i przyrost ustawi je zarówno na 1.

Uwaga

Właściwość IDENTITY jest określana zamiast określania wartości NULL lub NOT NULL w definicji kolumny. Każda kolumna z właściwością IDENTITY nie jest automatycznie dopuszczana do wartości null. Możesz określić wartość NOT NULL tylko dla samodzielnej dokumentacji, ale jeśli określisz kolumnę o wartości NULL (co oznacza wartość null), instrukcja tworzenia tabeli wygeneruje błąd.

Tylko jedna kolumna w tabeli może mieć ustawioną właściwość IDENTITY; jest on często używany jako KLUCZ PODSTAWOWY lub klucz alternatywny.

Poniższy kod przedstawia tworzenie tabeli Sales.Promotion używanej w poprzednich przykładach sekcji, ale tym razem z kolumną tożsamości o nazwie PromotionID jako kluczem podstawowym:

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Uwaga

Pełne szczegóły instrukcji CREATE TABLE wykraczają poza zakres tego modułu.

Wstawianie danych do kolumny tożsamości

Gdy właściwość IDENTITY jest zdefiniowana dla kolumny, instrukcje INSERT w tabeli zazwyczaj nie określają wartości dla kolumny IDENTITY. Aparat bazy danych generuje wartość przy użyciu następnej dostępnej wartości dla kolumny.

Można na przykład wstawić wiersz do tabeli Sales.Promotion bez określania wartości dla kolumny PromotionID :

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Zwróć uwagę, że mimo że klauzula VALUES nie zawiera wartości dla kolumny PromotionID , nie musisz określać listy kolumn w klauzuli INSERT — kolumny Identity są wykluczone z tego wymagania.

Jeśli ten wiersz jest pierwszym wstawionym do tabeli, wynik jest nowym wierszem w następujący sposób:

Identyfikator promocji

PromotionName

StartDate

ProductModelID

Discount

Notatki

1

Wyprzedaż

2021-01-01T00:00:00

23

0.1

10% rabatu

Po utworzeniu tabeli nie ustawiono wartości inicjacji ani przyrostu dla kolumny IDENTITY, więc pierwszy wiersz jest wstawiany z wartością 1. Następny wiersz, który ma zostać wstawiony, zostanie przypisany wartość PromotionID 2 itd.

Pobieranie wartości tożsamości

Aby zwrócić ostatnio przypisaną wartość IDENTITY w ramach tej samej sesji i zakresu, użyj funkcji SCOPE_IDENTITY; Jak to:

SELECT SCOPE_IDENTITY();

Funkcja SCOPE_IDENTITY zwraca najnowszą wartość tożsamości wygenerowaną w bieżącym zakresie dla dowolnej tabeli. Jeśli potrzebujesz najnowszej wartości tożsamości w określonej tabeli, możesz użyć funkcji IDENT_CURRENT w następujący sposób:

SELECT IDENT_CURRENT('Sales.Promotion');

Zastępowanie wartości tożsamości

Jeśli chcesz zastąpić automatycznie wygenerowaną wartość i przypisać określoną wartość do kolumny IDENTITY, najpierw należy włączyć wstawianie tożsamości przy użyciu instrukcji SET IDENTITY INSERT table_name ON. Po włączeniu tej opcji można wstawić jawną wartość dla kolumny tożsamości, podobnie jak każda inna kolumna. Po zakończeniu możesz użyć instrukcji SET IDENTITY INSERT table_name OFF, aby wznowić korzystanie z automatycznych wartości tożsamości, używając ostatniej wartości wprowadzonej jawnie jako inicjator.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

Jak już wiesz, właściwość IDENTITY służy do generowania sekwencji wartości dla kolumny w tabeli. Jednak właściwość IDENTITY nie jest odpowiednia do koordynowania wartości w wielu tabelach w bazie danych. Załóżmy na przykład, że Twoja organizacja rozróżnia sprzedaż bezpośrednią i sprzedaż od odsprzedawców i chce przechowywać dane dla tej sprzedaży w osobnych tabelach. Oba rodzaje sprzedaży mogą wymagać unikatowego numeru faktury i możesz uniknąć duplikowania tej samej wartości dla dwóch różnych rodzajów sprzedaży. Jednym z rozwiązań tego wymagania jest utrzymywanie puli unikatowych wartości sekwencyjnych w obu tabelach.

Zmiana nazwy kolumny tożsamości

Czasami musisz zresetować lub pominąć wartości tożsamości dla kolumny. W tym celu kolumna będzie "zmieniana" przy użyciu funkcji DBCC CHECKIDENT. Za pomocą tej opcji można pominąć wiele wartości lub zresetować następną wartość tożsamości do wartości 1 po usunięciu wszystkich wierszy w tabeli. Aby uzyskać szczegółowe informacje dotyczące korzystania z narzędzia DBCC CHECKIDENT, zobacz dokumentację referencyjną języka Transact-SQL.

KOLEJNOŚĆ

W języku Transact-SQL można użyć obiektu sekwencji do zdefiniowania nowych wartości sekwencyjnych niezależnie od określonej tabeli. Obiekt sekwencji jest tworzony przy użyciu instrukcji CREATE SEQUENCE, opcjonalnie podając typ danych (musi być typem całkowitym lub dziesiętnym lub liczbowym ze skalą 0), wartością początkową, wartością przyrostową, maksymalną wartością i innymi opcjami związanymi z wydajnością.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

Aby pobrać następną dostępną wartość z sekwencji, użyj konstrukcji NEXT VALUE FOR, w następujący sposób:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

TOŻSAMOŚĆ lub SEKWENCJA

Podczas podejmowania decyzji, czy używać kolumn IDENTITY, czy obiektu SEQUENCE do automatycznego wypełniania wartości, należy pamiętać o następujących kwestiach:

  • Użyj funkcji SEQUENCE, jeśli aplikacja wymaga udostępniania jednej serii liczb między wieloma tabelami lub wieloma kolumnami w tabeli.

  • Sekwencja umożliwia sortowanie wartości według innej kolumny. Konstrukcja NEXT VALUE FOR może użyć klauzuli OVER, aby określić kolumnę sortowania. Klauzula OVER gwarantuje, że zwracane wartości są generowane w kolejności klauzuli ORDER BY klauzuli OVER. Ta funkcja umożliwia również generowanie numerów wierszy dla wierszy w miarę ich zwracania w elemecie SELECT. W poniższym przykładzie tabela Production.Product jest sortowana według kolumny Name , a pierwsza zwrócona kolumna jest liczbą sekwencyjną.

    SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;

    Mimo że poprzednia instrukcja po prostu wybierała wartości sekwencji do wyświetlenia, wartości są nadal "używane", a wyświetlane wartości sekwencji nie będą już dostępne. Jeśli uruchomisz powyższe polecenie SELECT wiele razy, za każdym razem uzyskasz różne wartości sekwencji.

  • Użyj funkcji SEQUENCE, jeśli aplikacja wymaga przypisania wielu liczb jednocześnie. Na przykład aplikacja musi zarezerwować pięć liczb sekwencyjnych. Żądanie wartości tożsamości może spowodować przerwy w serii, jeśli inne procesy zostały jednocześnie wystawione numery. Możesz użyć procedury systemu sp_sequence_get_range , aby pobrać kilka liczb w sekwencji jednocześnie.

  • Sekwencja umożliwia zmianę specyfikacji sekwencji, takiej jak wartość przyrostowa.

  • Wartości TOŻSAMOŚCI są chronione przed aktualizacjami. Jeśli spróbujesz zaktualizować kolumnę za pomocą właściwości IDENTITY, wystąpi błąd.