Welcome to weblogs.com.pk Sign in | Join | Help

Knowing Identity Value

If you are involved in database application development, especially in Data Access Layer development, it is often required to know the value of the last inserted Identity Value (SQL Server equivalent of AutoNumber in Access)

 

Once I happen to see a very funny code doing this. It was an ASP3 application, coded in VBScript using ADO. The person was using the following approach.

 

Insert into TableWithIdentityField (Field1, Field2) values (Field1, Field2);

Select Max(IdentityField) from TableWithIdentityField;

 

Though it was working fine during his development, but as soon as he deployed that on server, the troubles started coming. Problem is obvious; this approach can work in single user mode. The concurrency issues are not handled properly.

 

SQL server provide methods to get the values of inserted identity values, these are:

 

  • Using @@Identity system variable
  • Using Scope_Identity() system stored procedure
  • Using Ident_Current() system stored procedure

 

The most common approach in TSql is to use @@Identity variable that returns the last inserted identity value. An example is shown below:

 

--@UserID, @Password, @FirstName, @LastName are parameters

--[Users] and [UserDetails] tables have UserIndex, Identity column

--xact_abort flag is already set to on, so no need of explicit rollbacks

--Processing

begin transaction

   insert into users (userid, [password]) values (@UserID, @Password)

   if @@rowcount<=0

   begin

       raiserror(‘Unable to create user’, 16, 1);

       return -1;

   end

   select @UserIndex=@@Identity;

   insert into userdetails (userindex, firstname, lastname)

       values (@UserIndex, @FirstName, @LastName);

   if @@rowcount<=0

   begin

       raiserror(‘Unable to create user details’, 16, 1);

       return -2;

   end

commit transaction

 

Approach of using @@Identity variable, has a little problem. Suppose we have some trigger on [Users] table, that insert the data into some other table, may be for the audit purposes and this another table also has identity field, then @@Identity will have the last inserted identity value, that is, the value that’s being inserted in the audit table, and not in the [Users] table.

 

To solve this problem we should always use Scope_Identity() system stored procedure. The Scope_Identity() returns the last identity value in the same scope and a scope is a module, stored procedure, trigger, function or a batch. Using this we will always have the identity value that we are looking for. Lets update our example accordingly.

 

--@UserID, @Password, @FirstName, @LastName are parameters

--[Users] and [UserDetails] tables have UserIndex, Identity column

--xact_abort flag is already set to on, so no need of explicit rollbacks

--Processing

begin transaction

   insert into users (userid, [password]) values (@UserID, @Password)

   if @@rowcount<=0

   begin

       raiserror(‘Unable to create user’, 16, 1);

       return -1;

   end

   select @UserIndex=Scope_Identity();

   insert into userdetails (userindex, firstname, lastname)

       values (@UserIndex, @FirstName, @LastName);

   if @@rowcount<=0

   begin

       raiserror(‘Unable to create user details’, 16, 1);

       return -2;

   end

commit transaction

 

Using Ident_Current() is straight forward, you need to specify the table name as parameter to this procedure, it returns the identity value that’s being inserted in the specified table. So if we need to know whats the value of identity value in the audit table in the procedure above, we may call Ident_Current(‘AuditTable’)

Published Saturday, April 03, 2004 12:48 PM by khurram
Filed under:

Comments

# re: Knowing Identity Value

Monday, April 05, 2004 10:19 AM by
Its a nice and helpful article for intermediate users , and atleast i like it much.
Thanks for helping people around, since sharing is caring.
With regards
M.Furqan Hameedi

# re: Knowing Identity Value

Monday, April 05, 2004 10:29 AM by Abdul Ghaffar
Excellent

# re: Knowing Identity Value

Monday, April 05, 2004 12:49 PM by Imran Ghani.T
Really helpful.
New Comments to this post are disabled