Generare valori automatici

  • 3 minuti

Potrebbe essere necessario generare automaticamente valori sequenziali per una colonna in una tabella specifica. Transact-SQL offre due modi per eseguire questa operazione: usare la proprietà IDENTITY con una colonna specifica in una tabella o definire un oggetto SEQUENCE e usare i valori generati da tale oggetto.

La proprietà IDENTITY

Per usare la proprietà IDENTITY, definire una colonna usando un tipo di dati numerico con scala 0 (ovvero solo numeri interi) e includere la parola chiave IDENTITY. I tipi consentiti includono tutti i tipi interi e decimali in cui si assegna in modo esplicito una scala 0.

È anche possibile specificare un valore di inizializzazione facoltativo (valore iniziale) e un incremento (valore di passaggio). Se si tralascia il valore di inizializzazione e l'incremento, verranno impostati entrambi su 1.

Nota

La proprietà IDENTITY viene specificata al posto di NULL o NOT NULL nella definizione della colonna. Qualsiasi colonna con la proprietà IDENTITY non ammette in modo automatico valori Null. È possibile specificare NOT NULL solo per la documentazione autonoma, ma se si specifica la colonna come NULL (ovvero che ammette i valori Null), l'istruzione di creazione della tabella genererà un errore.

Solo una colonna in una tabella può avere la proprietà IDENTITY impostata; viene spesso usata come CHIAVE PRIMARIA o chiave alternativa.

Il codice seguente illustra la creazione della tabella Sales.Promotion usata negli esempi della sezione precedente, ma questa volta con una colonna Identity denominata PromotionID come chiave primaria:

CREATE TABLE Sales.Promotion
(
PromotionID int IDENTITY PRIMARY KEY,
PromotionName varchar(20),
StartDate datetime NOT NULL DEFAULT GETDATE(),
ProductModelID int NOT NULL REFERENCES Production.ProductModel(ProductModelID),
Discount decimal(4,2) NOT NULL,
Notes nvarchar(max) NULL
);

Nota

I dettagli completi dell'istruzione CREATE TABLE non rientrano nell'ambito di questo modulo.

Inserimento di dati in una colonna Identity

Quando la proprietà IDENTITY viene definita per una colonna, le istruzioni INSERT nella tabella non specificano in genere un valore per la colonna IDENTITY. Il motore di database genera un valore usando il successivo valore disponibile per la colonna.

Ad esempio, è possibile inserire una riga nella tabella Sales.Promotion senza specificare un valore per la colonna PromotionID:

INSERT INTO Sales.Promotion
VALUES
('Clearance Sale', '01/01/2021', 23, 0.10, '10% discount')

Si noti che anche se la clausola VALUES non include un valore per la colonna PromotionID, non è necessario specificare un elenco di colonne nella clausola INSERT; le colonne Identity sono esenti da questo requisito.

Se questa riga è la prima inserita nella tabella, il risultato è una nuova riga simile alla seguente:

PromotionID

PromotionName

StartDate

ProductModelID

Sconto

Note

1

Svendita

2021-01-01T00:00:00

23

0,1

Sconto del 10%

Quando è stata creata la tabella, per la colonna IDENTITY non sono stati impostati valori di inizializzazione o incremento, quindi la prima riga viene inserita con un valore pari a 1. Alla riga successiva da inserire verrà assegnato un valore PromotionID pari a 2 e così via.

Recupero di un valore Identity

Per restituire il valore IDENTITY assegnato più di recente all'interno della stessa sessione e dello stesso ambito, usare la funzione SCOPE_IDENTITY, come riportato di seguito:

SELECT SCOPE_IDENTITY();

La funzione SCOPE_IDENTITY restituisce il valore Identity più recente generato nell'ambito corrente per qualsiasi tabella. Se occorre il valore Identity più recente in una tabella specifica, è possibile usare la funzione IDENT_CURRENT, come illustrato di seguito:

SELECT IDENT_CURRENT('Sales.Promotion');

Sostituzione dei valori Identity

Se si desidera sostituire il valore generato automaticamente e assegnare un valore specifico alla colonna IDENTITY, è necessario abilitare prima di tutto gli inserimenti Identity usando l'istruzione SET IDENTITY INSERT table_name ON. Con questa opzione abilitata, è possibile inserire un valore esplicito per la colonna Identity, proprio come qualsiasi altra colonna. Al termine, è possibile usare l'istruzione SET IDENTITY INSERT table_name OFF per riprendere a usare i valori Identity automatici, usando il valore più recente immesso in modo esplicito come valore di inizializzazione.

