Condividi tramite

CREATE TABLE (Transact-SQL) IDENTITY (proprietà)

  • Articolo

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure database SQL di Azure Synapse Analytics in Microsoft Fabric

Crea una colonna Identity in una tabella. Questa proprietà viene usata con le istruzioni Transact-SQL CREATE TABLE e ALTER TABLE.

Nota

La proprietà IDENTITY è diversa dalla proprietà SQL-DMO Identity che espone la proprietà identity di riga di una colonna.

Convenzioni relative alla sintassi Transact-SQL

Sintassi

IDENTITY [ (seed , increment) ]

Questa sintassi non è supportata da pool SQL serverless in Azure Synapse Analytics.

Argomenti

seed

Valore utilizzato per la prima riga caricata nella tabella.

increment

Valore incrementale aggiunto al valore Identity della riga precedente caricata.

Nota

I valori di Azure Synapse Analytics per l'identità non sono incrementali a causa dell'architettura distribuita del data warehouse. Per altre informazioni, vedere Uso di IDENTITY per creare chiavi surrogate in un pool Synapse SQL.

È necessario specificare sia il valore di inizializzazione che l'incremento oppure nessuno dei due valori. In questo secondo caso, il valore predefinito è (1,1).

Osservazioni:

Le colonne Identity possono essere usate per la generazione di valori di chiave. La proprietà Identity in una colonna garantisce le condizioni seguenti:

  • Ogni nuovo valore viene generato in base al valore di inizializzazione e all'incremento correnti.

  • Ogni nuovo valore per una transazione specifica è diverso da altre transazioni simultanee nella tabella.

La proprietà Identity in una colonna non garantisce le condizioni seguenti:

  • Univocità del valore: l'univocità deve essere applicata usando un vincolo o UNIQUE UNIQUE un PRIMARY KEY indice.

    Nota

    Azure Synapse Analytics non supporta PRIMARY KEY né vincoli o UNIQUE UNIQUE indici. Per altre informazioni, vedere Uso di IDENTITY per creare chiavi surrogate in un pool Synapse SQL.

  • Valori consecutivi all'interno di una transazione : non è garantito che una transazione che inserisca più righe ottenga valori consecutivi per le righe perché potrebbero verificarsi altri inserimenti simultanei nella tabella. Se i valori devono essere consecutivi, la transazione deve usare un blocco esclusivo nella tabella o usare il SERIALIZABLE livello di isolamento.

  • Valori consecutivi dopo il riavvio del server o altri errori. SQL Server può memorizzare nella cache valori Identity per motivi di prestazioni e alcuni valori assegnati possono andare persi in caso di errore del database o riavvio del server. Questo può comportare dei gap nel valore Identity al momento dell'inserimento. Se le lacune non sono accettabili, l'applicazione deve usare il proprio meccanismo per generare valori di chiave. L'uso di un generatore di sequenze con l'opzione NOCACHE può limitare le lacune alle transazioni mai sottoposte a commit.

  • Riutilizzo dei valori : per una determinata proprietà Identity con valore di inizializzazione/incremento specifico, i valori Identity non vengono riutilizzati dal motore. Se un'istruzione insert specifica ha esito negativo o se viene eseguito il rollback dell'istruzione insert, i valori Identity utilizzati andranno persi e non verranno generati di nuovo. Questa condizione può comportare dei gap quando vengono generati i successivi valori Identity.

Queste restrizioni fanno parte della progettazione per migliorare le prestazioni e perché sono accettabili in molte situazioni comuni. Se non è possibile usare valori Identity a causa di queste restrizioni, creare una tabella separata contenente un valore corrente e gestire l'accesso alla tabella e all'assegnazione di numeri con l'applicazione.

Se una tabella che include una colonna Identity viene pubblicata per la replica, la colonna Identity deve essere gestita in modo adeguato per il tipo di replica usato. Per altre informazioni, vedere Replicare colonne Identity.

