Seems SCOPE_IDENTITY() returns @@identity+1 in SQL 2005

Seems SCOPE_IDENTITY() returns @@identity+1 in SQL 2005

• Dirk Teufel

  I hope I am not telling you to suck eggs here, but nore that SCOPE_IDENTITY() returns the last identity field created in the current scope, not the last identity field returned period. Other than that, I am not sure why you are getting that. Try comparing SCOPE_IDENTITY() with the value of @@IDENTITY. If SCOPE_IDENTITY() is one more than @@IDENTITY, then this is your problem.

  HTH

  
  

• doctus

  Silly me. :p

  Sorry I have posted the wrong function. I meant [IDENT_CURRENT].

  I was hit by its value returned after we upgraded to SQL 2005. Here is the codes I have used on our production SQL server 2000 for 3 years.

  Insert Members(DomainID, Email, Password, Created)

  values(@DomainID, '', IDENT_CURRENT('Members')+1, getdate())

  select @MemberID = SCOPE_IDENTITY()

  Usually, the values in @MemberID and PASSWORD field are the same, but now

  PASSWORD = @MemberID +1 in SQL 2005.

  Bill

  
  
  

• dzCepheus

  IDENT_CURRENT is not session specific. So you should avoid this in cases where multiple inserts can happen at the same time. You will get incorrect results. Use SCOPE_IDENTITY or @@IDENTITY.
  
  

• RoyAF

  Thanks Umachandar,

  You are correct, I have tested on 3 three different servers with different versions.

  My problem maybe caused by upgrading SQL server to 2005 as stated in BOL.

  "Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented. "

  Thanks again

  Bill

  
  
  

• qx

  There is no behavior change for IDENT_CURRENT between SQL2000 and SQL2005. The script below will produce identical output:

  create table #ident( i int not null identity )
  select ident_current('#ident')
  insert into #ident default values
  select ident_current('#ident')
  insert into #ident default values
  select ident_current('#ident')
  drop table #ident

  As I said, ident_current is not session specific so if there are concurrent inserts then you can get incorrect results depending on the order of the operations. I still don't get why you are using the current identity value of the table in another column. You can use SCOPE_IDENTITY if there are triggers on the table. The safest is to set the column to NULL in the insert and then update the column again with the value from SCOPE_IDENTITY.

  
  
  

• Nikko

  Thanks for your response.

  You can only get the identity key from SCOPE_IDENTITY or @@IDENTITY after a record is inserted into a table, but I need this key before I insert a record, and @@IDENTITY could return you a wrong ID if there is trigger in current table.

  IDENT_CURRENT can return a last identity ID for a specific table.

  My question is:

  if IDENT_CURRENT returns NEXT last identity ID in SQL 2005, while it returns last identity ID in SQL 2000

  Thanks

  Bill
  

  
  
  
• Seems SCOPE_IDENTITY() returns @@identity+1 in SQL 2005