EG
1
2
4
5
8
The next primary key should be 3 (NOT 9)
This table changes often, so just adding 1 to the last number used, will fast run out of numbers.
The table is accessed by many users, so it can not be manually generated.
Using the IDENTITY property will successfully and correctly manage autonumbering. However, as you noted, deletions will leave gaps. I suggest that you use a datatype sufficient to allow both growth and allow for the deletions. A bigint datatype is a very, very large number -up to 9,223,372,036,854,775,807, I couldn't imagine that you are adding so much data to the database that a bigint would be inadequate.
It would be possible to create a 'homegrown' solution to manage this, but in my experience, with many users, that is a mistake. It would require a lot of table locking and/or data contention, placing unneeded stress on the database.
There are occassionally, real business needs for managing a numbering sequence, for example check numbers. But in those cases, deletions are usually not allowed.
|||Yes and after about 1 year or so when it flips over to 1 again, and starts over writing data.Its a very nasty bug.
Its happened before.|||
Hi:
Please refer to the following article, and see if it would help with your concern. :-)
http://www.sqlteam.com/item.asp?ItemID=765
Thanks.
|||That changes existing data.
but can be expanded upon, to use a multi sql statement to defrag a database.
but then how to reset the auto number to the next number.
So far the only way, i can do what i want, is one of two ways
1. Run all sql through a service, that can sync me a new auto fill in number.
2. Have unacceptable down time, and run a program to defrag the tables.
