Identiteit gebruiken om surrogaatsleutels te maken met behulp van een toegewezen SQL-pool in Azure Synapse Analytics
In dit artikel vindt u aanbevelingen en voorbeelden voor het gebruik van de eigenschap IDENTITY om surrogaatsleutels te maken voor tabellen in een toegewezen SQL-pool.
Wat is een surrogaatsleutel
Een surrogaatsleutel voor 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 voor hun tabellen wanneer ze datawarehouse-modellen ontwerpen. U kunt de eigenschap IDENTITY gebruiken om dit doel eenvoudig en effectief te bereiken zonder dat dit van invloed is op de laadprestaties.
Notitie
In Azure Synapse Analytics:
- De identiteitswaarde neemt in elke distributie vanzelf toe en overlapt niet met IDENTITEITSwaarden in andere distributies. De id-waarde in Synapse is niet gegarandeerd uniek als de gebruiker expliciet een dubbele waarde met 'SET IDENTITY_INSERT ON' invoegt of identiteit opnieuw instelt. Zie CREATE TABLE (Transact-SQL) IDENTITY (Property) voor meer informatie.
- BIJWERKEN op distributiekolom garandeert niet dat de ID-waarde uniek is. Gebruik DBCC CHECKIDENT (Transact-SQL) na UPDATE voor distributiekolom om de uniekheid te verifiëren.
Een tabel met een kolom IDENTITY maken
De eigenschap IDENTITY is ontworpen om uit te schalen over alle distributies in de toegewezen SQL-pool zonder dat dit van invloed is op de laadprestaties. Daarom is de implementatie van IDENTITY gericht op het bereiken van deze doelen.
U kunt een tabel definiëren als de eigenschap IDENTITY wanneer u de tabel voor het eerst maakt met behulp van een syntaxis die vergelijkbaar is met de volgende instructie:
CREATE TABLE dbo.T1
( C1 INT IDENTITY(1,1) NOT NULL
, C2 INT NULL
)
WITH
( DISTRIBUTION = HASH(C2)
, CLUSTERED COLUMNSTORE INDEX
)
;
U kunt vervolgens gebruiken INSERT..SELECT om de tabel te vullen.
In de rest van deze sectie worden de nuances van de implementatie uitgelicht, zodat u deze beter kunt begrijpen.
Toewijzing van waarden
De eigenschap IDENTITY garandeert niet de volgorde waarin de surrogaatwaarden worden toegewezen vanwege de gedistribueerde architectuur van het datawarehouse. De eigenschap IDENTITY is ontworpen om uit te schalen over alle distributies in de toegewezen SQL-pool zonder dat dit van invloed is op de laadprestaties.
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 in distributie 1 terechtgekomen. De eerste rij heeft de surrogaatwaarde 1 in kolom C1en de tweede rij heeft de surrogaatwaarde 61. Beide waarden zijn gegenereerd door de eigenschap IDENTITY. De toewijzing van de waarden is echter niet aaneengesloten. Dit gedrag is standaard.
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 een zestigste van de waarden van het gegevenstype. Daarom is de eigenschap IDENTITY beperkt tot INT en BIGINT alleen gegevenstypen.
SELECTEER.. IN
Wanneer een bestaande kolom IDENTITY wordt geselecteerd in een nieuwe tabel, neemt de nieuwe kolom de eigenschap IDENTITY 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 kolom IDENTITY wordt meer dan één keer weergegeven in de lijst SELECT.
- De kolom IDENTITY maakt deel uit van een expressie.
Als aan een van deze voorwaarden wordt voldaan, wordt de kolom NIET NULL gemaakt in plaats van de eigenschap IDENTITY over te nemen.
CREATE TABLE AS SELECT
CREATE TABLE AS SELECT (CTAS) volgt hetzelfde SQL Server gedrag dat is gedocumenteerd voor SELECT. IN. U kunt echter geen eigenschap IDENTITY opgeven in de kolomdefinitie van het CREATE TABLE deel van de instructie. U kunt de functie IDENTITY ook niet gebruiken in het SELECT deel van de CTAS. Als u een tabel wilt vullen, moet u gebruiken CREATE TABLE om de tabel te definiëren, gevolgd door INSERT..SELECT om deze te vullen.
Expliciet 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 te voegen in de kolom IDENTITY.
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 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 eigenschap IDENTITY heeft enkele gevolgen voor uw code voor het laden van gegevens. In deze sectie worden enkele basispatronen beschreven voor het laden van gegevens in tabellen met behulp van IDENTITY.
Als u gegevens in een tabel wilt laden en een surrogaatsleutel wilt genereren met behulp van IDENTITEIT, maakt u de tabel en gebruikt u vervolgens INSERT.. SELECT of INSERT.. WAARDEN om het laden 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 gegevens te laden CREATE TABLE AS SELECT in een tabel met een kolom IDENTITY.
Zie Designing Extract, Load, and Transform (ELT) for dedicated SQL pool (Designing Extract, Load, and Transform) for dedicated SQL pool (Designing Extract, Load, and Transform) (ELT) ontwerpen voor meer informatie over het laden van gegevens en best practices voor laden.
Systeemweergaven
U kunt de sys.identity_columns catalogusweergave gebruiken om een kolom te identificeren die de eigenschap IDENTITY bevat.
Om u meer inzicht te geven in het databaseschema, laat dit voorbeeld zien hoe u sys.identity_column' integreert 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 eigenschap IDENTITY kan niet worden gebruikt:
- Wanneer het gegevenstype van de kolom niet INT of BIGINT is
- 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:
Algemene taken
Deze sectie bevat enkele voorbeeldcode die u kunt gebruiken om algemene taken uit te voeren wanneer u met IDENTITY-kolommen werkt.
Kolom C1 is de IDENTITEIT 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
De seed en increment voor de eigenschap IDENTITY zoeken
U kunt de catalogusweergaven gebruiken om de identiteitsverhogings- en seedconfiguratiewaarden 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'
;