What is the difference between Scope Identity Identity Identity and Ident Current

Question

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to identity