Dela via

Använda IDENTITY för att skapa surrogatnycklar i en dedikerad SQL-pool

  • Artikel

I den här artikeln hittar du rekommendationer och exempel för hur du använder IDENTITY egenskapen för att skapa surrogatnycklar på 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 egenskapen IDENTITY för att uppnå det här målet enkelt och effektivt utan att påverka belastningsprestandan.

Kommentar

I Azure Synapse Analytics:

  • IDENTITY-värdet ökar automatiskt i varje distribution och överlappar inte IDENTITY-värden i andra distributioner. Identitetsvärdet i Synapse är inte garanterat unikt om användaren uttryckligen infogar ett duplicerat värde med SET IDENTITY_INSERT ON eller återställer IDENTITY. 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. Genomförandet av IDENTITY är därför inriktat på att uppnå dessa mål.

Du kan definiera en tabell som att ha IDENTITY egenskapen 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 IDENTITY egenskapen. Allokeringen av värdena är dock inte sammanhängande. Detta beteende är av design.

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 praktiken endast åtkomst till en sextiedel av datatypens värden. Därför är egenskapen IDENTITY begränsad till INT och BIGINT endast datatyper.

UTVALD.. IN

När en befintlig IDENTITY kolumn har valts i en ny tabell ärver IDENTITY den nya kolumnen egenskapen, såvida inte något av följande villkor är sant:

  • -instruktionen SELECT innehåller en koppling.
  • Flera SELECT instruktioner kopplas med hjälp UNIONav .
  • Kolumnen IDENTITY visas mer än en gång i listan SELECT .
  • Kolumnen IDENTITY är en del av ett uttryck.

Om något av dessa villkor är sant skapas NOT NULL kolumnen i stället för att IDENTITY ärva egenskapen.

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) följer samma SQL Server-beteende som dokumenteras för SELECT..INTO. Du kan dock inte ange en IDENTITY egenskap i kolumndefinitionen för CREATE TABLE delen av -instruktionen. Du kan inte heller använda IDENTITY funktionen i den SELECT del 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.

Infoga explicita 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 hjälp SET IDENTITY_INSERTav :

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äsa in data

Förekomsten av IDENTITY egenskapen har vissa konsekvenser för din datainläsningskod. Det här avsnittet visar några grundläggande mönster för att läsa in data i tabeller med hjälp IDENTITYav .

Om du vill läsa in data i en tabell och generera en surrogatnyckel med hjälp IDENTITYav skapar du tabellen och använder INSERT..SELECT eller INSERT..VALUES utför sedan 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');

Kommentar

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 IDENTITY kolumn.

Mer information om hur du läser in data finns i Designa extrahering, inläsning och transformering (ELT) för dedikerad SQL-pool och metodtips för inläsning.

Systemvyer

Du kan använda sys.identity_columns katalogvy för att identifiera en kolumn som har IDENTITY egenskapen .

För att hjälpa dig att bättre förstå databasschemat visar det här exemplet hur du integrerar sys.identity_columns 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

Egenskapen IDENTITY kan inte användas:

  • När kolumndatatypen inte INT är 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

Du kan använda följande exempelkod för att utföra vanliga uppgifter när du arbetar med IDENTITY kolumner.

Kolumn C1 är i IDENTITY 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 allokerade värdet för en distribuerad tabell:

SELECT MAX(C1)
FROM dbo.T1

Hitta seed och increment för egenskapen IDENTITY

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

Relaterat innehåll