SCOPE_IDENTITY vs @@IDENTITY

Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY value
that another row could have been inserted into the same table by another user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.
@.@.IDENTITY and SCOPE_IDENTITY are session specific, so you will never see
identity values generated by other concurrent users. But the difference
between the two is explained in SQL Server Books Online documentation of
SCOPE_IDENTITY
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
level of isolation than @.@.IDENTITY? That is, is it ever possible that
between
inserting a row into a table and subsequently retrieving the @.@.IDENTITY
value
that another row could have been inserted into the same table by another
user
(even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
to
me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
affected by the above scenario.
|||If I might add a note... There is a time when @.@.identity can give you the
incorrect information, and that is when there are triggers on the table...
So you are correct in your thinking about scope_identity - it is the one you
should probably be using...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"phil" <phil@.discussions.microsoft.com> wrote in message
news:ED79C78A-155C-419A-9862-CAEE2164ECCD@.microsoft.com...
> Would I be correct in understanding that SCOPE_IDENTITY( ) gives a finer
> level of isolation than @.@.IDENTITY? That is, is it ever possible that
> between
> inserting a row into a table and subsequently retrieving the @.@.IDENTITY
> value
> that another row could have been inserted into the same table by another
> user
> (even if I retrieve @.@.IDENTITY immediately after my INSERT)? It would seem
> to
> me that as SCOPE_IDENTITY( ) is scope specific that its less likely to be
> affected by the above scenario.
|||If u perform INSERTING, and u have TRIGGER that also perform some INSERT
so @.@.IDENTITY will be of table that TRIGGER inserts
Message posted via http://www.sqlmonster.com