Using Identity Values

In previous chapters, I introduced the function of identity values in a table. They are used to generate surrogate keys—unique identifiers based on sequential numbers.

A Standard Problem and Solution

Identity values are similar to the autonumber data type in Access tables, but there is one difference that generates many questions in Usenet newsgroups among developers who are used to Access/ADO behavior. When you insert a record into a table, the value of the autonumber field is immediately available in Access. Unfortunately, due to the nature of the client/server environment, this is not the case in SQL Server.

The best way to insert a record into a SQL Server table and obtain an identity key is to use a stored procedure. The following stored procedure, ap_Inventory_Insert, presents such a solution. A new record is inserted into a table and the key is read using the Scope_Indentity() function.

      Create Procedure dbo.ap_Inventory_Insert      -- insert inventory record and return Id           @intEquipmentId int,           @intLocationId int,           @inyStatusId tinyint,           @intLeaseId int,           @intLeaseScheduleId int,           @intOwnerId int,           @mnsRent smallmoney,           @mnsLease smallmoney,           @mnsCost smallmoney,           @inyAcquisitionTypeId int,           @intInventoryId int OUTPUT      As      Set Nocount On      Insert into dbo.Inventory (Equipmentld, LocationId, StatusId,                      LeaseId, LeaseScheduleId, OwnerId,                      Rent, Lease, Cost,                      AcquisitionTypeId)      Values (        @intEquipmentld, @intLocationId, @inyStatusId,                      @intLeaseId,  @intLeaseScheduleld, @intOwnerId,                      @mnsRent, @mnsLease, @mnsCost,                      @inyAcquisitionTypeId)      Select @intInventoryId = Scope_Indentity()      Return 

Identity Values and Triggers

The @@identity function/global variable is a traditional alternative to the Scope_Identity() function. However, there is one significant difference, @@identity returns the last identity value set in the current connection, while Scope_Identity() returns the last identity value that was set in the current scope (for example, in a stored procedure). If the table in which the record was inserted (in this case, Inventory) has a trigger that inserts a record into some other table with an identity key, the value of that key will be recorded in @@ identity.

You can reproduce this behavior using the following script. It must be executed against the tempdb database.

      Create Table a (a_id int identity(1,1),                      a_desc varchar(20),                      b_desc varchar(20))      Go      Create Table b (b_id int identity(1,1),                       b_desc varchar(20))      Go      Create Trigger tr_a_I      On dbo.a      After Insert     -- For Insert      As      If @@Rowcount = 0           Return      Insert Into b (b_desc)           Select b_desc from inserted      Go 

Now execute this batch:

      Insert into b (b_desc)      Values ('1')      Insert into a (a_desc, b_desc)      Values ('aaa', 'bbb')      Select @@identity [IdentityValue] 

SQL Server returns the following result:

      (1 row(s) affected)      (1 row(s) affected)      IdentityValue      -------------------------------------------------      2      (1 row(s) affected) 

The first Insert statement adds the first record to table b. The second Insert statement adds the first record in a table. Because there is a trigger on the table, another record (the second one) will be inserted into table b, and the value of @@ identity will be set to 2. If there was no trigger, the Select statement would return a value of 1.

This is not a problem if you use the Scope_Identity() function. It returns the last identity value generated in the current scope of the current process. The following example adds this function to the code executed earlier against the tempdb database:

      Insert into b (b_desc)      Values ('1')      Insert into a (a_desc, b_desc)      Values ('aaa', 'bbb')      Select @@identity [@@Identity], SCOPE_IDENTITY() [SCOPE_IDENTITY()] 

When you execute it, notice that the Scope_Identity() function returns the proper result:

      (1 row(s) affected)      (1 row(s) affected)       @@Identity                  SCOPE_IDENTITY()       --------------------------- -----------------------------       4                           2      (1 row(s) affected) 

Tip 

You should always use ScopeJdentityO instead of the @@identity function.