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:
- This mean the server has allocated 1000 items in the session variable
- 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..