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

Posted by evilek on Stack Overflow See other posts from Stack Overflow or by evilek
Published on 2010-06-14T17:07:27Z Indexed on 2010/06/14 17:12 UTC
Read the original article Hit count: 181

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 ?

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server-2008