Difference and similarities between scope_identity, @@identity and ident_current

Below are the definitions of these functions:

scope_identity

1. This returns the last identity row id which is inserted to any table in the current scope ( i.e. session/ function/ triggers / procedure /batch)

2.. If no insert statement is executed having identity column in the current session prior to this statement then null value will be returned.

Ident_current

1. This will return the last identity number specific to table which can be in any query

2. This accepts table name as a parameter, so it is dependent on the table only.

@@identity

1. This will return the last identity number of the specific session of any table which is inserted in the last.

2. In case of transaction rollback/ failure , it will return the last value which was inserted before break down of the transaction .

Differences as follows :

1. Scope_identity, @@identity will return the last value inserted any table in the session( batch/procedure)

2. Ident_current will provide the identity number for the specific table which may be used in any of the session which the other 2 can’t

3. In case of rollback @@identity will not consider any change but Scope_identity, Ident_current will only give data for the last value in case of rollback it will not show the last committed value/ during the transaction it will show the last value inserted.

Similarities as follows :

All 3 functions will return the same output if only one table is there in the current session and the table is not used in any other place on the server and no failure of any bulk copy or transaction rollback.

----- Sample is given as below :

Create 2 tables as the belo[removed]

CREATE TABLE Tab_test_id1(id1 int IDENTITY)

CREATE TABLE Tab_test_id2(id2 int IDENTITY(100,1))

---- Create a trigger on Tab_test_id1 so records can be inserted into another table(Tab_test_id2 ) in other session to check all 3 functions :

CREATE TRIGGER TRigger_on_Tab1 ON Tab_test_id1

FOR INSERT

AS

BEGIN

INSERT Tab_test_id2 DEFAULT VALUES

END

----- Now insert the default values in the Table_test_id2

INSERT Tab_test_id1 DEFAULT VALUES

---- Check the result of all the functions

select scope_identity() out_scope_ident, @@identity out_@@ident,

ident_current('Tab_test_id1') ident_curr_IDENT_Tab1,ident_current('Tab_test_id2') ident_curr_IDENT_Tab2

In this as the last row inserted in current session is in table1 so the output is 1

In @@identity the last row inserted in any session is 100 so it has returned 100