@@IDENTITY versus IDENT_CURRENT() 4

[bperry]

I've been doing a bit of testing with @@identity values, found out some stuff I didn't know that you might find interesting.

---------------
@@IDENTITY
---------------
@@identity returns the most recently generated Identity value, from any table, from YOUR connection. Imagine that we have two tables, both with an Identity column, and we do this:

Insert Into Table1 (EmpName, Title) VALUES('Gil','Mgr')
Insert Into Table2 (Product,Category) VALUES('Milk','Dairy')

In this case, 'Select @@Identity' will return to us the new Identity value from Table2 only, not Table1.

If we needed to know both new Identity values, then we could do something like this:

Insert Into Table1 (EmpName, Title) VALUES('Gil','Mgr')
set @myIden1 = @@Identity
Insert Into Table2 (Product,Category) VALUES('Milk','Dairy')
set @myIden2 = @@Identity
Select @myIden1, @myIden2


Let's say 'Select @@Identity' returns 149 to us. Question: If other users Insert more records into Table2 (generating new Identity values in that table) what will happen? Answer: If we do 'Select @@Identity' again, it will still return 149 to us, because @@Identity returns the most recent Identity value from our connection only, ignoring any other users also inserting new records.

---------------------------------
IDENT_CURRENT('tablename')
---------------------------------
This is a new function available in SQL 2000 (sorry, not version 7.0). You give it a table name, and it returns the last generated Identity value across ALL connections for that specific table (which might be a useful thing to know)

If we do this:
Insert Into Table1 (EmpName, Title) VALUES('Gil','Mgr')

and then do these immediately:
Select @@IDENTITY
Select IDENT_CURRENT('Table1')

then from both commands we will get the Identity value for the new record that we just inserted. But if we wait a few minutes and then again do:
Select @@IDENTITY
Select IDENT_CURRENT('Table1')

then @@IDENTITY will still show the Identity value that we created (since it pertains only to our connection.) But we might get a different value for IDENT_CURRENT: if someone else has inserted a new record into Table1 since we did, then we will now see the value of THEIR new Identity value.

-----------
Ain't that interestin'.

bperry

 

[Yaniv72]

Hi,
You can also use the SCOPE_IDENTITY( ).
Please check it in the BOL.

Yaniv

 

[tlbroadbent]

bperry,

Excellent post. Thanks.

Yaniv,

Thanks for your suggestion. I didn't know about scope_identity. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

@@IDENTITY and SCOPE_IDENTITY() are similar but not exactly the same. They both apply only to your connection, and don't bear on any other users at all.

@@IDENTITY returns the most recent Identity value for your session, but across all scopes.
SCOPE_IDENTITY() returns the most recent Identity, but only for the current scope.

Here's an example:
Run a stored procedure that inserts on a table, and on that table there is an Insert trigger that then does another Insert into another table.
SCOPE_IDENTITY returns the Identity value from the first table, while @@IDENTITY returns the Identity from the second table inserted by the trigger.

It's a pretty subtle difference. Unless you're doing something like I've described, I think they both will return the same value almost all the time.