Geeks With Blogs
AzamSharp Some day I will know everything. I hope that day never comes.
When trying to retrieve the identity of the last row inserted in the table always use SCOPE_IDENTITY instead of @@IDENTITY. If you are simply inserting data into the database table using a simple INSERT query than you won't be able to see the disaster behind using @@IDENTITY. But as soon as you or your fellow developer start using triggers and tries to insert data into another table depending on the insertion in the first table the whole hell will break loose. Here is a small scenario. You write a query to insert data into table1, a trigger is fired which is used to insert data into table2. Now if inside your query you retrieve @@IDENTITY you will not get the identity of the last row inserted in table1 but you will get the identity of the last row inserted in table2 since technically that was the last identity that was generated and which offcourse you were not expecting. If you want to get the identity from table1 than you will use SCOPE_IDENTITY. Simply, @@IDENTIY will return you the last ID generated in any table. If you are firing triggers on 10 tables and if you do @@IDENTITY than it will return you the identity from the table10. I will soon write a small article on this topic. Posted on Wednesday, July 13, 2005 1:03 PM | Back to top


Comments on this post: Use SCOPE_IDENTITY instead of @@IDENTITY

# re: Use SCOPE_IDENTITY instead of @@IDENTITY
Requesting Gravatar...
For those using SQL Server CE, you can only use the SCOPE_IDENTITY function when using the System.Data.SqlClient namesapce, it is not available in the System.data.SqlServerCe namespace. You have to go back to using @@IDENTITY with SQL Server CE.
Left by Liam Westley on Jul 14, 2005 12:06 AM

# re: Use SCOPE_IDENTITY instead of @@IDENTITY
Requesting Gravatar...
Basic info all found in BOL.
Left by JP on Jul 19, 2005 1:14 PM

# re: Use SCOPE_IDENTITY instead of @@IDENTITY
Requesting Gravatar...
Thanx sir
Left by AMar on Apr 25, 2006 4:25 AM

# re: Use SCOPE_IDENTITY instead of @@IDENTITY
Requesting Gravatar...
Really useful for me
Left by msveera on Jul 20, 2006 11:15 PM

Your comment:
 (will show your gravatar)


Copyright © Mohammad Azam | Powered by: GeeksWithBlogs.net