Použití IDENTITY k vytvoření náhradních klíčů s využitím vyhrazeného fondu SQL v Azure Synapse Analytics

V tomto článku najdete doporučení a příklady použití vlastnosti IDENTITY k vytvoření náhradních klíčů u tabulek ve vyhrazeném fondu SQL.

Co je náhradní klíč

Náhradní klíč v tabulce je sloupec s jedinečným identifikátorem pro každý řádek. Klíč se negeneruje z dat tabulky. Modelátoři dat při návrhu modelů datového skladu rádi ve svých tabulkách vytvářejí náhradní klíče. Vlastnost IDENTITY můžete použít k dosažení tohoto cíle jednoduše a efektivně, aniž by to mělo vliv na výkon načítání.

Poznámka

V Azure Synapse Analytics:

• Hodnota IDENTITY se v každém rozdělení zvyšuje sama o sobě a nepřekrývá se s hodnotami IDENTITY v jiných distribucích. Hodnota IDENTITY ve službě Synapse není zaručená jedinečná, pokud uživatel explicitně vloží duplicitní hodnotu s parametrem SET IDENTITY_INSERT ON nebo znovu nastaví identitu. Podrobnosti najdete v tématu CREATE TABLE (Transact-SQL) IDENTITY (Vlastnost).
• Update v distribučním sloupci nezaručuje jedinečnou hodnotu IDENTITY. Pomocí příkazu DBCC CHECKIDENT (Transact-SQL) po aktualizaci v distribučním sloupci ověřte jedinečnost.

Vytvoření tabulky se sloupcem IDENTITY

Vlastnost IDENTITY je navržená tak, aby škálovat na více instancí napříč všemi distribucemi ve vyhrazeném fondu SQL, aniž by to mělo vliv na výkon načítání. Proto se implementace IDENTITY zaměřuje na dosažení těchto cílů.

Tabulku můžete definovat tak, aby měla vlastnost IDENTITY při prvním vytvoření tabulky pomocí syntaxe podobné následujícímu příkazu:

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

Tabulku pak můžete naplnit pomocí příkazu INSERT..SELECT .

V této části se zvýrazní drobné odlišnosti implementace, abyste je lépe pochopili.

Přidělení hodnot

Vlastnost IDENTITY nezaručuje pořadí, ve kterém se náhradní hodnoty přidělují, kvůli distribuované architektuře datového skladu. Vlastnost IDENTITY je navržená tak, aby škálovat na více instancí napříč všemi distribucemi ve vyhrazeném fondu SQL, aniž by to mělo vliv na výkon načítání.

Následující příklad je znázorněný na obrázku:

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

V předchozím příkladu se dva řádky dostaly do distribuce 1. První řádek má náhradní hodnotu 1 ve sloupci C1a druhý řádek má náhradní hodnotu 61. Obě tyto hodnoty byly vygenerovány vlastností IDENTITY. Přidělení hodnot však není souvislé. Toto chování je záměrné.

Nerovnoměrná data

Rozsah hodnot určitého datového typu je rovnoměrně rozložený napříč distribucemi. Pokud distribuovaná tabulka trpí nerovnoměrnou distribucí dat, může dojít k předčasnému vyčerpání rozsahu hodnot dostupných pro daný datový typ. Pokud například všechna data skončí v jednom rozdělení, pak má tabulka ve skutečnosti přístup pouze k 1-šedesátiny hodnot datového typu. Z tohoto důvodu je vlastnost IDENTITY omezena pouze na INT datové typy a BIGINT .

VYBERTE.. DO

Pokud je existující sloupec IDENTITY vybraný do nové tabulky, zdědí nový sloupec vlastnost IDENTITY, pokud není splněna jedna z následujících podmínek:

• Příkaz SELECT obsahuje spojení.
• Několik příkazů SELECT je spojených pomocí klauzule UNION.
• Sloupec IDENTITY je v seznamu SELECT uvedený vícekrát.
• Sloupec IDENTITY je součástí výrazu.

Pokud je některá z těchto podmínek splněná, vytvoří se sloupec NOT NULL místo dědění vlastnosti IDENTITY.

CREATE TABLE AS SELECT

FUNKCE CREATE TABLE AS SELECT (CTAS) se řídí stejným SQL Server chováním, které je popsáno pro funkci SELECT. DO. V definici CREATE TABLE sloupce části příkazu ale nemůžete zadat vlastnost IDENTITY. V části CTAS také nemůžete použít funkci SELECT IDENTITY. Pokud chcete naplnit tabulku, musíte použít CREATE TABLE příkaz k definování tabulky, po INSERT..SELECT které následuje k jejímu naplnění.

Explicitní vkládání hodnot do sloupce IDENTITY

Vyhrazený fond SQL podporuje SET IDENTITY_INSERT <your table> ON|OFF syntaxi. Pomocí této syntaxe můžete explicitně vložit hodnoty do sloupce IDENTITY.

Mnozí modelátoři dat rádi používají předdefinované záporné hodnoty pro určité řádky ve svých dimenzích. Příkladem je řádek -1 nebo "neznámý člen".

Další skript ukazuje, jak explicitně přidat tento řádek pomocí IDENTITY_INSERT SET:

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
;

Načítají se data

Přítomnost vlastnosti IDENTITY má určité důsledky pro kód pro načítání dat. Tato část popisuje některé základní vzory pro načítání dat do tabulek pomocí identity.

Pokud chcete načíst data do tabulky a vygenerovat náhradní klíč pomocí identity, vytvořte tabulku a pak použijte insert. VYBERTE nebo INSERT. HODNOTY pro provedení zatížení.

Následující příklad zvýrazňuje základní vzor:

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

Poznámka

Aktuálně není možné použít CREATE TABLE AS SELECT při načítání dat do tabulky se sloupcem IDENTITY.

Další informace o načítání dat najdete v tématech Návrh extrakce, načítání a transformace (ELT) pro vyhrazený fond SQL a Osvědčené postupy načítání.

Systémová zobrazení

Pomocí zobrazení katalogu sys.identity_columns můžete identifikovat sloupec, který má vlastnost IDENTITY.

Abychom vám pomohli lépe porozumět schématu databáze, tento příklad ukazuje, jak integrovat sys.identity_column' s jinými zobrazeními systémového katalogu:

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

Omezení

Vlastnost IDENTITY není možné použít v následujících případech:

• Pokud datový typ sloupce není INT nebo BIGINT
• Pokud je sloupec zároveň distribučním klíčem
• Když je tabulka externí tabulkou

Následující související funkce se ve vyhrazeném fondu SQL nepodporují:

• IDENTITY()
• @@IDENTITY
• SCOPE_IDENTITY
• IDENT_CURRENT
• IDENT_INCR
• IDENT_SEED

Běžné úkoly

Tato část obsahuje ukázkový kód, který můžete použít k provádění běžných úloh při práci se sloupci IDENTITY.

Sloupec C1 je IDENTITA ve všech následujících úlohách.

Vyhledání nejvyšší přidělené hodnoty pro tabulku

MAX() Pomocí funkce můžete určit nejvyšší hodnotu přidělenou distribuované tabulce:

SELECT MAX(C1)
FROM dbo.T1

Vyhledání počáteční hodnoty a přírůstku pro vlastnost IDENTITY

Zobrazení katalogu můžete použít ke zjištění hodnot přírůstku identity a počáteční konfigurace pro tabulku pomocí následujícího dotazu:

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

Další kroky

• Přehled tabulek
• CREATE TABLE (Transact-SQL) IDENTITY (vlastnost)
• DBCC CHECKIDENT