Scope Identity in SQL Server

Posted by Sridevi under Sql Server on 7/13/2012 | Points: 10 | Views : 2136 | Status : [Member] | Replies : 12
What is scope identity in SQL server? Could anyone explain me in detail?

Regards,
Sridevi

Sridevi


Responses

Posted by: Premalatha on: 7/13/2012 [Member] Starter | Points: 25

Up
0
Down
Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch.

Premalatha
Software Engineer

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 7/13/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

It returns identity of Last Record Inserted in the table



R D Patel

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sgtammana on: 7/14/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
you will get the Latest identity value of the current scope which you are in.
for example:
if you are executing an SP. in that sp you had inserted a record in a table A and the identity value is 10 and also at the same time from the outside another user insert the record in the same table as 11.
if used the scope identity you will get the value as 10.

Tammana

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 7/15/2012 [Member] Silver | Points: 25

Up
0
Down
1.MSDN http://msdn.microsoft.com/en-us/library/ms190315.aspx


Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Hemanthlaxmi on: 7/16/2012 [Member] Starter | Points: 25

Up
0
Down
Hi Sridevi,

read this blog clearly exaplined about the Scope Identity

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

If this helps you .
Please "Mark as Answer"

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ranjeet_8 on: 7/16/2012 [Member] [MVP] Gold | Points: 25
Posted by: Karthikeyanp365 on: 7/25/2012 [Member] Starter | Points: 25

Up
0
Down
SELECT @@IDENTITY
SELECT SCOPE_IDENTITY()

The above both sql query returns the last identity value entered into a table in your current session.

SELECT IDENT_CURRENT('tablename')
It returns the last IDENTITY value produced in a table

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bhupentiwari on: 7/25/2012 [Member] Starter | Points: 25

Up
0
Down
Hi
refer this

http://www.dotnetfunda.com/articles/article516-difference-between-scopeidentity-and-identity.aspx

Thanks n Regards
Bhupendra Tiwari

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 7/31/2012 [Member] Silver | Points: 25

Up
0
Down
Mark as answer if it helps...,That motivates...!!!

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 9/5/2012 [Member] Bronze | Points: 25

Up
0
Down
Hi,
SELECT 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.

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Shanky11 on: 9/5/2012 [Member] Starter | Points: 25

Up
0
Down
Can u give some example?

Sridevi, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response