what is difference between identity and scope_identity ?

Question

difference between identity and scope_identity

Ronika Jency · Answer

Please read below link https://www.c-sharpcorner.com/UploadFile/8911c4/different-between-scope-identity-ident-current-and-identit/

satheesh d · Answer

Identity one of the property applied to the column which its value is gets auto increamented whenever a new row or record is added in that table. If we mentioned any column in the table as a identity column, then its values is auto increamented for every new records. This property can be used in create, alter table command in SQL. Identity[ (seed, increament)] here the seed vaload is loaded for the first record in th table and it is auto increamented by increament with its previous vaue. @@Identity and scope_Identity return the last value of the table but it depends on the session and scope of the query. Select @@IDENTITY - same session and Any Scope Select Scope_Identity - same session and same scope Create Table UserDetails (UserId int Identity(1,1),UserName nvarchar(50) NOT NULL ) Insert INTO UserDetails Values ( 'satheesh' ) Insert INTO UserDetails Values ( 'Arun' ) Insert INTO UserDetails Values ( 'Daril' ) Insert INTO UserDetails Values ( 'Naveen' ) Select * from UserDetails Create Proc InsertRecord (@UserName nvarchar(50)) AS BEGIN Insert into UserDetails values (@UserName); END Exec InsertRecord 'Selva' Select @@IDENTITY AS IDEN,Scope_Identity() AS SCOPE Output for @@IDENTITY and Scope_Identity()

Jignesh Kumar · Answer

Hi Salman, Both are used for same perpose but it has different scope, For Reference : https://www.aspsnippets.com/Articles/Difference-between-IDENTITY-SCOPEIDENTITY-and-IDENTCURRENT-in-SQL-Server.aspx

Rajanikant Hawaldar · Answer

Hi Salman Khan, *)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.

Gajendra Jangid · Answer

Differences Between SCOPE IDENTITY, IDENT CURRENT, and IDENTITY in SQL Server 2008. SCOPE_IDENTITY is: SCOPE_IDENTITY returns the last IDENTITY value inserted into an IDENTITY column in the same scope. SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

what is difference between identity and scope_identity ?

Answers (5)