Scope Identity and @@Identity in SQL Server

Question

Sir, I need your help in the following:- what is the difference between scope identity and @@Identity? please explain this with an example.

Sanjeeb Lenka · Answer

Hi, refer to this link http://www.c-sharpcorner.com/UploadFile/rohatash/identity-and-scope_identity-in-sql-server-2012/ http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

Sanjeeb Lenka · Answer

SCOPE_IDENTITY returns the last identity value generated by any table in current session in current scope. nothing in it, if you insert a record in a query browser it will return that last inserted identity in that session and scope.

Bineesh Viswanath · Answer

Ok, Now I got Idea of @@identity. But the idea of SCOPE_IDENTITY () is still far away. So need help in this. could you give a clear idea that when this function generate? I know that this function generate during value insertion. Could you give me your own idea about SCOPE_IDENTITY? I not need any website link for this.

Jignesh Trivedi · Answer

hi, @@IDENTITY : returns the last indentity value generated for any table in current session. SCOPE_IDENTITY returns the last identity value generated by any table in current session in current scope. It might possible both returns different value for current session if multiple table insert is occured. Please refer http://blogs.msdn.com/b/spike/archive/2009/10/20/my-take-on-identity-vs-scope-identity.aspx http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/ hope this will help you.

Scope Identity and @@Identity in SQL Server

Answers (4)