Article ID: 163446
Article Last Modified on 2/22/2005
APPLIES TO
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
- Microsoft SQL Server 7.0 Standard Edition
This article was previously published under Q163446
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.
Improperly relying on this behavior may quickly cause changes in your application. For example, suppose you do an insert into a table and then check the @@IDENTITY value to do foreign key (FK) inserts. The administrator then adds an insert trigger to the table to perform an insert into the audit table. Now the @@IDENTITY value has been altered, and is no longer the proper @@IDENTITY for FK inserts.
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.
Here is an example of implementing your own counter column to maintain the highest sequence number:
/* for 6.5 or earlier versions, add columns until each row is at least half of a 2K page so each row is on a separate page */
create table IdentityTable
(ForTable sysname not null,
Value int not null)
go
create proc GetNextIdentity @ForTable sysname, @Value int OUTPUT
AS
set nocount on
begin tran
/* if this is the first value generated for this table, start with zero */
if not exists (select * from IdentityTable where ForTable = @ForTable)
insert IdentityTable (ForTable, Value) values (@ForTable, 0)
/* update must be before select to issue a lock and prevent duplicates */
update IdentityTable
set Value = Value + 1
where ForTable = @ForTable
select @Value = Value from IdentityTable
where ForTable = @ForTable
commit tran
return @value
go
-- Example execution for the Pubs Database
declare @MyIdentity int
exec @MyIdentity = GetNextIdentity @ForTable = 'authors', @Value = 0
select @MyIdentity
Be careful, because this method may cause concurrency contention issues. The guide also describes several other methods. However one of these methods, using @@DBTS, should not be used because the behavior of that feature may change in future versions.
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.
Note: The SQL Server 7.0 feature allowing multiple triggers on the same object requires that proper logic be built into all INSERT triggers bound to the given object. There is no trigger precedence ordering and as such the developer must account for the logic in any ordering.
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
Keywords: kbprb kbcode kbusage KB163446
