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.



No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts