| @@IDENTITY | SCOPE_IDENTITY () | IDENT_CURRENT() |
| Provides latest identity value of a table inserted on a particular session. | Provides latest identity value of a table inserted on a particular session. | Provides latest identity value of a table, which is provided as input to this function. |
| Latest identity value could be fetched from table used on trigger also. | Latest identity value could be fetched only from table used in the current session and not from trigger. | Provides latest identity value of a table irrespective to session/connection |
Example:-
IF OBJECT_ID('identity_table') is not null drop table identity_table
IF OBJECT_ID('trigger_table') is not null drop table trigger_table
-- Creating new table with identity column
CREATE TABLE identity_table (id int identity(1,1),id_value varchar(20) DEFAULT('test'))
go-- Creating new table with identity column to be used on trigger.
CREATE TABLE trigger_table (id int identity(5,1),id_value varchar(20))
go-- Creating after trigger to check @@IDENTITY value
CREATE TRIGGER trg_after_identity_table
ON identity_table
FOR INSERT
AS
BEGIN
INSERT INTO trigger_table
SELECT inserted.id_value FROM inserted
END
go
INSERT INTO identity_table(id_value)
VALUES (default),(default),(default),(default),(default),
(default),(default),(default),(default),(default)
SELECT @@IDENTITY AS '@@IDENTITY',SCOPE_IDENTITY () AS 'SCOPE_IDENTITY',
IDENT_CURRENT ('identity_table') AS 'IDENT_CURRENT1',IDENT_CURRENT ('trigger_table') AS 'IDENT_CURRENT2'
@@identity fetches latest identity value from trigger_table table (used in trigger), where as scope_identity() fetches latest identity value from identity_table table (used in procedure/batch statement).
