Q:

Retrieve the PK using @@identity

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")

A:

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.

A:

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()

