Difference between @@Identity, Scope_Identity (), IDENT

Difference between @@Identity, Scope_Identity (), IDENT_Current(‘tablename’)

Problem: Which one of the following should be use to get the last Identity of the recently added row in T-SQL or Stored Procedure: @@Identity, Scope_Identity (), IDENT_Current(‘tablename’)
Solution: So many times we required to get the last inserted rows identity to be insert them in child table for reference in stored procedure. All the above statements gives us the last identity inserted but in different perspective.
So if you want to get the last identity in T-SQL or Stored Procedure always use Scope_Identity () to avoid problems in Multiuser (concurrent) scenarios. Let me explain why?
Lets see what does each means?
@@Identity :
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
Scope_Identity () :
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
IDENT_Current(‘tablename’):
It 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.IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.

Comments

Custom Effective Paging With Sorting across all pages GridView in Asp.Net

Hi, I need to comeback to blogging anyhow, since it was too long break. Hence yesterday i decided to start blogging again, and thought of this topic. When Microsoft shipped GridView control with Asp.net, it came with lot of new functionality, but it also lacking of existing functionality like 'VirtualCount' property which was available with DataGrid but not with GridView. Since because of this it is very much difficult to implement Custom Paging with Grid View. Now what is custom paging and default paging? To answer this question i will take an example. I need to display records on a page in tabular format, with paging and sorting. Well if you go with default paging and default sorting your development work is easy. But with it there is a drawback. - It is grid view control which decides what records to display and sorted by which column. So for that it need complete set of all records. Suppose if you want to display only 25 records, a...

Ways to Execute SSIS Package in SQL2K5

Problem: One of the Junior SQL Server Developers in my company approached me yesterday with a dilemma. He was developing an SSIS Package which imports data from a comma separated text file and he wanted to know the different ways in which one can execute an SSIS Package in SQL Server 2005 and higher versions. At first I started to tell him, but figured it would be smarter to document the options and share the information. Solution: In SQL Server 2005 and higher versions there are different ways in which one can execute an SSIS package. Let us go through each option one by one. Execute SSIS Package Using SQL Server Business Intelligence Development Studio (BIDS) During the development phase of the project developers can test the SSIS package execution by running the package from Business Intelligence Development Studio a.k.a. BIDS. 1. In Solution Explorer, right click the SSIS project folder that contains the package which you want to run and then click properties as shown in the snipp...

Puzzle

Print from 1 to 100 sequentially with out using cursor, while or any other iterative loop. Ans: We can achieve this by using Common Table Expression (CTE). CTE, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. Moreover, CTEs can be recursively defined, allowing a recursive entity to be enumerated without the need for recursive stored procedures. We can achieve the above using recursive CTE: Please execute the below statements: WITH Series (Number) AS ( SELECT 1 UNION ALL SELECT Number +1 FROM Series WHERE Number 100 ) Select * from Series And see the result

Tar's Arena

Search This Blog