what is difference between identity and scope_identity ?

Salman Khan

7y935SQL

difference between identity and scope_identity

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ronika Jency7y ago
Accepted answer
Please read below link

https://www.c-sharpcorner.com/UploadFile/8911c4/different-between-scope-identity-ident-current-and-identit/
+1
satheesh d4y ago
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()
+1
Jignesh Kumar7y ago
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

+1
Rajanikant Hawaldar7y ago
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.

+1
Gajendra Jangid7y ago
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.
+1