0

We have a dozen clients which use an Assembly for inserting data into my SQL Server 2012 in form of simple SQL statements.

There are two tables (e.g. [User] and [Location]) with an identity column and a foreign key.

The execution of the statement is always the same: create user, save the new id as foreign key for creating the location.

In pseudo code:

//open DB Connection
Connection.Open();

//Insert user
"INSERT INTO User (Name, Birthdate) VALUES ("Smith", "1.1.1919");
 SELECT SCOPE_IDENTITY();" //save the new ID in var "newID"

//Execute Statement
ExecuteQuery();

//Insert Location
"INSERT INTO Location(Country, City, User_ID) VALUES ("Germany", "Cologne", newID)"

//Execute Statement
ExecuteQuery();

//close Connection
Connection.Close();

No magic till this point...but if I run this code at the same time on multiple clients or in parallel threads is there a possibility that SCOPE_IDENTITY() retrieves the new created ID of an User created by another client/thread?

Especially is there time between the insert of the user and Scope_Identity() for another thread to insert a new user which Scope_Identity reads?

Is maybe the OUTPUT clause an alternative?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Anton Kruk
  • 31
  • 6
  • http://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide – user2864740 Feb 17 '15 at 20:34
  • 6
    `SCOPE_IDENTITY` is the identity inside the scope. So concurrent connexion, having distinct scopes, won't overlap. https://msdn.microsoft.com/fr-fr/library/ms190315.aspx – rducom Feb 17 '15 at 20:35
  • So if every connection has its own scope on the db, whats the difference between a scope and a session? – Anton Kruk Feb 17 '15 at 20:49

1 Answers1

2

To clarify things

A session corresponds to the current connexion you have to the database (Ado.Net / EF / SSMS etc.). An application might have multiple sessions to a database.

A scope is the context of execution of a SQL command. Imagine you call some T-SQL, which calls a stored procedure, which executes some Trigger. There will be a scope for your T-SQL, then another nested scope for the code inside the stored procedure, then another scope for the code inside the trigger. So when your use SCOPE_IDENTITY, you are retrieving the last inserted PK Id inside the scope where you are.

Inherently, different sessions means different scopes.

At the opposite, @@IDENTITY returns the last inserted ID inside the session. It's not "scope-aware". If you insert in a table, and a trigger do something behind the scene, you have chances to get the ID inserted by the trigger.

Note that if you rollback a transaction where a PK Id have been generated, the PK counter don't go back to the previous value, the row is not committed because of the rollback, but a little hole will exists your table PK continuity ...

rducom
  • 7,072
  • 1
  • 25
  • 39
  • Thanks for the explanation! Third possibilty is `IDENT_CURRENT` which will return the value of a specific table on any session and any scope - not really the right thing for my application. – Anton Kruk Feb 18 '15 at 10:19
  • 1
    A little "so evident" thing I forget to say : a scope (a stored procedure for ex) is not shared among different sessions, neither among different calls. If you call 16 times the same SP in parallel inside the same session, it will create different 16 scopes – rducom Feb 18 '15 at 10:27