-1

For the really lazy on the field: is there a single statement that will toggle IDENTITY INSERT sometable from on to off and vice versa?

George Menoutis
  • 6,894
  • 3
  • 19
  • 43
  • You mean [SET INSERT_IDENTITY (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017)? – Thom A Sep 05 '19 at 09:57
  • @Larnu I think OP wants a command that will *toggle*, not be either `ON` or `OFF`. – DavidG Sep 05 '19 at 09:58
  • Yes. The request is to have the statement toggle the current status without providing it. So, running the same statement two times would first set it on and then off. – George Menoutis Sep 05 '19 at 09:58
  • 4
    No, there is no single line to do that. You could wrap it in a check (like [this](https://stackoverflow.com/questions/10637976/how-do-you-check-if-identity-insert-is-set-to-on-or-off-in-sql-server)) but this all seems super dangerous to me anyway. – DavidG Sep 05 '19 at 10:00
  • 2
    Explicitly setting it to `ON` or `OFF` _**has**_ to be better, for so many reasons, and it's hardly onerous – Diado Sep 05 '19 at 10:14
  • 3
    I would argue that any value that is auto-generated should carry no business meaning, so except for a very few well-defined cases, you shouldn't use `set identity insert at all. – Zohar Peled Sep 05 '19 at 10:30
  • @GeorgeMenoutis what's the *real* request behind that "request"? Why should anyone have to do that? Toggling is what someone though would be the answer to the problem, it's not the problem itself. The requirement for a single statement is even weirder. – Panagiotis Kanavos Sep 05 '19 at 13:08
  • 1
    This sounds like an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). There's a problem X (bulk imports perhaps?) and someone though that Y is the solution (toggling identity). When that doesn't work the question is about Y, not the actual problem Y. What is the actual problem? – Panagiotis Kanavos Sep 05 '19 at 13:10
  • Besides toggling means you have *no idea what the setting is* at any time. That's not a good idea at all and I can't imagine why anyone would want to be in such an uncertain state – Panagiotis Kanavos Sep 05 '19 at 13:11

1 Answers1

2

No, there is no 'toggel identity_insert' statement and for good reasons:

Values that are auto-generated by the database should carry no business meaning.

Using an identity column as a surrogate key, can help you keep things simple in your database, but to attribute business meaning to the values that the database generates for you is wrong - that what leads people to ask questions like "How to fix the gaps in my identity column" (my personal favorite answer is this one by Aaron Bertrand).

The fact of the matter is that autogenerated values are very helpful tools when you only use them as they are designed to be used - and identity column is designed to provide a simple row identifier which is incrementing (or decrementing) in the order the rows was inserted into the table.

For that reason, set identity_insert should only be used in very few situations - in fact, other then copying data from one table to another, when you want to keep the original values exactly - I can't even think of another situation where set identity_insert would be useful.

Combine that with the fact that only one table can be in identity_insert state per session, you should be able to easily understand why you don't want to see something like toggle identity_insert which is ambiguous at best.

tl;dr; Given the limited number of times the set identity_insert statement should be used, and it's limitations, having a toggle identity_insert statement is a really bad idea.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121