Helyettesítő kulcsok létrehozása dedikált SQL-készletben az IDENTITY használatával

Ebben a cikkben javaslatokat és példákat talál arra, hogy a 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ítő 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 az adattárházmodellek tervezésekor szívesen hoznak létre helyettesítő kulcsokat a tábláikon. A tulajdonság használatával egyszerűen és hatékonyan érheti el ezt a IDENTITY célt a terhelési teljesítmény befolyásolása nélkül.

Note

Az Azure Synapse Analytics szolgáltatásban:

  • Az IDENTITY érték minden elosztásban magától növekszik, és nincs átfedésben más elosztások IDENTITY értékeivel. 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 használatával vagy újra inicializálja az IDENTITY-t. További részletekért lásd: CREATE TABLE (Transact-SQL) IDENTITY (Property).
  • Az elosztási oszlop frissítése nem garantálja, hogy az azonosító értéke egyedi. 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 IDENTITÁS oszlopmal

A IDENTITY tulajdonság úgy lett kialakítva, hogy a dedikált SQL-készlet összes disztribúciója felskálázható legyen a terhelési teljesítmény befolyásolása nélkül. Ezért a végrehajtás IDENTITY ezen célok elérése felé irányul.

A táblázat definiálásakor megadhatja, hogy rendelkezzen a IDENTITY tulajdonsággal, a következőhöz hasonló szintaxist használva teheti ezt meg az első létrehozáskor:

CREATE TABLE dbo.T1
(    C1 INT IDENTITY(1,1) NOT NULL,
     C2 INT NULL
)
WITH
(   DISTRIBUTION = HASH(C2),
    CLUSTERED COLUMNSTORE INDEX
);

Ezután ön INSERT..SELECT segítségével feltöltheti a táblát.

A szakasz további része a megvalósítás árnyalatait emeli ki, hogy jobban megértse őket.

Értékek kiosztása

A 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. A IDENTITY tulajdonság úgy lett kialakítva, hogy a dedikált SQL-készlet összes disztribúciója felskálázható legyen a terhelési teljesítmény befolyásolása nélkül.

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ő sorban a C1 oszlopban a helyettesítő érték 1, míg a második sorban a helyettesítő érték 61. Mindkét értéket a IDENTITY tulajdonság hozta létre. Az értékek kiosztása azonban nem folytonos. Ez szándékosan van.

Ferde 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ásban végződik, akkor a tábla gyakorlatilag csak az adattípus értékeinek egy-hattiethéhez fér hozzá. Ezért a IDENTITY tulajdonság csak és csak adattípusokra korlátozódik INTBIGINT .

SELECT..INTO

Ha egy meglévő IDENTITY oszlop van kijelölve egy új táblában, az új oszlop örökli a IDENTITY tulajdonságot, kivéve, ha az alábbi feltételek egyike teljesül:

  • Az SELECT utasítás egy illesztést tartalmaz.
  • Több SELECT utasítást összekapcsolnak a UNION használatával.
  • Az IDENTITY oszlop többször szerepel a SELECT listában.
  • Az IDENTITY oszlop egy kifejezés része.

Ha bármelyik feltétel igaz, az oszlop létrejön NOT NULL, ahelyett, hogy örökölné a IDENTITY tulajdonságot.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) ugyanolyan SQL Server-es viselkedést követ, mint amit a dokumentáció SELECT..INTO-re ír. Azonban az utasítás IDENTITY részének oszlopdefiníciójában nem adhat meg CREATE TABLE tulajdonságot. Ön sem használhatja a IDENTITY függvényt a SELECT részében a CTAS-nak. Egy tábla feltöltéséhez a táblát CREATE TABLE meg kell határozni, majd INSERT..SELECT fel kell tölteni.

Explicit értékek beszúrása IDENTITÁS oszlopba

A dedikált SQL-készlet támogatja a SET IDENTITY_INSERT <your table> ON|OFF szintaxist. Ezzel a szintaxisval explicit módon szúrhat be értékeket az IDENTITY oszlopba.

Sok adatmodellező szeretne előre definiált negatív értékeket használni a dimenziók bizonyos soraiban. Ilyen például a -1 vagy ismeretlen tagsor .

A következő szkript bemutatja, hogyan lehet explicit módon hozzáadni ezt a sort a következő használatával SET IDENTITY_INSERT:

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 jelentősége van az adatbetöltési kódra. Ez a szakasz néhány alapvető mintát mutat be az adatok táblákba való betöltésére a IDENTITY használatával.

Ha adatokat szeretne betölteni egy táblába, és helyettesítő kulcsot szeretne létrehozni a használatával IDENTITY, hozza létre a táblát, majd használja INSERT..SELECT vagy INSERT..VALUES végezze el a betöltést.

Az alábbi példa az alapmintá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');

Note

Jelenleg nem használható CREATE TABLE AS SELECT , ha adatokat tölt be egy oszlopot tartalmazó IDENTITY táblába.

Az adatok betöltésével kapcsolatos további információkért tekintse meg a dedikált SQL-készlet kinyerését, betöltését és átalakítását (ELT) és a betöltési ajánlott eljárásokat ismertető témakört.

Rendszernézetek

A sys.identity_columns katalógusnézet használatával azonosíthatja a tulajdonsággal rendelkező IDENTITY oszlopokat.

Az adatbázisséma jobb megértéséhez ez a példa bemutatja, hogyan integrálható sys.identity_columns 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

A IDENTITY tulajdonság nem használható:

  • Ha az oszlop adattípusa nem INT vagy BIGINT
  • Ha az oszlop egyben a terjesztési kulcs is
  • When the table is an external table

A dedikált SQL-készlet nem támogatja a következő kapcsolódó függvényeket:

Gyakori feladatok

A következő mintakód segítségével gyakori feladatokat végezhet el a(z) IDENTITY oszlopokkal való munkavégzés során.

Column C1 is the IDENTITY in all the following tasks.

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

Find the seed and increment for the IDENTITY property

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

Kapcsolódó tartalom

  • Last updated on