0

I have studied the following line on msdn.

"@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session.

However, SCOPE_IDENTITY returns the value only within the current scope.

@@IDENTITY is not limited to a specific scope"

so my question is "what is current session and current scope" in above lines.

Thanks in advance.

Community
  • 1
  • 1
Code Rider
  • 2,003
  • 5
  • 32
  • 50
  • 2
    Related: http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row – Oded Dec 12 '12 at 11:40
  • 4
    The examples on the MSDN page seem clear: http://msdn.microsoft.com/en-us/library/ms190315.aspx - what do you not understand? – Oded Dec 12 '12 at 11:44
  • @Oded didn't reach at the pages you linked in your comments. by the way, thanks. – Code Rider Dec 12 '12 at 11:48

2 Answers2

1

If you want to return the value you just inserted (to use to insert to child tables),then generally you need to use scope _identity(). It is specific to the scope of the statement your connection ran. It wil nto give you anyone else's identity value.

@@identiy, is also specific to your scope, but it also includes trigger valuies in the scope and thus if the table has a trigger that also inserts to an identity, that is the identity returned. as such, it means that @@identity should not be used to return teh value you inserted as it will start returnign the worng value as soon as anyone adds a trigger.

Then there is ident_current. This is the most dangerous of all because it retuns the last identitiy onteh table no matter which coneection put it in. So if you use that to get an identity value, you need to be awre that it is not necessarlity related to the record your connection put in and using this to get the identity to use to insert to child tables is a guarantee of data integrity problems.

Newer version of SQL server have an OUTPUT clause, this is far superior to using any of the three items above as you can return a set of identities and the values of other fields as well.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • The concept of current_scope is cleared. But still i don't know about the current_session. What exactly it is? – Code Rider Apr 16 '13 at 15:42
  • Each time an application connects to SQL Server it creates a new session. So if 100 users are each indivudally logged in doing work, each is in its own session. This is why ident_current is so dangerous, it is not limited to the work you specifically did but can cross sesssions and bring back an identity that someone else inserted. – HLGEM Apr 16 '13 at 15:51
-2

@@IDENTITY having scope limited to current session But SCOPE_IDENTITY() have scope across all the sessions for the server

padmanvg
  • 7
  • 3
  • -1 `SCOPE_IDENTITY` is more limited scope than `@@IDENTITY` not less limited. – Martin Smith Apr 16 '13 at 15:02
  • @MartinSmith what is exactly current session and current scope?? I have stuck in this concept. – Code Rider Apr 16 '13 at 15:17
  • @CodeRider - The difference between the two is that `@@IDENTITY` will also include `IDENTITY` values generated in child scopes such as stored procedure calls or code in triggers that is fired in response to an action at the current scope. `SCOPE_IDENTITY` is only affected by statements at the same level. – Martin Smith Apr 16 '13 at 15:22
  • thanks @Martin. The concept of stored procedure and triggers relates to current scope. But what is "current session"?? – Code Rider Apr 16 '13 at 15:26