@@identity bring back wrong number

Question

@@identity bring back wrong number

tripower

SSCrazy

Points: 2035
More actions
December 23, 2009 at 11:45 am

#137775

I have an insert procedure with @@identity set to an output variable. The output variable is bring back a number nowhere near the max id column. The max id column is somewhere near 91 and @@identity is giving me a number like 312. It is incrementing but not incrementing the right number. What could be causing this and is there anyway to reset this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if there is a trigger on your destination table, @@identity could return the identity from that other table instead of the one you inserted; that's why you need to use the scope_identity() function isntead...it returns the identity related to the scope of your query.

if you have a very high transaction environment, @@identity could return the value of the last identity from the last table touched, instead of the one you are touching.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 2

@@identity has a lot of weaknesses which could cause this. If you're using 2005 you should probably be looking at the OUTPUT clause to get back the ID you want, since it will return from the same table on the actual record you inserted. All o fthe other alternatives have weaknesses that could cuase it to return the wrong value in a multi-user, high concurrency setup.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

With SQL2005 and higher, simply use the OUTPUT clause to be 100% sure of the value assigned to your row(s).

There are a number of articles at SSC regarding the identity property.

Use the search feature.By default you will also get a ton of forum threads, but you can filter to receive only the articles.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

roundel1900 (12/23/2009)
I have an insert procedure with @@identity set to an output variable. The output variable is bring back a number nowhere near the max id column. The max id column is somewhere near 91 and @@identity is giving me a number like 312. It is incrementing but not incrementing the right number. What could be causing this and is there anyway to reset this?

For all the reasons others have stated, never use @@IDENTITY. Always use SCOPE_IDENTITY() instead. It will return the correct value whether you have triggers on the table or not. It works in all versions of SQL Server starting with 2000.

As a side bar... the OUTPUT clause works a fine trick but might be a little on the overkill side if you're you're just inserting one row at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)