Difference between the SCOPE_IDENTITY(),@@IDENTITY and IDENT_CURRENT in Sql Server

SCOPE_IDENTITY(),@@IDENTITY and IDENT_CURRENT('table_name') are the SQL SERVER Functions which will return the last inserted record Identity Value.

Normally in the development stage we need to get the last inserted row information using SQL Query.

Lets go in deep to understand the differences between these three functions.

@@IDENTITY :

@@IDENTITY will return the Identity value generated in a table irrespective of the scope.The main advantage lies here when we have a trigger on a table that causes anidentity to be created in another table,you will get the identity that was created last since it gets the identity regardless of the table.

Syntax :SELECT @@IDENTITY;

SCOPE_IDENTITY():

SCOPE_IDENTITY() also returns the Identity Value generated in a table with in the same scope regardless of the table that produced the value.

Syntax:SELECT SCOPE_IDENTITY();

IDENT_CURRENT('table_name'):

It will returns the last Identity value produced in a table regardless of the connection that created the value and regardless of the scope of the statement that produced the value.Simplye IDENT_CURRENT is not limited to any scope or session.

Syntax: SELECT IDENT_CURRENT('table_name') ;

Note: It always better to use the SCOPE_IDENTITY() to return the idenitty of the recently added row in our T-SQL Statement or Stored Procedure.

Popular posts from this blog

Exporting to excel from a custom class object using C#.NET

Get the Clicked Cell Value and Column Name of GridView in ASP.NET

How to apply watermark to the textbox and dropdownlist?