What is the difference between Scope_Identity(), Identity(), @@Identity, and Ident_Current()?

sql sql-server identity
Tebo picture Tebo · Dec 17, 2009 · Viewed 141.5k times · Source

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?

Answer

Guffa picture Guffa · Dec 17, 2009
  • 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.

Related questions

Handling identity columns in an "Insert Into TABLE Values()" statement?

In SQL Server 2000 or above is there anyway to handle an auto generated primary key (identity) column when using a statement like the following? Insert Into TableName Values(?, ?, ?) My goal is to NOT use the column names at all.

Read More
SQL Server 2012 column identity increment jumping from 6 to 1000+ on 7th entry

I have a strange scenario in which the auto identity int column in my SQL Server 2012 database is not incrementing properly. Say I have a table which uses an int auto identity as a primary key it is sporadically skipping …

Read More
@@IDENTITY, SCOPE_IDENTITY(), OUTPUT and other methods of retrieving last identity

I have seen various methods used when retrieving the value of a primary key identity field after insert. declare @t table ( id int identity primary key, somecol datetime default getdate() ) insert into @t default values select SCOPE_IDENTITY() --returns 1 select @@…

Read More