262

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?

robinCTS
  • 5,746
  • 14
  • 30
  • 37
Orson
  • 14,981
  • 11
  • 56
  • 70

7 Answers7

526
  • 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 a select...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.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • 29
    I chose this as the answer, because of the "A situation where the scope_identity() and the @@identity..." paragraph. It clarified things more. – Orson Dec 17 '09 at 10:14
  • @Guffa - "The session is the database connection". Is session maintained across connections if you are using Connection Pooling? – Dave Black Jan 11 '16 at 15:19
  • 4
    This is a role model answer. In particular, working with SQL and SQL Server can be weird, and this explains things in a very clear, layman's manner, while still being fairly informative. It doesn't sound like something being communicated between two database specialists, which a LOT of other SE answers do. – Panzercrisis Feb 13 '18 at 18:55
  • @DaveBlack from what i read: No, session is not maintained in the pool, session is unique for your script-run after connect(). When pooling ... PHP for SQL Server uses ODBC connection pooling. When a connection from the pool is used, the connection state is reset. Closing the connection returns the connection to the pool. (note: see remarks for linux/mac) https://learn.microsoft.com/en-us/sql/connect/php/connection-pooling-microsoft-drivers-for-php-for-sql-server – GDmac May 05 '20 at 21:47
  • @SebastianMeine: The probable reason that your answer was deleted is that it doesn't answer the question at all, but rather is a comment. The bug that you are talking about was fixed nine years ago, so it's unlikely that you would find a system where it's not patched. I wouldn't recommend that you shy away from `scope_identity()` just because there was once a bug related to it. – Guffa Oct 15 '21 at 16:19
  • @Guffa, if the bug was indeed fixed, I agree with you. Do you have evidence, like a KB? – Sebastian Meine Oct 15 '21 at 20:43
  • 1
    @SebastianMeine: It's in the kb article that rory.ap linked to: https://support.microsoft.com/en-us/kb/2019779 – Guffa Oct 19 '21 at 12:16
56

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 an INSERT, and the trigger executes another INSERT 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 an INSERT, 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().

Liam
  • 27,717
  • 28
  • 128
  • 190
Brannon
  • 25,687
  • 5
  • 39
  • 44
25

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.

Community
  • 1
  • 1
Hemant Sakta
  • 615
  • 7
  • 8
  • Should be the accepted answer because it actually defines, in the context of SQL, what a scope and session actually are. – Hashim Aziz Apr 11 '23 at 21:18
14

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.

robinCTS
  • 5,746
  • 14
  • 30
  • 37
devio
  • 36,858
  • 7
  • 80
  • 143
6

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.

Ali
  • 3,373
  • 5
  • 42
  • 54
Andrew
  • 26,629
  • 5
  • 63
  • 86
6

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.

Ali
  • 3,373
  • 5
  • 42
  • 54
Jonas Lincoln
  • 9,567
  • 9
  • 35
  • 49
3

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

Dmitriy Dokshin
  • 710
  • 5
  • 25