Problem
I’m aware that Scope Identity(), Identity(), @@Identity, and Ident Current() all return the identity column’s value, but I’m curious about the differences.
Part of the debate I’m having is what they mean by scope when it comes to these services.
I’d also like a short example of how to use them in various scenarios?
Asked by Orson
Solution #1
The database connection is represented by the session. The current query or stored procedure defines the scope.
If you have a trigger on the table, the scope identity() and @@identity functions will be different. The scope identity() function will return the identity created by the query, however the @@identity function will return the identity formed by the trigger if you have a query that inserts a record, prompting the trigger to insert another record someplace.
Normally, the scope identity() function would be used.
Answered by Guffa
Solution #2
Good question.
See http://msdn.microsoft.com/en-us/library/ms187342.aspx for further information.
To summarize, always utilize SCOPE IDENTITY when inserting rows and need to know the value of the identity column for the row you just put ().
Answered by Brannon
Solution #3
It will be quite simple to grasp these techniques if you understand the distinction between scope and session.
This discrepancy is described in a really beautiful blog post by Adam Anderson:
As a result, the following are the differences between the three identity retrieval methods:
Answered by Hemant Sakta
Solution #4
In contrast to the global scope of @@IDENTITY, scope refers to the code context in which the INSERT statement SCOPE IDENTITY() is executed.
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.
Answered by devio
Solution #5
To better understand the issue with @@Identity, consider the following:
The id from the insert in the trigger (a log id or whatever) will be returned by @@Identity, whereas the id from the insert in the original table will be returned by scope identity().
Scope identity() and @@identity will both return the same value if you don’t have any triggers. If you have triggers, consider what kind of value you’d like.
Answered by Jonas Lincoln
Post is based on https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide