This page has been translated using an automatic translation service and the translation may not be very accurate.

SQL Serveur: "INSERT INTO..." and reading of column IDENTITY (English)

If you use this code and feel it is a useful tool, consider making a donation (through PayPal) to help support the project. You can donate as little or as much as you wish, any amount is greatly appreciated!

Italian page For the original Italian page, please click on the link: http://guru4.net/articoli/sql-server-identity/default.aspx

One of the more frequent demands tied to uses of database is that one to insert a new record in a table and to know the value of the autoincrementante field that the database has assigned to the new record.
The more banal solution would be that one than to carry out, endured after instruction "INSERT INTO..." a reading that gives back the maximum value of field ID; unfortunately this system is not atomic, that is it is not possible to have the certainty that the read value corresponds to the record from we inserted in the case of concurrent operations or, however, much frequent. In fact, between our writing and our reading, it could have been carried out an other writing; in this case the read value would not be "ours", but that one of the last operation... and goodbye integrity of the data!
Using SQL Serveur it is but possible to recall in sequence more instruction T-SQL (separating them with ";") and to obtain directly from the engine of database the value of the field identity by means of the function SCOPE_IDENTITY(), endured after to have better carried out the insertion of the new record (or: to the inside of the same within, therefore in "isolated" way).
We see like making:

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

Analyzing the example code we can distinguish three various instructions:

  1. SET NOCOUNT ON; - the restitution Prevents as it leave of turns out you of the message that indicates the number of lines modified from a Transact-SQL instruction. When the option SET NOCOUNT is set up on ON, the conteggio (that it indicates the number of lines modified from a Transact-SQL instruction) does not come given back. The conteggio it comes given back when SET NOCOUNT is set up on OFF. The option SET NOCOUNT ON eliminates the shipment of messages DONE_IN_PROC to client for the every instruction of one stored procedures. When the usefullnesses available in Microsoft SQL Serveur for the execution of query are executed, it comes prevented to the visualization of the message "interested Lines: nn "to the term of Transact-SQL instructions, which SELECT, INSERT, UPDATE and DELETE.

  2. INSERT INTO... - table Represents normal school statement T-SQL for the insertion of a record in one. In the example reference has been made the table "Shippers" of famous database "the Northwind".

  3. SELECT SCOPE_IDENTITY() AS... - function "SCOPE_IDENTITY" gives back to last value IDENTITY inserted in one column IDENTITY in the same within. A within is a module, that is one stored procedures, a trigger, one function or a batch. Two instructions therefore belong to the same within if they are included in same stored the procedures, function or batch (like in our example).

Example of I use

The following code extension an example of I use practical to the inside of one page ASP (using like VBScript language):

<%
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
%>

Observations

SQL Serveur puts to various dispositions functions, similar between they, that they give back the values inserted in columns IDENTITY: SCOPE_IDENTITY, IDENT_CURRENT and @@IDENTITY

From the documentation differences are evidenced but of the important:

  • For function IDENT_CURRENT some restriction of within or session does not exist. The function is limited however to one specific table. Function IDENT_CURRENT gives back to the Identity value generated for one specific table in whichever session and whichever within.

  • Functions SCOPE_IDENTITY and @@IDENTITY give back to the last Identity value generated in one table during the running session. SCOPE_IDENTITY however only gives back the value to the inside of the running within, while @@IDENTITY is not limited to a specific within.

  • One supposes, as an example, that the two tables T1 and T2 are available and that in T1 a INSERT is defined trigger. When a line in T1 becomes part, the trigger it comes activated and it comes inserted one line in T2. This example illustrates two ambles to you, that is the insertion in table T1 and the insertion in the T2 table like result of the trigger.

  • If a Identity column is available it is in T1 that in T2, when an instruction INSERT in table T1 is executed functions @@IDENTITY and SCOPE_IDENTITY give back various values.

  • @@IDENTITY gives back the last value of column IDENTITY inserted in whichever within of the running session, that is the value inserted in the T2 table.

  • SCOPE_IDENTITY() gives back instead value IDENTITY inserted in table T1, that is the value inserted during the execution of last instruction INSERT in the same within. Function SCOPE_IDENTITY() gives back NULL if it comes recalled before that in the specific within Identity column comes executed an instruction INSERT in one.