SQL Identities

I always get confused, is it scope_identity, @@ident, or what to get the primary key of a row I just inserted?

The SQL Authority Blog does a good job of sorting things out.

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

The short answer is that you want to use SCOPE_IDENTITY()

And if you are stuck with developing with Access, here is your answer-

http://databases.aspfaq.com/general/how-do-i-get-the-identity/autonumber-value-for-the-row-i-inserted.html

Another technique is to generate a unique key on the server and then insert that as your primary key into the database. Since you insert it with the other info you usually know what it is. Usually developers use GUID's for this, which you can create both in classic ASP (check out my classic ASP post) and natively in .NET. There is a performance hit using GUID's though, but you can minimize that hit by using COMB GUID's. I've got some code for generating those somewhere, but I'm not going to hunt for it tonight. But here is a discussion thread with some code samples-

http://www.informit.com/discussion/index.aspx?postid=a8275a70-0698-46f0-8c8f-bf687464628c

NEWSEQUENTIALID is also available, but that is created on the database side, so the purpose is kind of lost, so I'd stick with COMB GUID's.

I used to get pretty religious about this type of stuff (hated GUID's), but ultimately most of us aren't building the next Amazon, Google, MySpace, etc..., so getting anal about primary keys to get that last ounce of efficiency isn't worth the time unless you approach that scale.

It seems like things are moving several steps away from the database in abstraction levels anyway (with O/R mappers and technology like LINQ). We have so much computing horsepower now that bloat seems to be the way of the future...