1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Harsha
  • 1,161
  • 4
  • 18
  • 38
  • 3
    In your example, what would you want to happen when you attempt to insert the 10th record with identity_insert off (i.e. the 10'th automatically allocated id would clash with the first id of the manually inserted "Smith" record)? – Daniel Renshaw Jan 18 '11 at 08:13
  • 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. – Harsha Jan 18 '11 at 08:26
  • Why do you care what the value of that primary key is? Why do you need to specifically set a value for that row in that case? – Lasse V. Karlsen Jan 18 '11 at 11:44
  • @Lasse: i need the primary key for sync web and desktop dbs. check this http://stackoverflow.com/questions/4721337/identity-insert-and-synchronization-problem-please-help – Harsha Jan 18 '11 at 12:57

4 Answers4

3

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
RichardTheKiwi
  • 105,798
  • 26
  • 196
  • 262
  • "it will fail as soon as the range runs up to the records with the nominated IDs" -- I wouldn't expect it to fail and testing your code using the value 4 rather than 1000000 it does not fail when inserting a further row. – onedaywhen Jan 18 '11 at 10:41
  • @one - we're on the same page. I put that disclaimer so that passers-by will understand that while this works for a specific purpose as the OP has clarified, it inherently plants a timed bug (even if it occurs 10k years later...) – RichardTheKiwi Jan 18 '11 at 10:46
  • @cyberkiwi: "it inherently plants a timed bug" -- really? I can't see it. The code works without error for me. – onedaywhen Jan 18 '11 at 11:28
  • @one - can't work out if you are serious, but I have updated the table column ID as "primary key", and added a test to show it failing. – RichardTheKiwi Jan 18 '11 at 11:43
  • @cyberkiwi: you are correct, in my case it fails once the identity crosses the limit. – Harsha Jan 18 '11 at 13:03
  • @cyberkiwi: "I have updated the table column ID as 'primary key'" -- well yes, that would make a big difference. – onedaywhen Jan 18 '11 at 14:08
2

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.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • then doesnt this affect the insertion of other records which are inserted from code. – Harsha Jan 18 '11 at 08:24
  • @Harsha - Isn't that what you are asking? Again, since you have a max ID of 10 in your table, there is **no way** that an automatic identity of 3 will be inserted. You will have to do identity inserts in that case. – Oded Jan 18 '11 at 08:25
  • i guess you are correct. but considering your answer, can i reseed the identity in a live database ? will there be any problem – Harsha Jan 18 '11 at 08:33
  • @Harsh - Why do you feel the need to do this? Having "spaces" in ID fields is normal for databases. – Oded Jan 18 '11 at 08:38
  • @Oded, this is my problem http://stackoverflow.com/questions/4721337/identity-insert-and-synchronization-problem-please-help – Harsha Jan 18 '11 at 08:42
  • 1
    @Harsha - the two answers given are reasonable ways to go forward. Resetting ID fields isn't. – Oded Jan 18 '11 at 08:43
2

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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

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.

GluedHands
  • 798
  • 1
  • 6
  • 16