SCOPE_IDENTITY, IDENT_CURRENT,@@IDENTITY functions return last-generated identity values.
IDENT_CURRENT()
Returns the last identity value generated for a given table in any session and any scope is called SCOPE_IDENTITY. When table have no identity column it returns null.
SCOPE_IDENTITY()
Returns the last identity value generated for any table in the current session and the current scope is called SCOPE_IDENTITY
@@IDENTITY()
Returns the last identity value generated for any table in the current session, across all scopes is called @@IDENTITY. @@IDENTITY is limited to cureent session, but not limited to current scope.
EXAMPLE
DECLARE @TABLE TABLE
(
ID INT IDENTITY ,
FULLNAME NVARCHAR(MAX) ,
GENDER CHAR(1) DEFAULT NULL ,
CREATEDDATE DATE ,
CREATEDTIME TIME
)
INSERT INTO @TABLE
VALUES ( 'AAMIR HASAN', 'M', GETDATE(), GETDATE() )
INSERT INTO @TABLE
VALUES ( 'AHMED HASAN', 'M', GETDATE(), GETDATE() )
INSERT INTO @TABLE
VALUES ( 'HINA ', 'F', GETDATE(), GETDATE() )
INSERT INTO @TABLE
VALUES ( 'SANA', 'M', GETDATE(), GETDATE() )
INSERT INTO @TABLE
VALUES ( 'AAMIR HASAN', 'M', GETDATE(), GETDATE() )
SELECT *
FROM @TABLE
SELECT @@IDENTITY ,
SCOPE_IDENTITY() ,
SCOPE_IDENTITY()
In Above example, this will always give you a correct result as shown in the Above Figure. @@IDENTITY can be wrong, if table has a Trigger,becasue @@IDENTITY return last value of identity column in a current session. As above i have defined the defination SCOPE_IDENTITY returns last identity value in a current session and current scope. And IDENT_CURRENT return a last identity value in any scope and any session. IDENT_CURRENT can return a last identity inserted value of any other session. So it is aproved that in different case we use SCOPE_IDENTITY, @@IDENTITY and IDENT_CURRENT.However, normally you would use the scope_identity() function.
Download
USER_INFORMATION_TABLE_SQL.sql (645.00 bytes)