Generování automatických hodnot

  • 3 min

Možná budete muset automaticky generovat sekvenční hodnoty pro jeden sloupec v konkrétní tabulce. Transact-SQL nabízí dva způsoby, jak to provést: použijte vlastnost IDENTITY s konkrétním sloupcem v tabulce nebo definujte objekt SEQUENCE a použijte hodnoty vygenerované tímto objektem.

Vlastnost IDENTITY

Pokud chcete použít vlastnost IDENTITY, definujte sloupec pomocí číselného datového typu s měřítkem 0 (tj. pouze celá čísla) a zahrňte klíčové slovo IDENTITY. Povolené typy zahrnují všechny celočíselné typy a desetinné číslo, kde explicitně dáváte měřítko 0.

Je také možné zadat volitelné počáteční hodnoty a přírůstek (hodnota kroku). Vynechejte semeno a přírůstky je nastaví na hodnotu 1.

Poznámka:

Vlastnost IDENTITY je určena místo zadání NULL nebo NOT NULL v definici sloupce. Jakýkoli sloupec s vlastností IDENTITY není automaticky nullable. Hodnotu NOT NULL můžete zadat pouze pro vlastní dokumentaci, ale pokud zadáte sloupec jako NULL (což znamená nullable), příkaz pro vytvoření tabulky vygeneruje chybu.

Vlastnost IDENTITY může mít nastavenou pouze jeden sloupec v tabulce; často se používá jako PRIMÁRNÍ KLÍČ nebo alternativní klíč.

Následující kód ukazuje vytvoření tabulky Sales.Promotion použité v předchozích příkladech oddílu, ale tentokrát se sloupcem identity s názvem PromotionID jako primárním klíčem:

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
);

Poznámka:

Úplné podrobnosti příkazu CREATE TABLE jsou nad rámec tohoto modulu.

Vložení dat do sloupce identity

Pokud je vlastnost IDENTITY definovaná pro sloupec, příkazy INSERT do tabulky obvykle nezadávají hodnotu sloupce IDENTITY. Databázový stroj vygeneruje hodnotu pomocí další dostupné hodnoty sloupce.

Můžete například vložit řádek do tabulky Sales.Promotion bez zadání hodnoty sloupce PromotionID :

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

Všimněte si, že i když klauzule VALUES neobsahuje hodnotu sloupce PromotionID , nemusíte v klauzuli INSERT zadávat seznam sloupců – Sloupce identity jsou z tohoto požadavku vyloučené.

Pokud je tento řádek první vložený do tabulky, výsledek je nový řádek podobný tomuto:

PromotionID

PromotionName

StartDate

ProductModelID

Discount

Notes

1

Výprodej

2021-01-01T00:00:00

23

0,1

10% sleva

Při vytvoření tabulky nebyly pro sloupec IDENTITY nastaveny žádné počáteční ani přírůstkové hodnoty, takže první řádek se vloží s hodnotou 1. Dalšímu řádku, který se má vložit, se přiřadí hodnota PromotionID 2 atd.

Načtení hodnoty identity

Pokud chcete vrátit naposledy přiřazenou hodnotu IDENTITY ve stejné relaci a oboru, použijte funkci SCOPE_IDENTITY; Nějak tak:

SELECT SCOPE_IDENTITY();

Funkce SCOPE_IDENTITY vrátí nejnovější hodnotu identity vygenerovanou v aktuálním oboru pro libovolnou tabulku. Pokud potřebujete nejnovější hodnotu identity v konkrétní tabulce, můžete použít funkci IDENT_CURRENT, například takto:

SELECT IDENT_CURRENT('Sales.Promotion');

Přepsání hodnot identity

