Az IDENTITY használata helyettesítő kulcsok létrehozásához dedikált SQL-készlettel az Azure Synapse Analyticsben
Ebben a cikkben javaslatokat és példákat talál arra, hogy az IDENTITY tulajdonság használatával helyettesítő kulcsokat hozhat létre a dedikált SQL-készlet tábláiban.
Mi az a helyettes kulcs?
A tábla helyettesítő kulcsa egy oszlop, amely minden sor egyedi azonosítójával rendelkezik. A kulcs nem a táblaadatokból jön létre. Az adatmodellezők szeretnek helyettesítő kulcsokat létrehozni a tábláikon, amikor adattárház-modelleket terveznek. Az IDENTITY tulajdonság használatával egyszerűen és hatékonyan érheti el ezt a célt a terhelési teljesítmény befolyásolása nélkül.
Megjegyzés
Az Azure Synapse Analyticsben:
- Az IDENTITÁS értéke önmagában növekszik minden eloszlásban, és nem fedi át az identitásértékeket más eloszlásokban. A Synapse IDENTITÁS értéke nem garantáltan egyedi, ha a felhasználó explicit módon beszúr egy duplikált értéket a "SET IDENTITY_INSERT ON" vagy a reseeds IDENTITY kifejezéssel. További információ: CREATE TABLE (Transact-SQL) IDENTITY (Tulajdonság).
- A terjesztési oszlop FRISSÍTÉSE nem garantálja, hogy az IDENTITÁS értéke egyedi legyen. Az egyediség ellenőrzéséhez használja a DBCC CHECKIDENT (Transact-SQL) függvényt a terjesztési oszlop frissítését követően.
Tábla létrehozása IDENTITY oszloppal
Az IDENTITY tulajdonság úgy lett kialakítva, hogy a terhelési teljesítmény befolyásolása nélkül felskálázza a dedikált SQL-készlet összes disztribúcióit. Ezért az IDENTITÁS megvalósítása ezen célok elérésére irányul.
A táblát úgy definiálhatja, hogy rendelkezik az IDENTITY tulajdonságtal, amikor először hozza létre a táblát az alábbi utasításhoz hasonló szintaxissal:
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1) NOT NULL
, C2 INT NULL
)
WITH
( DISTRIBUTION = HASH(C2)
, CLUSTERED COLUMNSTORE INDEX
)
;
Ezután a INSERT..SELECT használatával feltöltheti a táblát.
Ez a szakasz a megvalósítás árnyalatait emeli ki, hogy jobban megértse őket.
Értékek kiosztása
Az IDENTITY tulajdonság nem garantálja a helyettesítő értékek lefoglalásának sorrendjét az adattárház elosztott architektúrája miatt. Az IDENTITY tulajdonság úgy lett kialakítva, hogy a terhelési teljesítmény befolyásolása nélkül felskálázza a dedikált SQL-készlet összes disztribúcióit.
Az alábbi példa egy illusztráció:
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1) NOT NULL
, C2 VARCHAR(30) NULL
)
WITH
( DISTRIBUTION = HASH(C2)
, CLUSTERED COLUMNSTORE INDEX
)
;
INSERT INTO dbo.T1
VALUES (NULL);
INSERT INTO dbo.T1
VALUES (NULL);
SELECT *
FROM dbo.T1;
DBCC PDW_SHOWSPACEUSED('dbo.T1');
Az előző példában két sor landolt az 1. eloszlásban. Az első sor oszlopában C1az 1 helyettesítő érték, a második sor pedig 61 helyettesítő értékkel rendelkezik. Mindkét értéket az IDENTITY tulajdonság hozta létre. Az értékek kiosztása azonban nem összefüggő. Ez a működésmód szándékos.
Eltolt adatok
Az adattípushoz tartozó értéktartomány egyenletesen oszlik el az elosztások között. Ha egy elosztott tábla torzított adatokat tartalmaz, akkor az adattípushoz elérhető értékek tartománya idő előtt elfogyhat. Ha például az összes adat egyetlen eloszlásba kerül, akkor a tábla gyakorlatilag csak az adattípus értékeinek egy-hatszázadához fér hozzá. Ezért az IDENTITY tulajdonság csak és csak adattípusokra INTBIGINT korlátozódik.
VÁLASSZA KI.. INTO
Ha egy meglévő IDENTITÁS oszlop van kijelölve egy új táblában, az új oszlop örökli az IDENTITY tulajdonságot, hacsak az alábbi feltételek egyike nem teljesül:
- A SELECT utasítás egy összekapcsolást tartalmaz.
- Több SELECT utasítás van összekapcsolva a UNION segítségével.
- Az IDENTITY oszlop többször is szerepel a SELECT listán.
- Az IDENTITY oszlop egy kifejezés része.
Ha a feltételek bármelyike igaz, az oszlop nem az IDENTITY tulajdonság öröklése, hanem a NOT NULL értéket hozza létre.
CREATE TABLE AS SELECT
A CREATE TABLE AS SELECT (CTAS) ugyanazt a SQL Server viselkedést követi, mint a SELECT.. BE. A utasítás részének oszlopdefiníciójában CREATE TABLE azonban nem adható meg IDENTITY tulajdonság. Az IDENTITY függvényt SELECT a CTAS részeként sem használhatja. Egy tábla feltöltéséhez meg kell CREATE TABLE határoznia a táblát, majd INSERT..SELECT fel kell töltenie.
Értékek explicit beszúrása identitásoszlopba
A dedikált SQL-készlet támogatja a SET IDENTITY_INSERT <your table> ON|OFF szintaxist. Ezzel a szintaxissal célzottan szúrhat be értékeket az IDENTITY oszlopba.
Sok adatmodellező elődefiniált negatív értékeket szeretne használni bizonyos sorokhoz a dimenziójukban. Ilyen például a -1 vagy az "ismeretlen tag" sor.
A következő szkript bemutatja, hogyan lehet explicit módon hozzáadni ezt a sort a SET IDENTITY_INSERT használatával:
SET IDENTITY_INSERT dbo.T1 ON;
INSERT INTO dbo.T1
( C1
, C2
)
VALUES (-1,'UNKNOWN')
;
SET IDENTITY_INSERT dbo.T1 OFF;
SELECT *
FROM dbo.T1
;
Adatok betöltése
Az IDENTITY tulajdonság jelenléte hatással van az adatbetöltési kódra. Ez a szakasz néhány alapvető mintát mutat be az adatok az IDENTITY használatával történő táblákba való betöltéséhez.
Ha adatokat szeretne betölteni egy táblába, és létrehoz egy helyettesítő kulcsot az IDENTITY használatával, hozza létre a táblát, majd használja az INSERT parancsot. VÁLASSZA VAGY SZÚRJA BE. ÉRTÉKEK a terhelés végrehajtásához.
Az alábbi példa az alapszintű mintát emeli ki:
--CREATE TABLE with IDENTITY
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1)
, C2 VARCHAR(30)
)
WITH
( DISTRIBUTION = HASH(C2)
, CLUSTERED COLUMNSTORE INDEX
)
;
--Use INSERT..SELECT to populate the table from an external table
INSERT INTO dbo.T1
(C2)
SELECT C2
FROM ext.T1
;
SELECT *
FROM dbo.T1
;
DBCC PDW_SHOWSPACEUSED('dbo.T1');
Megjegyzés
Jelenleg nem használható CREATE TABLE AS SELECT , ha adatokat tölt be egy IDENTITY oszlopot tartalmazó táblába.
További információ az adatok betöltéséről: A dedikált SQL-készlet kinyerése, betöltése és átalakítása (ELT) tervezése és az ajánlott eljárások betöltése.
Rendszernézetek
A sys.identity_columns katalógusnézet használatával azonosíthatja az IDENTITY tulajdonsággal rendelkező oszlopokat.
Az adatbázisséma jobb megértéséhez ez a példa bemutatja, hogyan integrálható sys.identity_column más rendszerkatalógus-nézetekkel:
SELECT sm.name
, tb.name
, co.name
, CASE WHEN ic.column_id IS NOT NULL
THEN 1
ELSE 0
END AS is_identity
FROM sys.schemas AS sm
JOIN sys.tables AS tb ON sm.schema_id = tb.schema_id
JOIN sys.columns AS co ON tb.object_id = co.object_id
LEFT JOIN sys.identity_columns AS ic ON co.object_id = ic.object_id
AND co.column_id = ic.column_id
WHERE sm.name = 'dbo'
AND tb.name = 'T1'
;
Korlátozások
Az IDENTITY tulajdonság nem használható:
- Ha az oszlop adattípusa nem INT vagy BIGINT
- Ha az oszlop egyben a terjesztési kulcs is
- Ha a tábla külső tábla
A következő kapcsolódó függvények nem támogatottak a dedikált SQL-készletben:
Gyakori feladatok
Ez a szakasz néhány mintakódot tartalmaz, amellyel gyakori feladatokat hajthat végre identitásoszlopok használatakor.
A C1 oszlop az összes alábbi feladat identitása.
Tábla legmagasabb lefoglalt értékének megkeresése
Használja a MAX() függvényt az elosztott táblákhoz lefoglalt legmagasabb érték meghatározásához:
SELECT MAX(C1)
FROM dbo.T1
Az IDENTITY tulajdonság magjának és növekményének megkeresése
A katalógusnézetek segítségével az alábbi lekérdezéssel felderítheti egy tábla identitásnövelési és magkonfigurációs értékeit:
SELECT sm.name
, tb.name
, co.name
, ic.seed_value
, ic.increment_value
FROM sys.schemas AS sm
JOIN sys.tables AS tb ON sm.schema_id = tb.schema_id
JOIN sys.columns AS co ON tb.object_id = co.object_id
JOIN sys.identity_columns AS ic ON co.object_id = ic.object_id
AND co.column_id = ic.column_id
WHERE sm.name = 'dbo'
AND tb.name = 'T1'
;