repeating identity

When using Access - you had to be careful about the autoincrement feature. If you delete a record from a table ( autoincrement id=1000) -then compact/repair - then add a new record to that table - the autoincrement field will say 1000 - if that autoincrement value was used to uniquely identify something - it is no longer unique. This is all background for my question...

Does SQL 2000 do this also ? I must have read somewhere that it doesn't - since I have code that moves records around ( delete from one table - insert into other), but the other night, I was awakened by the thought that SQL2000 does the same as Access - i.e. repeating identity after compact/repair

Do I need to worry ?The only thing you need to worry about is if you happen to use theSET IDENTITY_INSERT ON command when inserting records.

This command will allow you to specify a value for the identity column when inserting a record instead of allowing SQL to assign one. And SQL won't know that your assigned numbers are already used and could attempt to reuse them later. This would only happen if you were sloppy in your assignment -- and it is not likely you would need to ever use the SET IDENTITY_INSERT anyway.

FWIW,
Terri|||thank you everyone : ) After re-reading my original post - I thought maybe I should reword it , just to make sure.

I have a table for service calls that has the identity field used for the service call number.
When the call is closed, I delete it from the open service call table - and insert it into the closed calls table.

If the call was number 1000 - open service calls highest identity will be 999.

when the next new call is created - will SQL assign 1001 or 1000 ( access would set new rec to 1000 - if a compact was done )

thanks again|||I'm not 100% clear...but if I am following you...

If your service call was 1000, and then was closed, and you then deleted that service call record from your table, the next identity assigned would be 1001. SQL Server will not reuse 1000.

With 2 exceptions
-- you explicitly insert a record with the identity field set to 1000
-- you reset the identity seed to 1000

Under normal circumstances without you doing anything funky like that, you are fine. SQL Server does not behave like Access.

Terri|||thanks again