Timeline for Why is IDENTITY_INSERT ON only allowed on one table at a time?
Current License: CC BY-SA 3.0
5 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 11, 2012 at 18:49 | comment | added | Olivier S | 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" | |
| Feb 11, 2012 at 16:28 | comment | added | Aaron Bertrand |
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.
|
|
| Feb 11, 2012 at 16:25 | comment | added | Olivier S | 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 ) | |
| Feb 11, 2012 at 14:44 | comment | added | Aaron Bertrand |
+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.
|
|
| Feb 11, 2012 at 13:11 | history | answered | Olivier S | CC BY-SA 3.0 |