Functions to get identity value

There are 3 functions to get current identity value

@@identity

Returns last identity value generated by last statement  in current session for all scopes

If statement fires trigger and identity value generated by that trigger than @@identity

will give value generated by that trigger

Even if transaction rolled back identity value is not reverted , so @@identity value also will not reverted.

IDENT_CURRENT (<table name>)

Gives last identity value generated for specific table

Its independent of se

SCOPE_IDENTITY()

Returns identity value generated in current session  current scope.

So if there is identity value generated by trigger it's not reflected here as it's not in current scope.

CREATE TABLE ident_test( id int IDENTITY( 1 , 1 ) ,

                         data varchar( 10)

                              );

CREATE TABLE trigger_test( id int IDENTITY( 5 , 10 )

                         );

CREATE TRIGGER ins_ident_test ON ident_test

    FOR INSERT

AS

BEGIN

    INSERT INTO INTO trigger_test

    DEFAULT VALUES;

END;

INSERT INTO INTO ident_test

VALUES( 'a');

INSERT INTO INTO ident_test

VALUES( 'b');

INSERT INTO INTO ident_test

VALUES( 'c');

SELECT *

  FROM ident_test;

SELECT *

  FROM trigger_test;

SELECT @@IDENTITY;

Return last generated identity value for statement, its from trigger

SELECT IDENT_CURRENT( 'ident_test') ,

       IDENT_CURRENT( 'trigger_test');

Returns last generated identity value for table

SELECT SCOPE_IDENTITY();

Returns last generated identity value for current scope.