Why is IDENTITY_INSERT ON only allowed on one table at a time?

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?

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...

StackExchange Database Administrators Q#12650, answer score: 12