Colonne IDENTITY nel data warehouse di Fabric

si applica a:✅ Magazzino di dati in Microsoft Fabric

In Fabric Data Warehouse, IDENTITY le colonne generano automaticamente nuovi valori numerici quando inserisci nuove righe in una tabella.

Le chiavi surrogate sono identificatori usati nel data warehousing per distinguere in modo univoco le righe, indipendentemente dalla loro chiave naturale. Questo articolo spiega come creare e gestire le chiavi surrogate usando IDENTITY, inclusa l'inserzione di valori espliciti e il reseeding.

Perché usare una colonna IDENTITY?

IDENTITY Le colonne eliminano l'assegnazione manuale delle chiavi, riducendo il rischio di errori e semplificando l'ingestione dei dati. I valori univoci gestiti dal sistema sono ideali come chiavi surrogate e chiavi primarie. Rispetto agli approcci manuali, IDENTITY le colonne offrono prestazioni migliori perché le chiavi uniche vengono generate automaticamente senza una logica di query aggiuntiva.

Il tipo di dato bigint , richiesto per le IDENTITY colonne, può memorizzare fino a 9.223.372.036.854.775.807 valori interi positivi. Questo intervallo garantisce che ogni riga riceva un valore unico nella propria IDENTITY colonna durante tutta la vita della tabella.

Per un piano di migrazione dei dati con chiavi surrogate da altre piattaforme di database, vedere Eseguire la migrazione di colonne IDENTITY a Fabric Data Warehouse.

Sintassi

Per definire una IDENTITY colonna in Fabric Data Warehouse, usa la IDENTITY proprietà nella definizione della colonna:

CREATE TABLE { warehouse_name.schema_name.table_name | schema_name.table_name | table_name } (
    [ column_name ] BIGINT IDENTITY ,
    [ ,...n ]
    -- Other columns here
);

La colonna identità non deve necessariamente essere la prima colonna nella definizione della tabella.

Funzionamento delle colonne IDENTITY

In Fabric Data Warehouse, non puoi specificare un valore iniziale personalizzato o un incremento. Il sistema gestisce i valori internamente per garantire l'unicità. IDENTITY le colonne producono sempre valori integer positivi. Ogni nuova riga riceve un nuovo valore e l'univocità è garantita finché la tabella esiste. Una volta usato un valore, IDENTITY non usa più lo stesso valore. Possono comparire spazi vuoti nei valori prodotti dalla IDENTITY colonna.

Allocazione di valori

A causa dell'architettura distribuita del motore di magazzino, la IDENTITY proprietà non garantisce l'ordine in cui vengono allocati i valori surrogati. La proprietà si espande tra i nodi di calcolo per massimizzare il parallelismo senza compromettere le prestazioni del carico. Di conseguenza, gli intervalli di valore provenienti da diversi compiti di ingestione potrebbero non essere sequenziali.

Il seguente esempio illustra questo comportamento.

-- Create a table with an IDENTITY column
CREATE TABLE dbo.Table1(
    Column1 BIGINT IDENTITY,
    Column2 VARCHAR(30) NULL
)

-- Ingestion task A
INSERT INTO dbo.Table1
VALUES (NULL), (NULL), (NULL), (NULL);

-- Ingestion task B
INSERT INTO dbo.Table1
VALUES (NULL), (NULL), (NULL), (NULL);

-- Review the data
SELECT * FROM dbo.Table1;

Risultato di esempio:

Screenshot del set di risultati di una query di una tabella con due colonne etichettate Colonna1 e Colonna2, che mostrano otto righe di dati. La colonna 1 contiene valori numerici grandi, la colonna 2 contiene il testo.

In questo esempio, Ingestion task A ed Ingestion task B eseguite sequencialmente come compiti indipendenti. Sebbene i compiti vengano eseguiti consecutivamente, la prima e l'ultima quattro righe hanno intervalli di chiave identità diversi in dbo.Table1.Column1. Possono anche verificarsi spazi tra le fasce assegnate al compito A e al compito B.

IDENTITYin Fabric Data Warehouse garantisce che tutti i valori in una IDENTITY colonna siano unici purché IDENTITY_INSERT non venga utilizzato, ma possono verificarsi lacune negli intervalli prodotti per un compito di ingestione.

Oggetti metadati di sistema

I seguenti oggetti metadati di sistema sono disponibili e utili quando si progettano e si lavorano i valori identità in Fabric Data Warehouse.

Elenca le colonne IDENTITY con la vista di sistema sys.identity_columns

Usa la vista catalogo sys.identity_columns per elencare tutte le colonne di identità in un magazzino. Il seguente esempio elenca tutte le tabelle che contengono una IDENTITY colonna, inclusi i nomi delle colonne schema, tabella e identità:

SELECT
    s.name AS SchemaName,
    t.name AS TableName,
    c.name AS IdentityColumnName
FROM
    sys.identity_columns AS ic
INNER JOIN
    sys.columns AS c ON ic.[object_id] = c.[object_id]
    AND ic.column_id = c.column_id
INNER JOIN
    sys.tables AS t ON ic.[object_id] = t.[object_id]
INNER JOIN
    sys.schemas AS s ON t.[schema_id] = s.[schema_id]
ORDER BY
    s.name, t.name;

In Fabric Data Warehouse, le colonne seed_value e increment_value di sys.identity_columns restituiscono NULL e non vengono aggiornate dopo la creazione della colonna di identità. La last_value colonna ritorna NULL di default, ma passa permanentemente a -1 dopo la prima operazione di inserimento identità sulla tabella.

Inserire valori con IDENTITY_INSERT

Di default, non puoi inserire valori in una IDENTITY colonna. Tuttavia, potresti dover inserire valori specifici durante la migrazione dei dati, il disaster recovery o quando popoli valori sentinella, ad esempio -1 per "Sconosciuto" nelle tabelle delle dimensioni.

