Difference between @@IDENTITY, SCOPE_IDENTITY () and IDENT_CURRENT
@@IDENTITY
It will return last or newly inserted record id(primary key value) of table in current session but it’s not limited to current scope. After inserting a row if any trigger or functions runs & they inserted a new row in table then it will return that latest inserted record id by trigger or function.
Note: Use this property whenever we don’t have any other functions or triggers.
Syntax:
SELECT @@IDENTITY
Declare @UserId int =0 INSERT INTO [dbo].[Users] ( [UserName] , [FirstName] , [LastName] , [Email] ) VALUES ( 'shivam' , 'shivam' , 'srivastava' , 'foo@bar.com' ) --Any external function or procedure may not call after insert. SET @UserId = @@IDENTITY()
SCOPE_IDENTITY()
It will return a newly inserted record identity (Primary Key value) of the table in the current session and it’s limited to the current scope. It means it will return the identity value of current inserted row. Any external function & procedure will not affect it.
Note:I recommend this property to get the identity value of current inserted row. Since no external function & procedure will not affect it.
Syntax:
SELECT SCOPE_IDENTITY()
Declare @UserId int =0 INSERT INTO [dbo].[Users] ( [UserName] , [FirstName] , [LastName] , [Email] ) VALUES ( 'shivam' , 'shivam' , 'srivastava' , 'foo@bar.com' ) --Any external function or procedure will not effect it. SET @UserId = SCOPE_IDENTITY()
IDENT_CURRENT
It will return the last record identity(Primary key value) of the table. It is not limited to, any session or scope.
Syntax:
SELECT IDENT_CURRENT('table-name')
0 comments :
Post a Comment