2

sorry if this question is dumb but I cant seem to find the answer. Just starting out with SQL. I was reading the answers for Get the last inserted row ID (with SQL statement), and it seems that SCOPE_IDENTITY is "the latest inserted value". Is this operation locked right after insert because if not, then another newly inserted row may become something that got inserted right after the insertion I intended to get the ID of.

Thanks for the help!

Community
  • 1
  • 1
yifanwu
  • 1,595
  • 3
  • 14
  • 25
  • 4
    It gives you the **last inserted ID** for your **scope** - e.g. if another user/connection is inserting something, you won't get that ID - you'll get the **last ID YOU inserted**. – marc_s Oct 04 '13 at 10:15
  • I see, thank you! Sorry I apparently didn't even know what to search for! That use of SCOPE make a lot of sense now! – yifanwu Oct 04 '13 at 11:17
  • Make sure you have the latest patches for your version of SQL server. Some have rather irritating bugs with SCOPE_IDENTITY(). – StingyJack Oct 04 '13 at 12:24

2 Answers2

5

To get latest inserted row information through SQL query you have three options:

@@IDENTITY: it returns the last identity value generated for any table in the current session, across all scopes

SCOPE_IDENTITY: it returns the last identity value generated for any table in the current session and the current scope.

IDENT_CURRENT: it returns the last identity value generated for a specific table in any session and any scope

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jagz W
  • 855
  • 3
  • 12
  • 28
1

There is a further alternative to using Scope_Identity that you should also study - have a read about using the OUTPUT clause: http://msdn.microsoft.com/en-us/library/ms177564(v=sql.100).aspx/html

Rikalous
  • 4,514
  • 1
  • 40
  • 52