Usa SET IDENTITY_INSERT per consentire temporaneamente inserti espliciti in una colonna identità:

SET IDENTITY_INSERT dbo.DimCustomer ON;

INSERT INTO dbo.DimCustomer (CustomerKey, CustomerName, Email)
VALUES (-1, 'John Doe', 'john@contoso.com');

SET IDENTITY_INSERT dbo.DimCustomer OFF;

Quando IDENTITY_INSERT è ON:

  • È richiesta una lista di colonne con l'affermazione INSERT .
  • Solo una tabella per sessione può avere IDENTITY_INSERT impostato su ON in un dato momento.

Importante

Dopo la disattivazione di IDENTITY_INSERT, reinizializzare i valori di identità con DBCC CHECKIDENT.

Reimpostare i valori IDENTITY con DBCC CHECKIDENT

Dopo aver inserito valori espliciti mediante IDENTITY_INSERT, usa DBCC CHECKIDENT per reimpostare il seed della colonna IDENTITY. L'operazione RESEED scansiona tutti gli intervalli di identità utilizzati e riservati tra i nodi di calcolo distribuiti per determinare i valori successivi corretti, garantendo l'unicità e prevenendo collisioni di chiavi.

DBCC CHECKIDENT('dbo.DimProduct', RESEED);

In Fabric Data Warehouse, DBCC CHECKIDENT supporta solo l'opzioneRESEED. Il magazzino determina automaticamente i successivi intervalli di valore corretti, e non puoi specificare un valore di reseed personalizzato. Per altre informazioni, vedere DBCC CHECKIDENT.

Limitazioni

Per ulteriori informazioni, consulta le colonne IDENTITY, IDENTITY (Transact-SQL) e Crea tabelle nel Warehouse in Microsoft Fabric.

  • Solo il tipo di dati bigint è supportato nelle colonne in IDENTITY Fabric Data Warehouse. Altri tipi di dati comportano un errore.
  • La definizione di un valore iniziale e di un incremento non è supportata. Il sistema gestisce i valori internamente.
  • Aggiungere una IDENTITY colonna a una tabella esistente con ALTER TABLE non è supportato. Considera di usare CREATE TABLE AS SELECT (CTAS) o SELECT... INTO per creare una copia di una tabella esistente e aggiungere una IDENTITY colonna.
  • Si applicano limitazioni al modo in cui IDENTITY le colonne vengono mantenute quando si crea una tabella selezionando da un'altra tabella con CTAS o SELECT...INTO. Per ulteriori informazioni, consulta la sezione Tipi di dati della clausola SELECT - INTO (Transact-SQL).
  • DBCC CHECKIDENT Supporta solo questa RESEED opzione. Non è supportato specificare un valore di reseed personalizzato oppure usare NORESEED.
  • IDENTITY Le colonne producono valori che sono garantiti unici, ma i valori non sono necessariamente sequenziali o ordinati, e possono verificarsi lacune.

Esempi

A. Creare una tabella con una colonna IDENTITY

CREATE TABLE Employees (
    EmployeeID BIGINT IDENTITY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Questa istruzione crea una Employees tabella in cui ogni nuova riga riceve automaticamente un valore unico EmployeeID come bigint .

B. Inserire righe in una tabella con una colonna identità

Quando fornisci valori per ogni colonna non identità nel loro ordine definito, non è necessario specificare una lista di colonne:

INSERT INTO Employees VALUES ('Quarantino', 'Esposito');

Puoi anche fornire una lista di colonne che omette la colonna identità:

INSERT INTO Employees (FirstName, LastName)
VALUES ('Ensi', 'Vasala');

C. Inserire valori espliciti con IDENTITY_INSERT

SET IDENTITY_INSERT dbo.Employees ON;

INSERT INTO dbo.Employees (EmployeeID, FirstName, LastName)
VALUES (100, 'Sentinel', 'Row');

SET IDENTITY_INSERT dbo.Employees OFF;

D. Inserire valori espliciti con COPY INTO

L'istruzione COPY INTO supporta l'opzione IDENTITY_INSERT di ingerire valori espliciti all'interno del comando. COPY INTO Le opzioni sovrascrivono qualsiasi impostazione a livello di sessione per IDENTITY_INSERT.

COPY INTO dbo.Employees (EmployeeID 1, FirstName 2, LastName 3)
FROM 'https://myaccount.blob.core.windows.net/myblobcontainer/folder1/'
WITH (
    FILE_TYPE = 'CSV',
    IDENTITY_INSERT = 'ON'
);

E. Risemina una tabella dopo inserimenti espliciti

DBCC CHECKIDENT('dbo.Employees', RESEED);

F. Crea una tabella con CREATE TABLE AS SELECT

Usa CTAS per creare una copia di una tabella e mantenere la IDENTITY proprietà nella tabella target:

CREATE TABLE RetiredEmployees
AS SELECT * FROM Employees;

La colonna nella tabella target eredita la IDENTITY proprietà dalla tabella sorgente. Per le limitazioni, vedi la sezione Data Types della clausola SELECT - INTO.

G. Crea una tabella con SELECT...INTO

Usare SELECT...INTO per creare una copia di una tabella e far persistere la IDENTITY proprietà nella tabella di destinazione:

SELECT *
INTO dbo.RetiredEmployees
FROM dbo.Employees
WHERE LastName = 'Esposito';

La colonna nella tabella target eredita la IDENTITY proprietà dalla tabella sorgente. Per le limitazioni, vedi la sezione Data Types della clausola SELECT - INTO.

Passo successivo

Usa le colonne IDENTITY in Fabric Data Warehouse

Contenuti correlati

  • Last updated on