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’)