SET IDENTITY_INSERT SalesLT.Promotion ON;

INSERT INTO SalesLT.Promotion (PromotionID, PromotionName, ProductModelID, Discount)
VALUES
(20, 'Another short sale',37, 0.3);

SET IDENTITY_INSERT SalesLT.Promotion OFF;

Come illustrato, la proprietà IDENTITY viene usata per generare una sequenza di valori per una colonna all'interno di una tabella. Tuttavia, la proprietà IDENTITY non è adatta per coordinare i valori tra più tabelle all'interno di un database. Si supponga, ad esempio, che l'organizzazione si differenzi tra vendite dirette e vendite ai rivenditori e desideri archiviare i dati per tali vendite in tabelle separate. Per entrambi i tipi di vendita può essere necessario un numero di fattura univoco; l'utente desidera evitare di duplicare lo stesso valore per due tipi diversi di vendita. Una soluzione per questo requisito consiste nel mantenere un pool di valori sequenziali univoci in entrambe le tabelle.

Reseeding di una colonna Identity

Occasionalmente, è necessario reimpostare o ignorare i valori di identità per la colonna. A tale scopo, verrà eseguito il "reseeding" della colonna usando la funzione DBCC CHECKIDENT. È possibile usare questa opzione per ignorare molti valori oppure reimpostare il valore di identità successivo a 1 dopo aver eliminato tutte le righe nella tabella. Per informazioni dettagliate sull'uso di DBCC CHECKIDENT, vedere la documentazione di riferimento di Transact-SQL.

SEQUENCE

In Transact-SQL è possibile usare un oggetto sequenza per definire nuovi valori sequenziali indipendentemente da una tabella specifica. Un oggetto sequenza si crea usando l'istruzione CREATE SEQUENCE; il tipo di dati (deve essere un tipo di numero intero o decimale o numerico con scala 0), il valore iniziale, un valore di incremento, un valore massimo e altre opzioni correlate alle prestazioni possono essere specificati in modo facoltativo.

CREATE SEQUENCE Sales.InvoiceNumber AS INT
START WITH 1000 INCREMENT BY 1;

Per recuperare il successivo valore disponibile da una sequenza, usare il costrutto NEXT VALUE FOR, come illustrato di seguito:

INSERT INTO Sales.ResellerInvoice
VALUES
(NEXT VALUE FOR Sales.InvoiceNumber, 2, GETDATE(), 'PO12345', 107.99);

IDENTITY o SEQUENCE

Quando si decide se usare colonne IDENTITY o un oggetto SEQUENCE per il popolamento automatico dei valori, tenere presente quanto segue:

  • Usare SEQUENCE se l'applicazione richiede la condivisione di una singola serie di numeri tra più tabelle o più colonne all'interno di una tabella.

  • SEQUENCE consente di ordinare i valori in base a un'altra colonna. Il costrutto NEXT VALUE FOR può usare la clausola OVER per specificare la colonna di ordinamento. La clausola OVER garantisce che i valori restituiti vengano generati in base all'ordine della clausola ORDER BY della clausola OVER. Questa funzionalità consente anche di generare numeri di riga per le righe restituite in un'istruzione SELECT. Nell'esempio seguente la tabella Production.Product viene ordinata in base alla colonna Name e la prima colonna restituita è un numero sequenziale.

    SELECT NEXT VALUE FOR dbo.Sequence OVER (ORDER BY Name) AS NextID,
    ProductID,
    Name
FROM Production.Product;

    Anche se l'istruzione precedente selezionava solo i valori SEQUENCE da visualizzare, i valori vengono ancora "usati" e i valori SEQUENCE visualizzati non saranno più disponibili. Se si esegue più volte l'istruzione SELECT riportata sopra, si otterranno sempre valori SEQUENCE diversi.

  • Usare SEQUENCE se l'applicazione richiede l'assegnazione di più numeri contemporaneamente. Devono essere riservati, ad esempio, cinque numeri sequenziali. La richiesta di valori di identità potrebbe comportare gap nella serie se sono stati emessi contemporaneamente numeri per altri processi. È possibile usare la procedura di sistema sp_sequence_get_range per recuperare contemporaneamente diversi numeri nella sequenza.

  • SEQUENCE consente di modificare la specifica della sequenza, ad esempio il valore di incremento.

  • I valori IDENTITY sono protetti dagli aggiornamenti. Se si tenta di aggiornare una colonna con la proprietà IDENTITY, verrà visualizzato un errore.