What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?
SqlSql ServerIdentitySql Problem Overview
I know Scope_Identity()
, Identity()
, @@Identity
, and Ident_Current()
all get the value of the identity column, but I would love to know the difference.
Part of the controversy I'm having is what do they mean by scope as applied to these functions above?
I would also love a simple example of different scenarios of using them?
Sql Solutions
Solution 1 - Sql
- The
@@identity
function returns the last identity created in the same session. - The
scope_identity()
function returns the last identity created in the same session and the same scope. - The
ident_current(name)
returns the last identity created for a specific table or view in any session. - The
identity()
function is not used to get an identity, it's used to create an identity in aselect...into
query.
The session is the database connection. The scope is the current query or the current stored procedure.
A situation where the scope_identity()
and the @@identity
functions differ, is if you have a trigger on the table. If you have a query that inserts a record, causing the trigger to insert another record somewhere, the scope_identity()
function will return the identity created by the query, while the @@identity
function will return the identity created by the trigger.
So, normally you would use the scope_identity()
function.
Solution 2 - Sql
Good question.
-
@@IDENTITY
: returns the last identity value generated on your SQL connection (SPID). Most of the time it will be what you want, but sometimes it isn't (like when a trigger is fired in response to anINSERT
, and the trigger executes anotherINSERT
statement). -
SCOPE_IDENTITY()
: returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc). -
IDENT_CURRENT()
: returns the last identity value for a specific table. Don't use this to get the identity value from anINSERT
, it's subject to race conditions (i.e. multiple connections inserting rows on the same table). -
IDENTITY()
: used when declaring a column in a table as an identity column.
For more reference, see: http://msdn.microsoft.com/en-us/library/ms187342.aspx.
To summarize: if you are inserting rows, and you want to know the value of the identity column for the row you just inserted, always use SCOPE_IDENTITY()
.
Solution 3 - Sql
If you understand the difference between scope and session then it will be very easy to understand these methods.
A very nice blog post by Adam Anderson describes this difference:
>Session means the current connection that's executing the command. > >Scope means the immediate context of a command. Every stored procedure call executes in its own scope, and nested calls execute in a nested scope within the calling procedure's scope. Likewise, a SQL command executed from an application or SSMS executes in its own scope, and if that command fires any triggers, each trigger executes within its own nested scope.
Thus the differences between the three identity retrieval methods are as follows:
>@@identity
returns the last identity value generated in this session but any scope.
>
>scope_identity()
returns the last identity value generated in this session and this scope.
>
>ident_current()
returns the last identity value generated for a particular table in any session and any scope.
Solution 4 - Sql
Scope means the code context that performs the INSERT
statement SCOPE_IDENTITY()
, as opposed to the global scope of @@IDENTITY
.
CREATE TABLE Foo(
ID INT IDENTITY(1,1),
Dummy VARCHAR(100)
)
CREATE TABLE FooLog(
ID INT IDENTITY(2,2),
LogText VARCHAR(100)
)
go
CREATE TRIGGER InsertFoo ON Foo AFTER INSERT AS
BEGIN
INSERT INTO FooLog (LogText) VALUES ('inserted Foo')
INSERT INTO FooLog (LogText) SELECT Dummy FROM inserted
END
INSERT INTO Foo (Dummy) VALUES ('x')
SELECT SCOPE_IDENTITY(), @@IDENTITY
Gives different results.
Solution 5 - Sql
Scope Identity
: Identity of last record added within the stored procedure being executed.
@@Identity
: Identity of last record added within the query batch, or as a result of the query e.g. a procedure that performs an insert, the then fires a trigger that then inserts a record will return the identity of the inserted record from the trigger.
IdentCurrent
: The last identity allocated for the table.
Solution 6 - Sql
To clarify the problem with @@Identity
:
For instance, if you insert a table and that table has triggers doing inserts, @@Identity
will return the id from the insert in the trigger (a log_id
or something), while scope_identity()
will return the id from the insert in the original table.
So if you don't have any triggers, scope_identity()
and @@identity
will return the same value. If you have triggers, you need to think about what value you'd like.
Solution 7 - Sql
Here is another good explanation from the book:
> As for the difference between SCOPE_IDENTITY and @@IDENTITY, suppose that you have a stored procedure P1 with three statements:
-
An INSERT that generates a new identity value
-
A call to a stored procedure P2 that also has an INSERT statement that generates a new identity value
-
A statement that queries the functions SCOPE_IDENTITY and @@IDENTITY The SCOPE_IDENTITY function will return the value generated by P1 (same session and scope). The @@IDENTITY function will return the value generated by P2 (same session irrespective of scope).