IDENTITY (proprietà) (Transact-SQL)

Articolo
10/31/2013

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

[!NOTA]

La proprietà IDENTITY è diversa dalla proprietà Identity di SQL-DMO, la quale espone la proprietà di identità di riga di una colonna.

Icona di collegamento a un argomento Convenzioni della sintassi Transact-SQL

Sintassi

IDENTITY [ (seed , increment) ]

Argomenti

seed
Valore utilizzato per la prima riga caricata nella tabella.
increment
Valore incrementale aggiunto al valore Identity della riga caricata in precedenza.

È 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 utilizzate per la generazione di valori di chiave. Tramite la proprietà Identity in una colonna viene garantito quanto riportato di seguito:

Ogni nuovo valore viene generato in base all'incremento e al valore di inizializzazione correnti.
Ogni nuovo valore per una transazione specifica è diverso da altre transazioni simultanee nella tabella.

Tramite la proprietà Identity in una colonna non viene garantito quanto riportato di seguito:

Univocità del valore. L'univocità deve essere applicata tramite un vincolo UNIQUE o PRIMARY KEY o l'indice UNIQUE.
Valori consecutivi in una transazione. In una transazione tramite cui vengono inserite più righe non viene garantito il recupero di valori consecutivi per le righe dal momento che si possono verificare altri inserimenti simultanei nella tabella. Se i valori devono essere consecutivi, nella transazione deve essere utilizzato un blocco esclusivo sulla tabella o il livello di isolamento SERIALIZABLE.
Valori consecutivi dopo il riavvio del server o altri errori. Tramite SQL Server è possibile memorizzare nella cache valori Identity per motivi di prestazioni e alcuni dei valori assegnati possono essere persi durante un errore del database o il riavvio del server. Questo può comportare dei gap nel valore Identity al momento dell'inserimento. Se i gap non sono accettabili, nell'applicazione deve essere utilizzato un generatore di sequenze con l'opzione NOCACHE o il proprio meccanismo per generare valori di chiave.
Riutilizzo di valori. In caso di una determinata proprietà Identity con un valore di inizializzazione/incremento specifico, i valori Identity non vengono riutilizzati dal motore. Se una particolare istruzione INSERT non riesce o se viene eseguito il rollback di quest'ultima, i valori Identity utilizzati vengono 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 utilizzare valori Identity a causa di queste restrizioni, creare una tabella separata contenente un valore corrente e gestire l'accesso all'assegnazione di numeri e tabelle 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 utilizzato. Per ulteriori informazioni, vedere Replica di colonne Identity.

È possibile creare una sola colonna Identity per tabella.

Esempi

A.Utilizzo della 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 AdventureWorks2012
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.Utilizzo della sintassi generica per individuare interruzioni nella sequenza di valori Identity

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

[!NOTA]

La prima parte del seguente script Transact-SQL è 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