SQL Server: "INSERT INTO..." e lettura della colonna IDENTITY

Se ritieni utile questo articolo, considera la possibilità di effettuare una donazione (il cui importo è a tua completa discrezione) tramite PayPal. Grazie.

Una delle richieste più frequenti legate all'utilizzo di database è quella di inserire un nuovo record in una tabella e conoscere il valore del campo autoincrementante che il database ha assegnato al nuovo record.
La soluzione più banale sarebbe quella di effettuare, subito dopo l'istruzione "INSERT INTO..." una lettura che restituisca il valore massimo del campo ID; purtroppo questo sistema non è atomico, ovvero non è possibile avere la certezza che il valore letto corrisponda al record da noi inserito nel caso di operazioni concorrenti o, comunque, molto frequenti. Infatti, tra la nostra scrittura e la nostra lettura, potrebbe essere stata effettuata un'altra scrittura; in questo caso il valore letto non sarebbe "il nostro", bensì quello dell'ultima operazione... e addio integrità dei dati!
Utilizzando SQL Server è però possibile richiamare in sequenza più istruzione T-SQL (separandole con ";") ed ottenere direttamente dall'engine del database il valore del campo identity mediante la funzione SCOPE_IDENTITY(), subito dopo aver effettuato l'inserimento del nuovo record (o meglio: all'interno dello stesso ambito, quindi in modo "isolato").
Vediamo come fare:

SET NOCOUNT ON;
INSERT INTO Shippers (CompanyName, Phone) VALUES ('Speedy Express', '(503) 555-9831');
SELECT SCOPE_IDENTITY() AS NewShipperID;

Analizzando il codice d'esempio possiamo distinguere tre diverse istruzioni:

  1. SET NOCOUNT ON; - Impedisce la restituzione come parte dei risultati del messaggio che indica il numero di righe modificate da un'istruzione Transact-SQL. Quando l'opzione SET NOCOUNT è impostata su ON, il conteggio (che indica il numero di righe modificate da un'istruzione Transact-SQL) non viene restituito. Il conteggio viene restituito quando SET NOCOUNT è impostata su OFF. L'opzione SET NOCOUNT ON elimina l'invio di messaggi DONE_IN_PROC al client per ogni istruzione di una stored procedure. Quando si eseguono le utilità disponibili in Microsoft SQL Server per l'esecuzione di query, viene impedita la visualizzazione del messaggio "Righe interessate: nn" al termine di istruzioni Transact-SQL, quali SELECT, INSERT, UPDATE e DELETE.

  2. INSERT INTO ... - Rappresenta il normale statement T-SQL per l'inserimento di un record in una tabella. Nell'esempio si è fatto riferimento alla tabella "Shippers" del noto database "Northwind".

  3. SELECT SCOPE_IDENTITY() AS ... - La funzione "SCOPE_IDENTITY" restituisce l'ultimo valore IDENTITY inserito in una colonna IDENTITY nello stesso ambito. Un ambito è un modulo, ovvero una stored procedure, un trigger, una funzione o un batch. Due istruzioni pertanto appartengono allo stesso ambito se sono incluse nella stessa stored procedure, funzione o batch (come nel nostro esempio).

Esempio di utilizzo

Il codice seguente mostra un esempio di utilizzo pratico all'interno di una pagina ASP (utilizzando come linguaggio VBScript):

<%
Dim dsn, cn, rs
dsn = "Provider=sqloledb;Data Source=localhost;Initial Catalog=Northwind;User Id=sa;Password=;"
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open dsn
Set rs = cn.Execute("SET NOCOUNT ON; INSERT INTO Shippers (CompanyName, Phone) VALUES ('Speedy Express', '(503) 555-9831'); SELECT SCOPE_IDENTITY() AS NewShipperID;")
Response.Write "In nuovo ID inserito nella tabella Shippers è " & rs("NewShipperID")
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
%>

Osservazioni

SQL Server mette a disposizioni diverse funzioni, simili tra loro, che restituiscono i valori inseriti nelle colonne IDENTITY: SCOPE_IDENTITY, IDENT_CURRENT e @@IDENTITY

Dalla documentazione si evidenziano però delle importanti differenze:

  • Per la funzione IDENT_CURRENT non esiste alcuna restrizione di ambito o di sessione. La funzione è limitata tuttavia a una tabella specifica. La funzione IDENT_CURRENT restituisce il valore Identity generato per una tabella specifica in qualsiasi sessione e in qualsiasi ambito.

  • Le funzioni SCOPE_IDENTITY e @@IDENTITY restituiscono l'ultimo valore Identity generato in una tabella durante la sessione corrente. SCOPE_IDENTITY tuttavia restituisce il valore solo all'interno dell'ambito corrente, mentre @@IDENTITY non è limitata a un ambito specifico.

  • Si supponga, ad esempio, che siano disponibili le due tabelle T1 e T2 e che in T1 sia definito un trigger INSERT. Quando si inserisce una riga in T1, il trigger viene attivato e viene inserita una riga in T2. Questo esempio illustra due ambiti, ovvero l'inserimento nella tabella T1 e l'inserimento nella tabella T2 come risultato del trigger.

  • Se è disponibile una colonna Identity sia in T1 che in T2, quando si esegue un'istruzione INSERT nella tabella T1 le funzioni @@IDENTITY e SCOPE_IDENTITY restituiscono valori diversi.

  • @@IDENTITY restituisce l'ultimo valore della colonna IDENTITY inserito in qualsiasi ambito della sessione corrente, ovvero il valore inserito nella tabella T2.

  • SCOPE_IDENTITY() restituisce invece il valore IDENTITY inserito nella tabella T1, ovvero il valore inserito durante l'esecuzione dell'ultima istruzione INSERT nello stesso ambito. La funzione SCOPE_IDENTITY() restituisce NULL se viene richiamata prima che nell'ambito specifico venga eseguita un'istruzione INSERT in una colonna Identity.