SQL Server identity suppression context

Question

Using SQL Server 2008 R2, I have a Users table in which I would like to reserve the top 99 identifier values ​​for users with manual entry system. I set my ID number to 100, so that all new users will be set to 100+ in normal behavior. I found that I could use SET IDENTITY_INSERT to suppress the identification specification and provide an explicit value for this column. What I need to know and cannot understand from MSDN is that this suppression will only apply to the script launch context. (i.e. new users created on the site by calling the regular proc insert will still receive an auto-generated identifier of the following value> 100)

Answer 1

SET IDENTITY_INSERT allows you to directly insert an identifier value, it does not require it. Thus, even if it was a general setup, starting your “normal” INSERT will receive an auto-generated identifier.

However, it is also a session parameter, so you are doubly safe.

As for the design - I did this and I don’t see a problem in it. Having "well-known" identifiers is fairly common - although you will have to decide if 100 is the right block for redundancy. Although it is possible, you really do not want to subsequently open an endless block. I used negative numbers in the past when there was no reserved range.