INTERVIEW QUESTIONS DATABASE SQL DETAILS

Question: What is the difference between @@identity and SCOPE_IDENTITY()?

Answer: This particular issue isn't so much about doing something right or wrong, it is about understanding your options so you choose the right one. Both @@IDENTITY and SCOPE_IDENTITY() return the last identity value (primary key) that was entered by your active session, but in different scenarios they can each return different values. When I say "active session" I am referring to the current activity you are engaging in. For example, if you can a stored procedure, that is what I am referring to as your active session. Each call to t a stored procedure (or user defined function, etc) is a session, unless the a stored procedure is nested in the stored procedure you are calling. In the case of a nested stored procedure or user defined method, while they are separate methods, they are part of the current session, but not part of the current scope. Your scope is limited to the method (stored procedure or user defined function) that you explicitly invoked. This is where the difference between @@IDENTITY and SCOPE_IDENTITY() comes in.

@@IDENTITY will return the last identity value entered into a table in your current session (this is limited to your session only, so you won't get identities entered by other users). While @@IDENTITY is limited to the current session, it is not limited to the current scope. In other words, 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. Now this isn't bad, as long as you ensure that things are done in the correct order. Where this can get ugly is when there is an application revision and a new trigger gets added that gets fired from your stored procedure. Your code didn't anticipate this new trigger, so you could now be getting an incorrect value back.

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.

For more information and example refer to: -
1) http://dotnetjunkies.com/Article/86F0988E-FED4-414F-BA2E-D01D953C11BE.dcik
2) http://msdn2.microsoft.com/en-us/library/aa259185(sql.80).aspx
3) http://msdn2.microsoft.com/en-us/library/aa933167(SQL.80).aspx

This particular issue isn't so much about doing something right or wrong, it is about understanding your options so you choose the right one. Both @@IDENTITY and SCOPE_IDENTITY() return the last identity value (primary key) that was entered by your active session, but in different scenarios they can each return different values. When I say "active session" I am referring to the current activity you are engaging in. For example, if you can a stored procedure, that is what I am referring to as your active session. Each call to t a stored procedure (or user defined function, etc) is a session, unless the a stored procedure is nested in the stored procedure you are calling. In the case of a nested stored procedure or user defined method, while they are separate methods, they are part of the current session, but not part of the current scope. Your scope is limited to the method (stored procedure or user defined function) that you explicitly invoked. This is where the difference between @@IDENTITY and SCOPE_IDENTITY() comes in.

@@IDENTITY will return the last identity value entered into a table in your current session (this is limited to your session only, so you won't get identities entered by other users). While @@IDENTITY is limited to the current session, it is not limited to the current scope. In other words, 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. Now this isn't bad, as long as you ensure that things are done in the correct order. Where this can get ugly is when there is an application revision and a new trigger gets added that gets fired from your stored procedure. Your code didn't anticipate this new trigger, so you could now be getting an incorrect value back.

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.

For more information and example refer to: -
1) http://dotnetjunkies.com/Article/86F0988E-FED4-414F-BA2E-D01D953C11BE.dcik
2) http://msdn2.microsoft.com/en-us/library/aa259185(sql.80).aspx
3) http://msdn2.microsoft.com/en-us/library/aa933167(SQL.80).aspx Source: CoolInterview.com

SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

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

SCOPE_IDENTITY and @@IDENTITY will return last identity values 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
Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question