Apart from performance, they all have rather different meanings.
SCOPE_IDENTITY()
will give you the last identity value inserted into any table directly within the current scope (scope = batch, stored procedure, etc. but not within, say, a trigger that was fired by the current scope).
IDENT_CURRENT()
will give you the last identity value inserted into a specific table from any scope, by any user.
@@IDENTITY
gives you the last identity value generated by the most recent INSERT statement for the current connection, regardless of table or scope. (Side note: Access uses this function, and thus has some issues with triggers that insert values into tables with identity columns.)
Using MAX()
or TOP 1
can give you entirely wrong results if the table has a negative identity step, or has had rows inserted with SET IDENTITY_INSERT
in play. Here's a script demonstrating all of these:
CREATE TABLE ReverseIdent (
id int IDENTITY(9000,-1) NOT NULL PRIMARY KEY CLUSTERED,
data char(4)
)
INSERT INTO ReverseIdent (data)
VALUES ('a'), ('b'), ('c')
SELECT * FROM ReverseIdent
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9000
SET IDENTITY_INSERT ReverseIdent ON
INSERT INTO ReverseIdent (id, data)
VALUES (9005, 'd')
SET IDENTITY_INSERT ReverseIdent OFF
SELECT IDENT_CURRENT('ReverseIdent') --8998
SELECT MAX(id) FROM ReverseIdent --9005
Summary: stick with SCOPE_IDENTITY()
, IDENT_CURRENT()
, or @@IDENTITY
, and make sure you're using the one that returns what you actually need.