Ok Hal, I see what you mean. I think my confusion was that I was making the assumption that others do such insertion tasks similar to the way I do them, which is one of two ways:

Open Connection
Insert Row
...
Request @@IDENTITY
Close Connection

    OR

Open Connection
Insert Row; Request @@IDENTITY;
Close Connection


I suppose your scenario is possible if you perform one action, then close the connection, then open again (getting another connection from the pool) and attempt to get the identity value.

Peter you ment
Open Connection
Insert Row;
Close Connection
.......
//here maybe another user inserts another row and @@IDENTITY will get a wrong answer
Open Connection
Request @@IDENTITY;
Close Connection

--------------------------------------------
Mehdi.:)

No, I meant what I posted. What I posted is the way I assumed other people do their database calls when they add records. If you use the techniques that I posted, you *shouldn't* encounter the problem we are discussing. Only when you close the connection then reopen it should you ever encounter this problem.

Keep in mind- the OLD way that @IDENTITY worked, it returned the last row - on any connection.. I got into the habbit of generating my own keys then- and that's just the way I am used to. I know MSFT "fixed" the problem.. I just don't use it.


Hal Levy
Web Developer, PDI Inc.

NOT a Wiley/Wrox Employee

Hi,

For AutoIncrement IDENTITY, IDENTITY SEED is a very good option.

Thanks,

Lalit
Life Means More... ;)

If u r sure that ur Primary key is Numeric/Number.. type then Identity is the best option. However u will face some hidden issues with Identity colum in future and solutions ar there for those as well. I would be better to discuss with us later if u face prob and can't solve. U should study a little about Identity colum related and consideration. U will enjoy that.

If ur PK colum is not a number type data container, I suggest u use some user defined function to generate and use stored procedure if its of ur interest.

Good luk.


B. Anant