Použití identity k vytvoření náhradních klíčů ve vyhrazeném fondu SQL

V tomto článku najdete doporučení a příklady použití IDENTITY vlastnosti k vytvoření náhradních klíčů v tabulkách 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 chtějí při návrhu modelů datového skladu vytvářet náhradní klíče ve svých tabulkách. Tuto vlastnost můžete použít IDENTITY k dosažení tohoto cíle jednoduše a efektivně, aniž by to mělo vliv na výkon zatížení.

Poznámka:

Ve službě Azure Synapse Analytics:

• Hodnota IDENTITY se v každé distribuci sama zvyšuje 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 SET IDENTITY_INSERT ON identitou nebo ji znovu vytvoří. Podrobnosti najdete v tématu CREATE TABLE (Transact-SQL) IDENTITY (vlastnost).
• FUNKCE UPDATE v distribučním sloupci nezaručuje, že hodnota IDENTITY je jedinečná. Pomocí 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 napříč všemi distribucemi ve vyhrazeném fondu SQL, aniž by to ovlivnilo výkon zatížení. Proto je implementace IDENTITY zaměřena na dosažení těchto cílů.

Tabulku můžete definovat jako IDENTITY vlastnost při prvním vytvoření tabulky pomocí syntaxe, která je 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
);

Pak můžete použít INSERT..SELECT k naplnění tabulky.

Zbývající část této části zvýrazňuje drobné odlišnosti implementace, abyste jim pomohli lépe porozumět.

Přidělení hodnot

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

Následující příklad je obrázek:

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 přistály dva řádky v rozdělení 1. První řádek má náhradní hodnotu 1 ve sloupci C1a druhý řádek má náhradní hodnotu 61. Obě tyto hodnoty byly generovány IDENTITY vlastností. Přidělení hodnot ale není souvislé. Toto chování je záměrné.

Nerovnoměrná distribuce dat

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 jedné distribuci, pak má tabulka přístup pouze k 66. 100 hodnotám datového typu. Z tohoto důvodu IDENTITY je vlastnost omezena pouze na INT datové typy a BIGINT datové typy.

VYBRAT.. DO

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

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

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

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) se řídí stejným chováním SQL Serveru, které je zdokumentované pro SELECT..INTO. V definici CREATE TABLE sloupce části příkazu ale nemůžete zadat IDENTITY vlastnost. Funkci také nemůžete použít IDENTITY v SELECT části CTAS. Pokud chcete naplnit tabulku, musíte ji použít CREATE TABLE k definování tabulky následované jejím naplněním INSERT..SELECT .

Vložení explicitních hodnot do sloupce IDENTITY

Vyhrazený fond SQL podporuje SET IDENTITY_INSERT <your table> ON|OFF syntaxi. Tuto syntaxi můžete použít k explicitní vložení hodnot do IDENTITY sloupce.

Mnoho modelů dat používá předdefinované záporné hodnoty pro určité řádky v jejich dimenzích. Příkladem je řádek -1 nebo neznámý člen .

Další skript ukazuje, jak explicitně přidat tento řádek pomocí 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;

Načtení dat

IDENTITY Přítomnost vlastnosti má určité důsledky pro váš kód načítání dat. V této části jsou zvýrazněny 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 ji použijte INSERT..SELECT nebo INSERT..VALUES proveďte načtení.

Následující příklad zvýrazní 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:

V současné době 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ématu Návrh extrakce, načtení a transformace (ELT) pro vyhrazený fond SQL a osvědčené postupy načítání.

Systémová zobrazení

Zobrazení katalogu sys.identity_columns můžete použít k identifikaci sloupce, který má IDENTITY vlastnost.

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

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 se nedá použít:

• Pokud datový typ sloupce není nebo není INTBIGINT
• Pokud je sloupec také distribučním klíčem
• Když je tabulka externí tabulkou

Ve vyhrazeném fondu SQL se nepodporují následující související funkce:

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

Běžné úkoly

Následující ukázkový kód můžete použít k provádění běžných úloh při práci se IDENTITY sloupci.

Sloupec C1 je ve IDENTITY všech následujících úkolech.

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ího a přírůstku vlastnosti IDENTITY

Zobrazení katalogu můžete použít ke zjištění hodnot konfigurace přírůstku identity a počátečních hodnot 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'
;

Související obsah

• Návrh tabulek pomocí vyhrazeného fondu SQL
• CREATE TABLE (Transact-SQL) IDENTITY (vlastnost)
• DBCC CHECKIDENT (Transact-SQL)