How to avoid "SQL Server automatically uses the new inserted value as the current identity value."

Question

I'm using SQL Server 2008

as per microsoft, http://msdn.microsoft.com/en-us/library/ms188059.aspx

when I execute the following

set identity_insert on  
//insert statements here
set identity_insert off

the identity of the column is set to the maximum value. Can I avoid this?

Consider the following scenario,

my table has 2 rows as follows

id, name  comm  
1,  John, 232.43  
2,  Alex, 353.52  

now using the above code, when I insert

10, Smith, 334.23

as per the above link, SQL Server automatically sets the identity to 10. So for newly inserted records (without using identity_insert on), id automatically starts with 11.

I want the identity value to be 3, after using identity_insert on/off

please help.

Answer 1

You can reset the seed value using DBCC CHECKIDENT:

DBCC CHECKIDENT ("MyTable", RESEED, 3);
GO

However, you have inserted a record Id of 10, so yes, the next one will indeed be 11.

It is documented on the command:

If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.

You can't have it both ways. Either have the lowest ID be the value of the base seed, or not.