What is the scope of SET IDENTITY_INSERT xyz ON?

Question

What is the scope of SET IDENTITY_INSERT xyz ON?

If I use this in one stored procedure for a certain table, what happens if a different user running a different procedure, inserts into that certain table at the same time?

Also, what happens if different users/procedures try to set SET IDENTITY_INSERT xyz ON for different tables at the same time?

Answer 1

It's a session option a table can only have the option on for any one table at a time but multiple different sessions can have it on for the same table (not sure that would ever be a good idea though!)

When a child batch finishes (that sets this option) it looks like it automatically gets unset for the connection.

CREATE TABLE Tst
(C INT IDENTITY(1,1))

EXEC('SET IDENTITY_INSERT Tst ON')
INSERT INTO Tst(C) VALUES (1) /*Fails - Complains IDENTITY_INSERT is off*/

SET IDENTITY_INSERT Tst ON
EXEC('INSERT INTO Tst(C) VALUES (1)') /*Succeeds this way round*/
SET IDENTITY_INSERT Tst OFF


SET IDENTITY_INSERT Tst ON
EXEC('SET IDENTITY_INSERT Tst ON; INSERT INTO Tst(C) VALUES (1);') /* Also succeeds like this*/

Answer 2

My testing (SQL 2008 R2) shows that the identity spec on a column still hands out the right values in one session, even if the table has IDENTITY_INSERT ON in another session.

You should be able to bulk insert data into the table with specific identity column values in one session (with IDENTITY_INSERT ON) while another user (in another session) is depending on the identity column to function normally.