How to force SQL Server 2008 to not change AUTOINC_NEXT value when IDENTITY_INSERT is ON ?

Question

Hello,

I got question about IDENTITY_INSERT. When you change it to ON, SQL Server automatically changes AUTOINC_NEXT value to the last inserted value as identity.

So if you got only one row with ID = 1 and insert row with ID = 100 while IDENTITY_INSERT is ON then next inserting row will have ID = 101. I'd like it to be 2 without need to reseed.

Such behaviour already exists in SQL Server Compact 3.5. Is it possible to force SQL Server 2008 to not change AUTOINC_NEXT value while doing insert with IDENTITY_INSERT = ON ?

Answer 1

I'm not aware of any way to prevent this behavior - after all, it's a prudent thing to do, anyway! If you've already set a value x (whatever that value might be), you shouldn't leave your seed value lower than x since otherwise, you're bound to run into getting a value in your IDENTITY column that's already there - not a good place to be!

But if you must, you can re-seed your IDENTITY column after you're done with your inserts using:

DBCC CHECKIDENT ('YourTableName', RESEED, 300);

where you can set any arbitrary value as your new seed value (here: 300). Of course, you need to be extra careful not to create any duplicates by setting the reseed value too low.

Answer 2

Additionally, if you do a reseed and your identity column is also a primary key, when you get back to the originally inserted value when IDENTITY_INSERT tablename ON was set you will get a PK violation. Another thing to think about.

Answer 3

Why would you want to do that? In the first place, there should be almost no occasions where you set identity insert to ON in a production system. And what difference would it make if you skip some? You can't count on identities not skipping in any event since it will skip if you do a rollback.