Pokud chcete přepsat automaticky vygenerovanou hodnotu a přiřadit konkrétní hodnotu ke sloupci IDENTITY, musíte nejprve povolit vkládání identit pomocí příkazu SET IDENTITY INSERT table_name ON. Pokud je tato možnost povolená, můžete vložit explicitní hodnotu sloupce identity stejně jako jakýkoli jiný sloupec. Až budete hotovi, můžete pomocí příkazu SET IDENTITY INSERT table_name OFF pokračovat pomocí automatických hodnot identit s použitím poslední hodnoty, kterou jste explicitně zadali jako počáteční hodnotu.

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 jste se dozvěděli, vlastnost IDENTITY slouží k vygenerování posloupnosti hodnot pro sloupec v tabulce. Vlastnost IDENTITY ale není vhodná pro koordinaci hodnot napříč více tabulkami v databázi. Předpokládejme například, že vaše organizace rozlišuje mezi přímým prodejem a prodejem prodejců a chce ukládat data pro tyto prodeje v samostatných tabulkách. Oba druhy prodeje můžou potřebovat jedinečné číslo faktury a můžete se vyhnout duplikování stejné hodnoty pro dva různé druhy prodeje. Jedním z řešení tohoto požadavku je udržovat fond jedinečných sekvenčních hodnot v obou tabulkách.

Opětovné vytvoření sloupce identity

V některých případech budete muset resetovat nebo přeskočit hodnoty identity pro sloupec. Uděláte to tak, že sloupec "přeinstalujete" pomocí funkce DBCC CHECKIDENT. Můžete ho použít ke přeskočení mnoha hodnot nebo k resetování další hodnoty identity na 1 po odstranění všech řádků v tabulce. Úplné podrobnosti o použití DBCC CHECKIDENT najdete v referenční dokumentaci jazyka Transact-SQL.

POSLOUPNOST

V jazyce Transact-SQL můžete pomocí sekvenčního objektu definovat nové sekvenční hodnoty nezávisle na konkrétní tabulce. Sekvenční objekt se vytvoří pomocí příkazu CREATE SEQUENCE, volitelně zadáte datový typ (musí to být celočíselný typ nebo desítkový nebo číselný s měřítkem 0), počáteční hodnota, hodnota přírůstku, maximální hodnota a další možnosti související s výkonem.

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

Pokud chcete načíst další dostupnou hodnotu z posloupnosti, použijte konstruktor NEXT VALUE FOR, například takto:

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

IDENTITA nebo SEKVENCE

Při rozhodování o použití sloupců IDENTITY nebo objektu SEQUENCE pro automatické naplnění hodnot mějte na paměti následující body:

  • Funkce SEQUENCE použijte, pokud vaše aplikace vyžaduje sdílení jedné řady čísel mezi více tabulkami nebo více sloupci v tabulce.

  • FUNKCE SEQUENCE umožňuje řadit hodnoty podle jiného sloupce. Konstruktor NEXT VALUE FOR může použít klauzuli OVER k určení sloupce řazení. Klauzule OVER zaručuje, že vrácené hodnoty se generují v pořadí klauzule OVER klauzule ORDER BY. Tato funkce také umožňuje generovat čísla řádků pro řádky při jejich vrácení v select. V následujícím příkladu je tabulka Production.Product seřazena podle sloupce Name a první vrácený sloupec je pořadové číslo.

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

    I když předchozí příkaz právě vybral hodnoty SEQUENCE, které se mají zobrazit, hodnoty se stále používají a zobrazené hodnoty SEQUENCE už nebudou k dispozici. Pokud výše uvedený příkaz SELECT spustíte několikrát, získáte pokaždé různé hodnoty SEQUENCE.

  • Funkce SEQUENCE použijte, pokud vaše aplikace vyžaduje přiřazení více čísel ve stejnou dobu. Například aplikace musí rezervovat pět sekvenčních čísel. Žádosti o hodnoty identity můžou vést k mezerám v řadě, pokud byly čísla vystavena současně jiné procesy. Systémovou proceduru sp_sequence_get_range můžete použít k načtení několika čísel v sekvenci najednou.

  • FUNKCE SEQUENCE umožňuje změnit specifikaci sekvence, například hodnotu přírůstku.

  • Hodnoty IDENTITY jsou chráněné před aktualizacemi. Pokud se pokusíte aktualizovat sloupec vlastností IDENTITY, zobrazí se chyba.