Condividi tramite

Usare IDENTITY per creare chiavi surrogate nel pool SQL dedicato

  • Articolo

In questo articolo sono disponibili raccomandazioni ed esempi per l'uso della IDENTITY proprietà per creare chiavi surrogate nelle tabelle nel pool SQL dedicato.

Che cos'è una chiave surrogata?

Una chiave surrogata in una tabella è una colonna con un identificatore univoco per ogni riga. La chiave non viene generata dai dati della tabella. I progettisti di modelli di dati preferiscono creare chiavi surrogate nelle tabelle durante la progettazione dei modelli per i data warehouse. È possibile usare la IDENTITY proprietà per raggiungere questo obiettivo in modo semplice ed efficace senza influire sulle prestazioni del carico.

Nota

In Azure Synapse Analytics:

  • Il valore IDENTITY aumenta autonomamente in ogni distribuzione e non si sovrappone ai valori IDENTITY di altre distribuzioni. Il valore IDENTITY in Synapse non è garantito che sia univoco se l'utente inserisce in modo esplicito un valore duplicato con SET IDENTITY_INSERT ON o invia nuovamente IDENTITY. Per i dettagli, vedere CREATE TABLE (Transact-SQL) IDENTITY (proprietà).
  • UPDATE nella colonna di distribuzione non garantisce che il valore IDENTITY sia univoco. Usare DBCC CHECKIDENT (Transact-SQL) dopo UPDATE sulla colonna di distribuzione per verificare l'univocità.

Creare una tabella con una colonna IDENTITY

La IDENTITY proprietà è progettata per aumentare il numero di istanze in tutte le distribuzioni nel pool SQL dedicato senza influire sulle prestazioni di carico. Pertanto, l'implementazione di è orientata al raggiungimento di IDENTITY questi obiettivi.

È possibile definire una tabella come con la proprietà quando si crea per IDENTITY la prima volta la tabella usando una sintassi simile all'istruzione seguente:

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

È quindi possibile usare INSERT..SELECT per popolare la tabella.

Nella parte restante di questa sezione vengono evidenziate le sfumature dell'implementazione per comprenderle in modo più completo.

Allocazione dei valori

La IDENTITY proprietà non garantisce l'ordine in cui i valori surrogati vengono allocati a causa dell'architettura distribuita del data warehouse. La IDENTITY proprietà è progettata per aumentare il numero di istanze in tutte le distribuzioni nel pool SQL dedicato senza influire sulle prestazioni di carico.

L'esempio seguente è una dimostrazione:

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

Nell'esempio precedente due righe raggiungono la distribuzione 1. La prima riga ha il valore surrogato 1 nella colonna C1 e la seconda riga ha il valore surrogato 61. Entrambi questi valori sono stati generati dalla IDENTITY proprietà . Tuttavia, l'allocazione dei valori non è contigua. Questo comportamento è impostato a livello di progettazione.

Dati asimmetrici

L'intervallo di valori per il tipo di dati è distribuito uniformemente tra le distribuzioni. Nel caso di una tabella distribuita con dati asimmetrici, l'intervallo di valori disponibili per il tipo di dati può esaurirsi in modo anomalo. Ad esempio, se tutti i dati vengono destinati a una singola distribuzione, la tabella ha effettivamente accesso solo a un sessantesimo dei valori del tipo di dati. Per questo motivo, la IDENTITY proprietà è limitata solo ai INT tipi di dati e BIGINT .

SELECT..INTO

Quando una colonna esistente IDENTITY viene selezionata in una nuova tabella, la nuova colonna eredita la IDENTITY proprietà, a meno che non venga soddisfatta una delle condizioni seguenti:

  • L'istruzione SELECT contiene un join.
  • Più SELECT istruzioni vengono unite tramite UNION.
  • La IDENTITY colonna è elencata più volte nell'elenco SELECT .
  • La IDENTITY colonna fa parte di un'espressione.

Se una di queste condizioni è vera, la colonna viene creata NOT NULL invece di ereditare la IDENTITY proprietà .

CREATE TABLE AS SELECT

CREATE TABLE AS SELECT (CTAS) segue lo stesso comportamento di SQL Server documentato per SELECT..INTO. Tuttavia, non è possibile specificare una IDENTITY proprietà nella definizione di colonna della CREATE TABLE parte dell'istruzione . Non è inoltre possibile usare la IDENTITY funzione nella SELECT parte di CTAS. Per popolare una tabella, è necessario usare l'istruzione CREATE TABLE per definire la tabella, seguita da INSERT..SELECT per popolarla.

Inserire valori espliciti in una colonna IDENTITY

Il pool SQL dedicato supporta la sintassi SET IDENTITY_INSERT <your table> ON|OFF. È possibile usare questa sintassi per inserire in modo esplicito i valori nella IDENTITY colonna.

Molti progettisti di modelli di dati preferiscono usare valori negativi predefiniti per alcune righe nelle dimensioni. Un esempio è la riga di membro -1 o sconosciuta.

Lo script successivo illustra come aggiungere in modo esplicito questa riga usando 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;

Caricare i dati

La presenza della IDENTITY proprietà ha alcune implicazioni per il codice di caricamento dei dati. In questa sezione vengono illustrati alcuni modelli di base per il caricamento dei dati in tabelle tramite IDENTITY.

Per caricare i dati in una tabella e generare una chiave surrogata usando IDENTITY, creare la tabella e quindi usare INSERT..SELECT o INSERT..VALUES per eseguire il caricamento.

L'esempio seguente illustra il modello di base:

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

Nota

Non è possibile usare CREATE TABLE AS SELECT attualmente durante il caricamento dei dati in una tabella con una IDENTITY colonna.

Per altre informazioni sul caricamento dei dati, vedere Progettazione ELT (Extract, Load, Transform) per il pool SQL dedicato e Procedure consigliate per il caricamento.

Visualizzazioni di sistema

È possibile utilizzare la vista del catalogo sys.identity_columns per identificare una colonna con la IDENTITY proprietà .

Per comprendere meglio lo schema del database, questo esempio mostra come integrare sys.identity_columns con altre viste del catalogo di sistema:

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

Limiti

La IDENTITY proprietà non può essere usata:

  • Quando il tipo di dati della colonna non INT è o BIGINT
  • Quando la colonna è anche la chiave di distribuzione
  • Quando la tabella è una tabella esterna

Le funzioni correlate seguenti non sono supportate nel pool SQL dedicato:

Attività comuni

È possibile usare il codice di esempio seguente per eseguire attività comuni quando si usano IDENTITY colonne.

La colonna C1 è in IDENTITY tutte le attività seguenti.

Individuare il valore massimo allocato per una tabella

Usare la funzione MAX() per determinare il valore massimo allocato per una tabella distribuita:

SELECT MAX(C1)
FROM dbo.T1

Trovare il valore di seeding e incremento per la proprietà IDENTITY

È possibile usare le viste del catalogo per individuare i valori di configurazione di incremento e seeding di IDENTITY per una tabella usando la query seguente:

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

Contenuto correlato