Toggle insert identity with single statement

Question

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?

Answer 1

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.