Retrieve the PK using @@identity

Question

Hi,

I'm building a website using ASP.NET and SQL Server, and I use

SELECT PK FROM Table WHERE PK = @@identity

My question is which is better and more reliable to retrieve the last inserted PK for multiuser website, using @@identity or using this:

SELECT MAX(PK) FROM Table WHERE PK = Session ("UserID")

Answer 1

I'm not sure exactly what you want to achieve, but the recommended way to retrieve the primary key value of the last statement on a connection is to use SCOPE_IDENTITY()

@@Identity is particularly risky where you are using triggers, since it returns the last generated identity value, including those generated by triggers flowing on from a statement.

MSDN has the following to say:

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

You should certainly use SCOPE_IDENTITY() in favour of the MAX(PK) approach - any number of possible future changes could invalidate this method.

Answer 2

Depends on what you're trying to accomplish. If you want to return the just-generated ID to the ASP.NET code (a typical scenario), then @@identity is your friend. In a high-concurrency situation, mak(PK) is not even guaranteed to be the PK you're after.

Answer 3

As mentioned by @David Hall the @@IDENTITY keyword returns the most recently created identity for your current connection, not always the identity for the recently added record in your query and may return an incorrect value. Using MAX(PK) there is a higher chance for an incorrect value and I'd strongly recommend against using it. To avoid the any race conditions I'd suggest that you use SCOPE_IDENTITY() to return the identity of the recently added record in your INSERT SQL Statement or Stored Procedure.

Answer 4

For SQL Server 2005 and above...

You can do the INSERT and SELECT in one call using the OUTPUT clause...

INSERT MyTable (col1, col2, ..., coln)
OUTPUT INSERTED.keycol, INSERTED.col1, INSERTED.col2, ..., INSERTED.coln
VALUES (val1, val2, ..., valn)

Otherwise, you only use SCOPE_IDENTITY()