PRB: Guarantee @@IDENTITY Value on a Per Table Basis

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

SQL Server documents and treats the @@IDENTITY variable as a global variable. The following is an excerpt from the SQL Server Books Online:

   To get the last identity value, use the @@IDENTITY global variable. This
   variable is accurate after an insert into a table with an identity
   column; however, this value is reset after an insert into a table
   without an identity column occurs.

This problem may compound itself when you start adding nested triggers or stored procedures.

WORKAROUND

There is no way to implement a solid workaround without making code changes. However, you can employ one of following two strategies:

Method One

Use the method documented in the SQL Server 4.21a Transact-SQL Guide to implement a counter or identity column.

To use this method of "Using a Sequence Number Table," see page 165 of the SQL Server 4.21a Transact-SQL Guide. The following is an excerpt from that guide:

   Here you use a separate table to maintain the highest sequence number.
   This approach ensures that sequence numbers are assigned in sequential
   order, without any holds, by effectively single-threading inserts.

Method Two

Design your own series of procedures and triggers to maintain the @@IDENTITY variable. The basic design is to create a temporary table before you begin working in the database. Then, for every table that contains an identity column, add an INSERT trigger that places the @@IDENTITY value in your temporary table. This value is then retrieved later by selecting back the proper value. If you are using transactions, be aware of their ramifications on the temporary table inserts.

The following script shows how to retain the last @@IDENTITY value assigned to a specific table:

   select @@VERSION
   go

   use pubs
   go

   set nocount on
   go


   print ''
   print 'Create the sample tables...'
   print ''
   go


   drop table tblAudit
   go

   create table tblAudit
   (
      iID   int   identity(2500,1),
      strData     varchar(10)
   )
   go

   drop table tblIdentity
   go

   create table tblIdentity
   (
      iID   int   identity(1,1),
      strData     varchar(10)
   )
   go


   print ''
   print 'Create the sample procedures and triggers...'
   print ''
   go

   create table #tblIdentity
   (
   iID   int,
   strTable   varchar(30)
   )
   go

   create trigger trgIdentity on tblIdentity for INSERT
   as
      insert into #tblIdentity values (@@IDENTITY, 'tblIdentity')
      insert into tblAudit values ('Audit entry')

   go

   create trigger trgAudit on tblAudit for INSERT
   as
      insert into #tblIdentity values (@@IDENTITY, 'tblAudit')
   go

   drop procedure sp_Insert
   go

   create procedure sp_Insert
   as
      insert into tblIdentity values('Test')

      print ' '
      print 'Simple reliance on the @@IDENTITY after the execution would
      incorrectly yield'
      print 'FK references of tblIdentity would be incorrect'
      print ' '
      select '@@IDENTITY' = @@IDENTITY

   go


   drop procedure sp_GetIdentity
   go

   create procedure sp_GetIdentity @strTable varchar(30)
   as
   declare   @iIdentity int

   select @iIdentity = iID
      from #tblIdentity
      where strTable = @strTable

   return @iIdentity
   go

   drop table #tblIdentity
   go


   print ''
   print 'Show the process in action'
   print ''

   create table #tblIdentity
   (
   iID   int,
   strTable   varchar(30)
   )
   go


   exec sp_Insert
   go


   print ' '
   print 'After execution you can get a specific table value...'
   print ' '
   go

   declare @iIdentity int

   exec @iIdentity = sp_GetIdentity 'tblIdentity'
   select 'tblIdenity' = @iIdentity

   exec @iIdentity = sp_GetIdentity 'tblAudit'
   select 'tblAudit' = @iIdentity

   drop table #tblIdentity
   go