Get PK using id @@

Question

I am building a website using ASP.NET and SQL Server and I am using

SELECT PK FROM Table WHERE PK = @@identity 

My question is, is it better and more reliable to retrieve the last inserted PK for a multi-user 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 get the primary key value of the last statement in the connection is to use SCOPE_IDENTITY ()

@@ Identity is especially dangerous when you use triggers, as it returns the last generated identification value, including those generated by triggers that flow from the statement.

MSDN has the following:

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 inside the current scope; @@ IDENTITY is not limited to a specific area.

You should definitely use SCOPE_IDENTITY () in favor of the MAX (PK) approach - any number of possible future changes may invalidate this method.

Answer 2

For SQL Server 2005 and higher ...

You can make 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 ()

Answer 3

As @David Hall already mentioned, the @@IDENTITY keyword returns the most recently created identity for your current connection, and not always the identifier of the recently added record in your request and may return an incorrect value. Using MAX(PK) , there is a higher probability of an incorrect value, and I highly recommend not using it. To avoid any race conditions, I suggest you use SCOPE_IDENTITY() to return the identity of a newly added record to your INSERT SQL query or stored procedure.

Answer 4

Depending on what you are trying to accomplish. If you want to return only the generated identifier to ASP.NET code (a typical scenario), then @@ identity is your friend. In a situation with a high level of concurrency mak (PK), it is not even guaranteed that you are after it.