Monday, March 17, 2008

IDENT_CURRENT, @@IDENTITY and SCOPE_IDENTITY()

         A very common question during the interview is to provide difference between IDENT_CURRENT, @@IDENTITY and SCOPE_IDENTITY().  All are used to retrieve the value of identity column after DML statement execution. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

  • IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.
  • @@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.
  • SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

The IDENT_CURRENT function returns NULL when the function is invoked on an empty table or on a table that has no identity column.
       I found a good article and an example which explains the difference very effectively. Here is the link for that.

Happy Programming !!!

Wednesday, March 12, 2008

CLR Stored Procedure in SQL Server 2005

       As we all know SQL Server 2005 has CLR integrated with it. This means one can write a code in manage application and execute it SQL Server. I read about this since Microsoft has launched SQL Server 2005, however I have never used it practically till last week.

Here is a complete article.