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