identity returning wrong - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

identity returning wrong

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-13 : 06:40:00

I have something that calls a stored procedure that inserts a record and returns an identity
now the issue it sometimes returns the right identity and sometimes a much lower number
so i called it now and it returned
250
251
253
82
83
84

i don't know where it's getting the 83 from - any ideas?

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2011-01-13 : 06:48:54

how are you querying the values back? If you need the newly inserted identity value use SCOPE_IDENTITY(). If you are using a SELECT query to get multiple Identity values due to a batch insert, use an ORDER BY with distinct columns from the batch.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-13 : 06:51:30

i'm using select @id=@@identity

is that wrong? I'm using it right after I run the insert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-13 : 06:52:42

For absolute correctness, you should use OUTPUT operator since the discovery that even SCOPE_IDENTITY() can return the wrong value in certain circumstances.

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-13 : 06:54:07

quote:
Originally posted by esthera

i'm using select @id=@@identity

is that wrong? I'm using it right after I run the insert

Yes!

@@IDENTITY returns the latest identity value created in the database, no matter which user or which table.

N 56°04'39.26"
E 12°55'05.63"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-13 : 07:01:53

@@IDENTITY returns the latest identity value created in the database, no matter which user or which table.

I cannot believe i did not know that - I always used this

i will try scopeidentity

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-13 : 07:02:53

does scope identity have to be used with a trigger - or can i use it after inserting to get the id just inserted

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-13 : 15:15:08

quote:
Originally posted by esthera

does scope identity have to be used with a trigger - or can i use it after inserting to get the id just inserted

Use it just like you would @@IDENTITY

Here is a link to the MS Connect bug report that Peso is talking about:
http://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-01-13 : 15:58:55

And here is the Books Online topic for the three various identity functions
http://msdn.microsoft.com/en-us/library/ms187342.aspx

N 56°04'39.26"
E 12°55'05.63"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-01-14 : 03:08:17

thanks for you help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources