I am running the MS Access database on SQL Server Management 18. For some reason, when I created a new entry, 994 index numbers just skipped. My last indexed number was 19311, and then it suddenly jumped to 20305 when captured. What can I do to let it run from 19311 onward again?
Asked
Active
Viewed 200 times
-1
-
Normally you should not rely on autonumbered field sequence because such kind "gaps" may appear quite often especially in a multiuser environment as a result of deletions of errors during inserting – Sergey S. Feb 11 '19 at 13:07
-
Thank you. To prevent this from happening again, what should the autonumber be changed to then? – Queline Feb 11 '19 at 13:14
1 Answers
1
This is pretty usual.
An identity seed is allocated before a query is committed. This means, if you run a query that inserts 100 records, but when getting the prompt if you actually want to add 100 records you press cancel, the identity seed is still incremented by 100. The same counts for copy-pasting records and many, many other operations.
You shouldn't need to prevent this from happening. Identity values are not meant to convey any meaning, and there shouldn't be a real need for changing them. If you've set your identity column to an Int(8) or Long Integer, you still have plenty of numbers to use.
SQL server explicitly blocks updating an identity column, and you also can't reseed an unique column below the initially set seed. This means: as soon as you've inserted number 20305, you can't reset it to a lower number than 20305.
You can work around that limitation by deleting all records higher than 20305, and then running DBCC CHECKIDENT ( table_name ) on SQL server with your table name to reset the seed to the highest occurring value. You can then re-add the deleted records.
See more on this Q&A for reclaiming the lost numbers, though I certainly advise against it.
Erik A
- 31,639
- 12
- 42
- 67