Ogni tabella può includere una sola colonna Identity.

Nelle tabelle ottimizzate per la memoria, il valore di inizializzazione e l'incremento devono essere impostati su 1, 1. L'impostazione del valore di inizializzazione o dell'incremento su un valore diverso da 1 genera l'errore seguente: The use of seed and increment values other than 1 is not supported with memory optimized tables.

Una volta impostata la proprietà Identity in una colonna, non può essere rimossa. Il tipo di dati può essere modificato purché il nuovo tipo di dati sia compatibile con la proprietà Identity.

Esempi

R. Utilizzare la proprietà IDENTITY con CREATE TABLE

Nell'esempio seguente viene creata una nuova tabella tramite la proprietà IDENTITY per un numero di identificazione a incremento automatico.

USE AdventureWorks2022;
GO
IF OBJECT_ID('dbo.new_employees', 'U') IS NOT NULL
    DROP TABLE new_employees;
GO

CREATE TABLE new_employees (
    id_num INT IDENTITY(1, 1),
    fname VARCHAR(20),
    minit CHAR(1),
    lname VARCHAR(30)
);

INSERT new_employees (fname, minit, lname)
VALUES ('Karin', 'F', 'Josephs');

INSERT new_employees (fname, minit, lname)
VALUES ('Pirkko', 'O', 'Koskitalo');

B. Usare la sintassi generica per trovare lacune nei valori Identity

Nell'esempio seguente viene illustrato l'uso di una sintassi generica per individuare le interruzioni nella sequenza di valori Identity create in seguito alla rimozione di dati.

Nota

La prima parte dello script Transact-SQL seguente è riportata solo a scopo illustrativo. È possibile eseguire lo script Transact-SQL a partire dal commento -- Create the img table.

-- Here is the generic syntax for finding identity value gaps in data.
-- The illustrative example starts here.
SET IDENTITY_INSERT tablename ON;

DECLARE @minidentval column_type;
DECLARE @maxidentval column_type;
DECLARE @nextidentval column_type;

SELECT @minidentval = MIN($IDENTITY),
    @maxidentval = MAX($IDENTITY)
FROM tablename

IF @minidentval = IDENT_SEED('tablename')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('tablename')
    FROM tablename t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('tablename')
            AND @maxidentval
        AND NOT EXISTS (
            SELECT *
            FROM tablename t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('tablename')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('tablename');

SET IDENTITY_INSERT tablename OFF;

-- Here is an example to find gaps in the actual data.
-- The table is called img and has two columns: the first column
-- called id_num, which is an increasing identification number, and the
-- second column called company_name.
-- This is the end of the illustration example.
-- Create the img table.
-- If the img table already exists, drop it.
-- Create the img table.
IF OBJECT_ID('dbo.img', 'U') IS NOT NULL
    DROP TABLE img;
GO

CREATE TABLE img (
    id_num INT IDENTITY(1, 1),
    company_name SYSNAME
);

INSERT img (company_name)
VALUES ('New Moon Books');

INSERT img (company_name)
VALUES ('Lucerne Publishing');

-- SET IDENTITY_INSERT ON and use in img table.
SET IDENTITY_INSERT img ON;

DECLARE @minidentval SMALLINT;
DECLARE @nextidentval SMALLINT;

SELECT @minidentval = MIN($IDENTITY)
FROM img

IF @minidentval = IDENT_SEED('img')
    SELECT @nextidentval = MIN($IDENTITY) + IDENT_INCR('img')
    FROM img t1
    WHERE $IDENTITY BETWEEN IDENT_SEED('img')
            AND 32766
        AND NOT EXISTS (
            SELECT *
            FROM img t2
            WHERE t2.$IDENTITY = t1.$IDENTITY + IDENT_INCR('img')
            )
ELSE
    SELECT @nextidentval = IDENT_SEED('img');

SET IDENTITY_INSERT img OFF;

Contenuto correlato