Automatikus értékek létrehozása
Előfordulhat, hogy automatikusan létre kell hoznia szekvenciális értékeket egy adott tábla egyik oszlopához. A Transact-SQL kétféleképpen használható: használja az IDENTITY tulajdonságot egy tábla egy adott oszlopával, vagy definiáljon egy SEQUENCE objektumot, és használja az objektum által létrehozott értékeket.
Az IDENTITY tulajdonság
Az IDENTITY tulajdonság használatához adjon meg egy oszlopot numerikus adattípussal 0 skálával (ami csak egész számokat jelent), és tartalmazza az IDENTITY kulcsszót. Az engedélyezett típusok közé tartozik az összes egész számtípus és tizedesjegy, ahol explicit módon 0 skálát ad meg.
Opcionális mag (kezdőérték) és növekmény (lépésérték) is megadható. Ha kihagyja a magot és a növekményt, mindkettőt 1-re állítja.
Feljegyzés
Az IDENTITY tulajdonság a NULL vagy a NOT NULL érték megadása helyett van megadva az oszlopdefinícióban. Az IDENTITY tulajdonsággal rendelkező oszlopok automatikusan nem null értékűek. A NOT NULL értéket csak az öndokumentációhoz adhatja meg, de ha NULL (azaz null értékű) értékként adja meg az oszlopot, a táblalétrehozás utasítása hibát fog eredményezni.
Előfordulhat, hogy a tábla csak egy oszlopában van megadva az IDENTITY tulajdonság; gyakran használják ELSŐDLEGES KULCSként vagy másodlagos kulcsként.
Az alábbi kód az előző szakasz példáiban használt Sales.Promotion tábla létrehozását mutatja be, de ezúttal egy PromotionID nevű identitásoszlopot használ elsődleges kulcsként:
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
);
Feljegyzés
A CREATE TABLE utasítás teljes részletei túlmutatnak a modul hatókörén.
Adatok beszúrása identitásoszlopba
Ha az IDENTITY tulajdonság egy oszlophoz van definiálva, az INSERT utasítás a táblában általában nem ad meg értéket az IDENTITY oszlophoz. Az adatbázismotor létrehoz egy értéket az oszlop következő elérhető értékével.
Beszúrhat például egy sort a Sales.Promotion táblába a PromotionID oszlop értékének megadása nélkül:
INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')
Figyelje meg, hogy annak ellenére, hogy a VALUES záradék nem tartalmaz értéket a PromotionID oszlophoz, nem kell oszloplistát megadnia az INSERT záradékban – Az identitásoszlopok mentesülnek a követelmény alól.
Ha ez a sor az első beszúrt sor a táblába, az eredmény egy új sor, mint a következő:
PromotionID
PromotionLabel
Kezdési dátum
ProductModelID
Árengedmény
Jegyzetek
0
Vámkezelés értékesítés
2021-01-01T00:00:00
23
0,1
10% kedvezmény
A tábla létrehozásakor a rendszer nem állított be mag- vagy növekményértéket az IDENTITY oszlophoz, így az első sor 1 értékkel lesz beszúrva. A következő beszúrandó sorhoz egy 2-ás PromotionID-érték lesz hozzárendelve, és így tovább.
Identitásérték beolvasása
Ha a legutóbb hozzárendelt IDENTITÁS értéket szeretné visszaadni ugyanazon a munkameneten és hatókörön belül, használja a SCOPE_IDENTITY függvényt; tetszik:
SELECT SCOPE_IDENTITY();
A SCOPE_IDENTITY függvény bármely tábla aktuális hatókörében létrehozott legújabb identitásértéket adja vissza. Ha egy adott táblában a legújabb identitásértékre van szüksége, használhatja a IDENT_CURRENT függvényt, például a következőt:
SELECT IDENT_CURRENT('Sales.Promotion');
Identitásértékek felülírása
Ha felül szeretné bírálni az automatikusan létrehozott értéket, és egy adott értéket szeretne hozzárendelni az IDENTITY oszlophoz, először engedélyeznie kell az identitásbeszúrást a SET IDENTITY INSERT table_name ON utasítással. Ha ez a beállítás engedélyezve van, beszúrhat egy explicit értéket az identitásoszlophoz, ugyanúgy, mint bármely más oszlophoz. Ha végzett, a SET IDENTITY INSERT table_name OFF utasítással folytathatja az automatikus identitásértékek használatát, az utolsó megadott érték magként való használatával.
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;
Mint megtanulta, az IDENTITY tulajdonság egy oszlop értéksorozatának létrehozására szolgál egy táblán belül. Az IDENTITY tulajdonság azonban nem alkalmas az adatbázisok több táblájának értékeinek összehangolására. Tegyük fel például, hogy a szervezet megkülönbözteti a közvetlen értékesítéseket és a viszonteladók felé irányuló értékesítéseket, és külön táblákban szeretné tárolni az ilyen értékesítések adatait. Mindkét típusú értékesítéshez egyedi számlaszámra lehet szükség, és érdemes lehet elkerülni, hogy ugyanazt az értéket két különböző típusú értékesítésnél duplikálja. Ennek a követelménynek az egyik megoldása, ha mindkét táblában egyedi szekvenciális értékekből álló készletet tart fenn.
Identitásoszlop újraküldése
Időnként alaphelyzetbe kell állítania vagy át kell hagynia az oszlop identitásértékét. Ehhez az oszlopot a DBCC CHECKIDENT függvénnyel kell "újraküldnie". Ezzel számos értéket kihagyhat, vagy visszaállíthatja a következő identitásértéket 1 értékre a tábla összes sorának törlése után. A DBCC CHECKIDENT használatával kapcsolatos részletes információkért tekintse meg a Transact-SQL referenciadokumentációját.
KÖVETKEZÉS
A Transact-SQL-ben egy szekvenciális objektummal definiálhat új szekvenciális értékeket egy adott táblától függetlenül. A rendszer a CREATE SEQUENCE utasítással hoz létre egy sorozatobjektumot, amely opcionálisan megadja az adattípust (0-s skálával rendelkező egész számnak vagy tizedesnek vagy számnak kell lennie), a kiindulási értéknek, a növekményes értéknek, a maximális értéknek és a teljesítménnyel kapcsolatos egyéb lehetőségeknek.
CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;
A következő elérhető érték egy sorozatból való lekéréséhez használja a NEXT VALUE FOR szerkezetet, például a következőt:
INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);
IDENTITÁS VAGY SZEKVENCIA
Amikor eldönti, hogy identitásoszlopokat vagy SEQUENCE objektumot használ-e az automatikus felugró értékekhez, tartsa szem előtt az alábbi szempontokat:
A SEQUENCE függvényt akkor használja, ha az alkalmazásnak egyetlen számsort kell megosztania több tábla vagy több oszlop között egy táblán belül.
A SEQUENCE lehetővé teszi, hogy az értékeket egy másik oszlop szerint rendezze. A KÖVETKEZŐ ÉRTÉK a szerkezethez az OVER záradék használatával adhatja meg a rendezési oszlopot. A OVER záradék garantálja, hogy a visszaadott értékek az OVER záradék ORDER BY záradékának sorrendjében jönnek létre. Ezzel a funkcióval sorszámokat is létrehozhat a sorokhoz, amikor azok a SELECT-ben lesznek visszaadva. Az alábbi példában a Production.Product tábla a Név oszlop szerint van rendezve, az első visszaadott oszlop pedig egy szekvenciális szám.
SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID, ProductID, Name FROM Production.Product;Annak ellenére, hogy az előző utasítás csak a SZEKVENCIA értékeket jelölte ki, az értékek továbbra is "fel vannak használva", és a megjelenített SORSZÁMÉRTÉKek már nem lesznek elérhetők. Ha többször futtatja a fenti SELECT parancsot, minden alkalommal különböző SORSZÁMÉRTÉKeket fog kapni.
Akkor használja a SEQUENCE függvényt, ha az alkalmazáshoz egyszerre több számot kell hozzárendelni. Egy alkalmazásnak például öt egymást követő számot kell lefoglalnia. Ha identitásértékeket kér le, akkor az adatsorban rések keletkezhetnek, ha más folyamatok egyidejűleg számokat adnak ki. A sp_sequence_get_range rendszereljárás használatával egyszerre több számot is lekérhet a sorrendben.
A SEQUENCE lehetővé teszi a szekvencia specifikációjának módosítását, például a növekmény értékét.
Az IDENTITÁSértékek védettek a frissítésektől. Ha megpróbál frissíteni egy oszlopot az IDENTITY tulajdonsággal, hibaüzenet jelenik meg.