Difference between Scope_Identity(), @@Identity, and Ident_Current(),Identity().

 

# SCOPE_IDENTITY(), IDENT_CURRENT, and @@IDENTITY

SCOPE_IDENTITY(), IDENT_CURRENT, and @@IDENTITY are similar functions because they return last values that are inserted into identity columns.

• SCOPE_IDENTITY(),  and  @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

• IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the last value generated for a specific table in any session and any scope. 

1. SELECT SCOPE_IDENTITY() AS SCOPEIDENTITYOUTPUT 
2. SELECT IDENT_CURRENT( 'table_or_view' ) AS IDENTOUTPUT 
3. Select @@IDENTITY AS IDENTITYOUTPUT  

Note:- The session is the database connection. The scope is the current query or the current stored procedure.

Identity(1,1):- Identity column of a table is a column whose value increases automatically. The value in an identity column is created by the server. A user generally cannot insert a value into an identity column. Identity column can be used to uniquely identify the rows in the table. 

Syntax:- IDENTITY [( seed, increment)] 

Seed: Starting value of a column, default value is 1. 

Increment: Incremental value that is added to the identity value of the previous row that was loaded. The default value 1.