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

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 u开发者_如何学Gosing identity_insert on/off

please help.

---

Here's a test table for this discussion

create table t4721736 ( id int identity primary key, name varchar(10), comm money )
insert t4721736 select 'John', 232.43 -- id=1
insert t4721736 select 'Alex', 353.52 -- id=2

-- check contents    
select * from t4721736 

-- do all this in a transaction
BEGIN TRAN

-- dummy insert
insert t4721736 select 'dummy', null

-- get what the id should be
declare @resetto bigint
set @resetto = scope_identity()

-- remove dummy record
delete t4721736 where id = @resetto

-- perform the insert(s)
set identity_insert t4721736 on;
insert t4721736(id,name,comm) select 10000000, 'Smith', 334.23;
set identity_insert t4721736 off;

-- reset the identity
set @resetto = @resetto - 1  -- it needs to be 1 prior
DBCC CHECKIDENT(t4721736, RESEED, @resetto)

COMMIT

Assuming you fully understand (I believe you do) that it will fail as soon as the range runs up to the records with the nominated IDs. SQL Server won't perform any auto-skip over IDs that already have records attached.

that will not be a problem, coz when i insert using identity_insert on, value of id will be greater than 10 million. so there will not be any problem of clashing

To see how this fails, shortcut the process by changing the "10000000" into "10" in the code above. Then, follow up with these:

-- inspect contents, shows records 1,2,10
select * from t4721736

-- next, insert 7 more records, bringing the id up to 9
insert t4721736 select 'U3', 0
insert t4721736 select 'U4', 0
insert t4721736 select 'U5', 0
insert t4721736 select 'U6', 0
insert t4721736 select 'U7', 0
insert t4721736 select 'U8', 0
insert t4721736 select 'U9', 0

Finally, try the next insert below

insert t4721736 select 'U10', 0

---

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.

---

If these rows you're inserting are special/magic rows (so they need specific IDs), have you considered making these rows have negative ID values? That way there's no conflict, and the IDENTITY value will not be reset by your adding them.

If it's some other reason why you need to insert these rows with vastly different ID values, perhaps you could expand your question to provide some info on that - we may be able to offer better solutions.

---

Another way to get around the "planted bug" dilemma is to create your own identity generator procedure and tracking table. The table includes a tablename and value that the next ID should be. This way you can reset it any value at any time. The procedure would include logic to check to see if the next generated key exists and if it does exist it will increment the key till it finds an ID that does not exist in the table and pass that back out to you. This would have to be implemented on all inserts to work correctly. Which is possible with a trigger. The downside is more processing overhead than using a negative number like Damien_The_Unbeliever suggests.