Använda IDENTITY för att skapa surrogatnycklar med hjälp av en dedikerad SQL-pool i Azure Synapse Analytics
I den här artikeln hittar du rekommendationer och exempel för hur du använder egenskapen IDENTITY för att skapa surrogatnycklar i tabeller i en dedikerad SQL-pool.
Vad är en surrogatnyckel?
En surrogatnyckel i en tabell är en kolumn med en unik identifierare för varje rad. Nyckeln genereras inte från tabelldata. Datamodellerare gillar att skapa surrogatnycklar på sina tabeller när de utformar datalagermodeller. Du kan använda IDENTITY-egenskapen för att uppnå det här målet enkelt och effektivt utan att påverka belastningsprestandan.
Anteckning
I Azure Synapse Analytics:
- IDENTITY-värdet ökar på egen hand i varje distribution och överlappar inte IDENTITY-värden i andra distributioner. IDENTITY-värdet i Synapse är inte garanterat unikt om användaren uttryckligen infogar ett dubblettvärde med "SET IDENTITY_INSERT ON" eller om identiteten har återställts. Mer information finns i CREATE TABLE (Transact-SQL) IDENTITY (Property).
- UPDATE i distributionskolumnen garanterar inte att IDENTITY-värdet är unikt. Använd DBCC CHECKIDENT (Transact-SQL) efter UPDATE i distributionskolumnen för att verifiera unikhet.
Skapa en tabell med en identitetskolumn
Egenskapen IDENTITY är utformad för att skala ut över alla distributioner i den dedikerade SQL-poolen utan att påverka belastningsprestandan. Därför är implementeringen av IDENTITY inriktad på att uppnå dessa mål.
Du kan definiera att en tabell har egenskapen IDENTITY när du först skapar tabellen med hjälp av syntax som liknar följande instruktion:
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1) NOT NULL
, C2 INT NULL
)
WITH
( DISTRIBUTION = HASH(C2)
, CLUSTERED COLUMNSTORE INDEX
)
;
Du kan sedan använda INSERT..SELECT för att fylla i tabellen.
Resten av det här avsnittet belyser nyanserna i implementeringen för att hjälpa dig att förstå dem mer fullständigt.
Allokering av värden
Egenskapen IDENTITY garanterar inte i vilken ordning surrogatvärdena allokeras på grund av datalagrets distribuerade arkitektur. Egenskapen IDENTITY är utformad för att skala ut över alla distributioner i den dedikerade SQL-poolen utan att påverka belastningsprestandan.
Följande exempel är en bild:
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');
I föregående exempel landade två rader i distribution 1. Den första raden har surrogatvärdet 1 i kolumnen C1och den andra raden har surrogatvärdet 61. Båda dessa värden genererades av egenskapen IDENTITY. Fördelningen av värdena är dock inte sammanhängande. Det här beteendet är avsiktligt.
Skeva data
Intervallet med värden för datatypen är jämnt fördelat över distributionerna. Om en distribuerad tabell har skeva data kan värdeintervallet som är tillgängligt för datatypen förbrukas i förtid. Om alla data till exempel hamnar i en enda distribution har tabellen i själva verket endast åtkomst till en sexdelad av datatypens värden. Därför är egenskapen IDENTITY begränsad till INT och BIGINT endast datatyper.
VÄLJ.. I
När en befintlig IDENTITY-kolumn väljs i en ny tabell ärver den nya kolumnen egenskapen IDENTITY, såvida inte något av följande villkor är sant:
- SELECT-instruktionen innehåller en koppling.
- Flera SELECT-instruktioner kopplas med hjälp av UNION.
- IDENTITY-kolumnen visas mer än en gång i SELECT-listan.
- IDENTITY-kolumnen är en del av ett uttryck.
Om något av dessa villkor är sant skapas kolumnen NOT NULL i stället för att ärva egenskapen IDENTITY.
CREATE TABLE AS SELECT
CREATE TABLE AS SELECT (CTAS) följer samma SQL Server beteende som dokumenteras för SELECT.. I. Du kan dock inte ange en identitetsegenskap i kolumndefinitionen CREATE TABLE för delen av -instruktionen. Du kan inte heller använda funktionen IDENTITY i delen SELECT av CTAS. För att fylla i en tabell måste du använda CREATE TABLE för att definiera tabellen följt av INSERT..SELECT för att fylla i den.
Uttryckligen infoga värden i en identitetskolumn
Dedikerad SQL-pool stöder SET IDENTITY_INSERT <your table> ON|OFF syntax. Du kan använda den här syntaxen för att uttryckligen infoga värden i IDENTITY-kolumnen.
Många datamodellerare gillar att använda fördefinierade negativa värden för vissa rader i sina dimensioner. Ett exempel är raden -1 eller "okänd medlem".
Nästa skript visar hur du uttryckligen lägger till den här raden med 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
;
Läser in data
Förekomsten av identitetsegenskapen har vissa konsekvenser för din datainläsningskod. I det här avsnittet beskrivs några grundläggande mönster för att läsa in data i tabeller med hjälp av IDENTITY.
Om du vill läsa in data i en tabell och generera en surrogatnyckel med hjälp av IDENTITY skapar du tabellen och använder sedan INSERT.. SELECT eller INSERT.. VÄRDEN för att utföra belastningen.
I följande exempel visas det grundläggande mönstret:
--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');
Anteckning
Det går inte att använda CREATE TABLE AS SELECT för närvarande när data läses in i en tabell med en identitetskolumn.
Mer information om hur du läser in data finns i Designing Extract, Load, and Transform (ELT) for dedicated SQL pool and Loading best practices (Designing Extract, Load, and Transform( ELT) for dedicated SQL pool and Loading best practices (Designing Extract, Load, and Transform( ELT) for dedicated SQL pool and Loading best practices (Designing Extract, Load, and Transform( ELT) for dedicated SQL pool
Systemvyer
Du kan använda sys.identity_columns katalogvyn för att identifiera en kolumn som har identitetsegenskapen.
För att hjälpa dig att bättre förstå databasschemat visar det här exemplet hur du integrerar sys.identity_column med andra systemkatalogvyer:
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'
;
Begränsningar
IDENTITY-egenskapen kan inte användas:
- När kolumndatatypen inte är INT eller BIGINT
- När kolumnen också är distributionsnyckeln
- När tabellen är en extern tabell
Följande relaterade funktioner stöds inte i en dedikerad SQL-pool:
Vanliga åtgärder
Det här avsnittet innehåller exempelkod som du kan använda för att utföra vanliga uppgifter när du arbetar med IDENTITY-kolumner.
Kolumn C1 är identiteten i alla följande uppgifter.
Hitta det högsta allokerade värdet för en tabell
MAX() Använd funktionen för att fastställa det högsta värdet som allokerats för en distribuerad tabell:
SELECT MAX(C1)
FROM dbo.T1
Hitta seed och increment för IDENTITY-egenskapen
Du kan använda katalogvyerna för att identifiera konfigurationsvärdena för identitetsökning och startvärde för en tabell med hjälp av följande fråga:
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'
;