IDENTITY gebruiken om surrogaatsleutels te maken in een toegewezen SQL-pool

In dit artikel vindt u aanbevelingen en voorbeelden voor het gebruik van de eigenschap voor het IDENTITY maken van surrogaatsleutels voor tabellen in een toegewezen SQL-pool.

Wat is een surrogaatsleutel?

Een surrogaatsleutel in een tabel is een kolom met een unieke id voor elke rij. De sleutel wordt niet gegenereerd op basis van de tabelgegevens. Gegevensmodelleerders maken graag surrogaatsleutels in hun tabellen wanneer ze datawarehouse-modellen ontwerpen. U kunt de IDENTITY eigenschap gebruiken om dit doel eenvoudig en effectief te bereiken zonder dat dit van invloed is op de laadprestaties.

Notitie

In Azure Synapse Analytics:

• De IDENTITY-waarde neemt in elke distributie vanzelf toe en overlapt niet met IDENTITY-waarden in andere distributies. In Synapse is de IDENTITEIT-waarde niet gegarandeerd uniek als de gebruiker expliciet een dubbele waarde invoegt met SET IDENTITY_INSERT ON of de IDENTITEIT opnieuw instelt. Zie CREATE TABLE (Transact-SQL) IDENTITY (Eigenschap) voor meer informatie.
• UPDATE op distributiekolom garandeert niet dat de ID-waarde uniek is. Gebruik DBCC CHECKIDENT (Transact-SQL) na UPDATE op distributiekolom om de uniekheid te verifiëren.

Een tabel maken met een identity-kolom

De IDENTITY eigenschap is ontworpen om uit te schalen voor alle distributies in de toegewezen SQL-pool zonder dat dit van invloed is op de prestatie. Daarom is de implementatie van IDENTITY gericht op het bereiken van deze doelstellingen.

U kunt een tabel definiëren met de eigenschap IDENTITY wanneer u de tabel voor de eerste keer aanmaakt, door gebruik te maken van syntaxis die lijkt op de volgende uitdrukking:

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

Vervolgens kunt u INSERT..SELECT gebruiken om de tabel te vullen.

In de rest van deze sectie worden de nuances van de implementatie benadrukt, zodat u deze beter kunt begrijpen.

Toewijzing van waarden

De IDENTITY eigenschap garandeert niet de volgorde waarin de surrogaatwaarden worden toegewezen vanwege de gedistribueerde architectuur van het datawarehouse. De IDENTITY eigenschap is ontworpen om uit te schalen over alle distributies in de toegewijde SQL-pool zonder dat dit van invloed is op de belastingsprestaties.

Het volgende voorbeeld is een afbeelding:

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

In het voorgaande voorbeeld zijn twee rijen geland in distributie 1. De eerste rij heeft de surrogaatwaarde 1 in kolom C1en de tweede rij heeft de surrogaatwaarde 61. Beide waarden zijn gegenereerd door de IDENTITY eigenschap. De toewijzing van de waarden is echter niet aaneengesloten. Dit is zo ontworpen.

Scheve gegevens

Het waardenbereik voor het gegevenstype is gelijkmatig verdeeld over de distributies. Als een gedistribueerde tabel last heeft van scheefgetrokken gegevens, kan het bereik van waarden dat beschikbaar is voor het gegevenstype voortijdig worden uitgeput. Als bijvoorbeeld alle gegevens in één distributie terechtkomen, heeft de tabel in feite toegang tot slechts één zestiende van de waarden van het gegevenstype. Daarom is de IDENTITY eigenschap beperkt tot INT en BIGINT alleen gegevenstypen.

SELECT..INTO

Wanneer een bestaande IDENTITY kolom is geselecteerd in een nieuwe tabel, neemt de nieuwe kolom de IDENTITY eigenschap over, tenzij aan een van de volgende voorwaarden wordt voldaan:

