Posted by Erland Sommarskog on 10/01/63 11:20

(paul@domainscanners.com) writes:
> Basically the original data's identity column went up to 1,000,000. I
> was hoping that the new data that was appended would be inserted as
> 1,000,001 then 1,000,002 then 1,000,003 all the way up to 1,100,000.
> However the new appended data went in as 1,254,324 then 1,254,325 etc.
>
> Is there a command I can run to resnycronise my identity column? so
> that the ID's run smoothly from 0 through to 1,1000,000?

If you want contiguous ids, or at least control over them, don't
use the IDENTITY property. When you attempt to insert a row into
a table with the IDENTITY property, you consume one number, even if
the INSERT fails. This may seem stupid, but it is actually a feature,
because it speeds up concurrency. If the number would be reused in
case of failure, SQL Server would need to lock the number, and no
other process had been able to insert until the INSERT have completed.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]