reseting autoincrement counter

[agoss]

in SQL 7.0 is it possible to reset the auto increment counter. for example, if insert 5 records into the db, they are numbered 1 to 5. if i delete record 5, i now have records 1-4 left. then, if i insert a new record it is assigned record number 6. how do i force it to use the deleted numbers?

thanks
andy

 

[foxdev]

You can't force it to use "deleted numbers", and its generally not a good idea to re-use primary keys. You could reseed the Identity value for the column, but I wouldn't do that with data in the table.
Robert Bradley

http://www.foxdev.com/

http://www.agrainofhope.org

 

[TomSark]

You could reseed the identity, as Robert (TIPMASTER OF THE WEEK) was alluding to, with the maximum value of the value + 1 as in:

Declare @maxval integer

select @maxval = max( column) + 1
from mytable

dbcc checkident( 'mytable', reseed, @maxval ) -- set it to 5

Now, if you do this, don't reseed it to a value lower than the max value. That is, if the max value of the column is 5, don't reseed to to a value lower than 6. If you do this, you will not be able to access the table... I shy away from this because it can be dangerous if you screw up the seed value...
Tom Davis
tdavis@sark.com

http://www.sarkdallas.com