20

It is the case that IDENTITY_INSERT can only be set to ON in one database table at a time, but why? Since IDENTITY columns aren't globally unique I can't think of any dangerous situation that could be caused by inserting identities into more than one table at the same time (at least not more dangerous than generally fudging with IDENTITY INSERT).

IDENTITY INSERT should rarely be used but what is the reason for the hard limit?

Improve this question
5
  • 1
    Deterrent perhaps, so it is rarely used?
    – Remus Rusanu
    Commented Feb 10, 2012 at 19:05
  • @RemusRusanu that's kind of what I was thinking, either that or to make sure you don't accidentally leave II ON for multiple tables.
    – Zelda
    Commented Feb 10, 2012 at 19:12
  • @Ben why is leaving it on accidentally for multiple tables worse than accidentally leaving it on accidentally for one table? Both can lead to the same kind of issue. I am genuinely curious about your question, and I don't think deterrent is the answer, or we'd have a lot more restrictions in the engine. But I do agree that if you feel you need to do this often, there is probably something suspect.
    – Aaron Bertrand
    Commented Feb 10, 2012 at 19:18
  • @AaronBertrand it's not, as I implied in the Q. Not sure about deterrent either, since SQL Server allows lots of other bad practices like naming your columns with reserved keywords (sometimes even if you don't use []!)
    – Zelda
    Commented Feb 10, 2012 at 19:20
  • @Ben right that was not necessarily for you but for any reader that came across the question.
    – Aaron Bertrand
    Commented Feb 10, 2012 at 19:21

2 Answers 2

Reset to default
12

I think it's to make it difficult. If you could just leave it on all the time, why even have an identity field?

There are actually a couple of restrictions, though:

  • It persists only on that connection
  • It's only settable on one table per connection

Based on the connection-related restrictions, I think it's mainly so it's never accidentally left ON.

Imagine if someone turned on ID insert on one of your tables, then you didn't realize and a (normally) invalid insert was performed that broke the integrity of your ID field?

Bear in mind ID fields can have duplicate values if there is no constraint or unique index in place...

Improve this answer
3
  • 1
    +1 I think your last point about duplicates is missed by a lot. Folks think if they set IDENTITY it also becomes a unique constraint. Very easy to disprove, of course, if they though to try.
    – Aaron Bertrand
    Commented Feb 11, 2012 at 14:46
  • @AaronBertrand But again, the duplicate ID risk is exactly the same on any table with IDENTITY INSERT on, why the hard limit? I think the fact that it persists only for the connection makes much more sense as a precaution.
    – Zelda
    Commented Feb 12, 2012 at 0:01
  • I wasn't suggesting the duplicate ID problem was a reason for the hard limit.
    – Aaron Bertrand
    Commented Feb 12, 2012 at 4:18
6

My guess is that is was a restriction due to the implementation. Allowing this setting on multiple table was a potential perfomance hit:

Since this is a session parameter, allowing the setting to be activated on a single table means that it is simple flag and the object id of the table to store on the session, server-side. Maybe this is just a single integer: 0 if no IDENTITY_INSERT is active, and some coding of databaseid + objectid for the table.

Allowing the parameter to be set on multiple tables within a session would mean that the server would store a dynamic list of such objects and check it for every insert statement. Imagine a session activates the parameter for one thousand tables:

  1. This mean the server has allocated 1000 items in the session variable
  2. This mean also that the server has to check the list of the 1000 items for every insert statement in this session.

Also I suspect that set identity_insert on has a performance wide performance hit on the server. In sybase there was an "identity burning set factor", which allowed to save the value of identity counter of a table to be saved only once in while ( the value is kept in memory and written down to disk once in a while and at server shutdown ). SQL Server is based on the same code so probably has some comparable optimization, but activating identity_insert on a table probably constrains the server to save the identity value for every insert, because else it can not guarantee a maximum gap size. So if one session makes a performance hit on the inserts in one table this is probably acceptable, but not if it can make the perf hit on all the auto_increment tables on the server..

Improve this answer
4
  • +1 Probably some truth here. I don't buy the gap size argument, as only one INSERT can be going on at a time for a session, and I could easily be inserting 10 million hard-coded IDENTITY values.
    – Aaron Bertrand
    Commented Feb 11, 2012 at 14:44
  • The gap size is related to what happens in case of crash: on sybase, if the server crashes the last identity is lost ( it was in memory ), so it restarts leaving a gap ( see identity burning set factor )
    – Olivier S
    Commented Feb 11, 2012 at 16:25
  • So in SQL Server are you suggesting that something different happens if the engine crashes while inserting 1,000,000 rows with an identity column, or overriding the identity column with 1,000,000 hard-coded values while SET IDENTITY_INSERT is enabled? I'm just suggesting that gap size does not affect multiple tables any differently than it affects a single table.
    – Aaron Bertrand
    Commented Feb 11, 2012 at 16:28
  • my guess - I have absolutely no proof of this - is that SET IDENTITY_INSERT on a table forces a write-to-disk of the autoincrement at each insert. The rationale would be that since the value you insert can be anything, the server can not consider "ok, if I write to disk only once every 1000 rows, in case of crash I can safely add 1000 to the last value I saved"
    – Olivier S
    Commented Feb 11, 2012 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.