Is MS-SQL’s IDENTITY_INSERT session-specific?
The Problem
Last week, I worked on updating integration tests in one of our projects. What these tests mainly did was —
- Insert data into some tables
- Run a process/function/application that processes this data and writes to another table X.
- And expect some data in table X, which is then asserted using select queries.
These tables, however, have an identity column, so we have to disable the constraint and then make our insert.
All this had been working fine, but after my change suddenly it stopped working and started throwing this error — (Which was unusual, since I already set the IDENTITY_INSERT to ON.)
Cannot insert explicit value for identity column in table ‘Friends’ when IDENTITY_INSERT is set to OFF.
What was my change?
My change was making sure each SQL command is executed in a separate connection/session. (Why? Well that’s a different story, for the sake of brevity let’s leave that part out.)
_databaseService.DisableIdentity(tableName);
_databaseService.Insert(tableName, values);Which means for the above two functions, a new connection is opened, the command is executed and the connection is closed.
Testing same behavior with SSMS
So to confirm this behavior, I fired up my SSMS and created a test table with an identity column.
CREATE TABLE [dbo].[Friends](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL
)
Next, I tried to insert into this table without setting the IDENTITY_INSERT to ON.
As expected, we get this error, it won’t allow me to insert unless I turn IDENTITY_INSERT property to ON.
Now to simulate the exact same behavior of my application, I will fire up the 2nd session and set the IDENTITY_INSERT property to ON there. And then go back to the first session and run the insert statement expecting it to work, BUT…
This was not expected! On googling a bit, I found this magical line on MSDN.
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON.
And it all makes sense now, wish I knew that before 🙂
ANOTHER QUESTION
So, before I knew this new piece of information, this is the steps I used to follow when inserting into an identity column.
- Set the IDENTITY_INSERT property to ON
- INSERT
- Set the IDENTITY_INSERT property to OFF
Now, that we know IDENTITY_INSERT is session-specific, does it mean IDENTITY_INSERT resets to OFF whenever a session is closed?
The short answer is NO, it’s not required since this property is SESSION based. For more detailed read, I would recommend this Stack Overflow question — How do you check if IDENTITY_INSERT is set to ON or OFF in SQL Server?