• De SELECT instructie bevat een join.
• Meerdere SELECT instructies worden samengevoegd met behulp van UNION.
• De IDENTITY kolom wordt meer dan één keer weergegeven in de SELECT lijst.
• De IDENTITY kolom maakt deel uit van een expressie.

Als een van deze voorwaarden waar is, wordt de kolom NOT NULL gemaakt in plaats van de IDENTITY eigenschap te erven.

TABEL MAKEN ALS SELECTEER

CREATE TABLE AS SELECT (CTAS) volgt hetzelfde SQL Server-gedrag dat wordt beschreven voor SELECT..INTO. U kunt echter geen eigenschap opgeven IDENTITY in de kolomdefinitie van het CREATE TABLE deel van de instructie. U kunt de IDENTITY functie ook niet gebruiken in het SELECT deel van de CTAS. Als u een tabel wilt vullen, moet u de CREATE TABLE tabel definiëren, gevolgd door INSERT..SELECT deze te vullen.

Expliciete waarden invoegen in een identiteitskolom

Toegewezen SQL-pool ondersteunt SET IDENTITY_INSERT <your table> ON|OFF syntaxis. U kunt deze syntaxis gebruiken om expliciet waarden in de kolom in te IDENTITY voegen.

Veel gegevensmodelleerders gebruiken graag vooraf gedefinieerde negatieve waarden voor bepaalde rijen in hun dimensies. Een voorbeeld is de rij -1 of onbekend lid .

In het volgende script ziet u hoe u deze rij expliciet toevoegt met behulp van 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;

Gegevens laden

De aanwezigheid van de IDENTITY eigenschap heeft enkele gevolgen voor het laden van gegevens. In deze sectie worden enkele basispatronen voor het laden van gegevens in tabellen gemarkeerd met behulp van IDENTITY.

Als u gegevens in een tabel wilt laden en een surrogaatsleutel wilt genereren met behulp van IDENTITY, maakt u de tabel en gebruikt u INSERT..SELECT of INSERT..VALUES om het laadproces uit te voeren.

In het volgende voorbeeld wordt het basispatroon gemarkeerd:

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

Notitie

Het is momenteel niet mogelijk om CREATE TABLE AS SELECT te gebruiken wanneer u gegevens laadt in een tabel met een IDENTITY kolom.

Zie ELT (Extract, Load and Transform) ontwerpen voor toegewezen SQL-pools en aanbevolen procedures voor gegevens laden voor meer informatie over het laden van gegevens.

Systeemweergaven

U kunt de sys.identity_columns catalogusweergave gebruiken om een kolom met de IDENTITY eigenschap te identificeren.

In dit voorbeeld ziet u hoe u het databaseschema beter kunt integreren sys.identity_columns met andere systeemcatalogusweergaven:

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

Beperkingen

De IDENTITY eigenschap kan niet worden gebruikt:

• Wanneer het gegevenstype van de kolom niet INT of BIGINT
• Wanneer de kolom ook de distributiesleutel is
• Wanneer de tabel een externe tabel is

De volgende gerelateerde functies worden niet ondersteund in een toegewezen SQL-pool:

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

Algemene taken

U kunt de volgende voorbeeldcode gebruiken om algemene taken uit te voeren wanneer u met IDENTITY kolommen werkt.

Kolom C1 is de IDENTITY in alle volgende taken.

De hoogste toegewezen waarde voor een tabel zoeken

Gebruik de MAX() functie om de hoogste waarde te bepalen die is toegewezen voor een gedistribueerde tabel:

SELECT MAX(C1)
FROM dbo.T1

Zoek de seed en increment voor de eigenschap IDENTITY

U kunt de catalogusweergaven gebruiken om de identiteitsverhogings- en seed-configuratiewaarden voor een tabel te detecteren met behulp van de volgende query:

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

Gerelateerde inhoud

• Tabellen ontwerpen met behulp van een toegewezen SQL-pool
• CREATE TABLE (Transact-SQL) IDENTITY (eigenschap)
• DBCC CHECKIDENT (Transact-SQL)