What is the best way to get the id of the previously inserted row? For example, if User A causes an connection execution that runs an INSERT statement, then after that runs a SELECT @@IDENTITY to get the new row's id, then does a select on said row, it should work fine. But what happens if after you execute the first INSERT statement, User B comes in and does another INSERT on another table before User A's page reaches the SELECT @@IDENTITY? User A will get the wrong row, correct?
What are the chances of this happening? Is there any way to avoid it?
actually that won't happen because @@IDENTITY is per connection. User A could insert a row at 10:00 AM and user B could then insert hundreds of rows at 10:02 AM and user A could A could then call @@IDENTITY at 10:04 AM and still get the value he would be expecting.