Auto increment in Database

Question

I use SQL Server and when I create a new table I make a specific field an auto increment primary key. and its auto increment field by 1 , but the last two field the increment become 1008 and 1009 like this 1,2,3,4,5,6,7,1008,1009 . Thanks so much.

Answer 1

it is because you have deleted some records after insertion, the increment continues from the previous point where you deleted entry. So to resolve this, just off the auto increment and save table and then again apply auto increment and save table.

Answer 2

If you are using an IDENTITY column in Sql server you cannot control the numbers. The only guarantee you get is that the numbers will be unique and that a number generated at a later time will be greater than an earlier number. There's always risk of wholes in the number sequence on rolled back transactions.

If you need to keep tight control over the sequence and not allow any wholes in the sequence you have to implement it yourself. Remember to add proper locking to the database table containing the sequence if you have multiple clients updating on the same time.

Answer 3

This is the natural behavior of an auto-incremented identity field in SQL Server. There are a few reasons for this is: reboots, rows deleted, server running multiple inserts from multiple sources at the same time, etc... The PK is not supposed to "mean" anything in your application/coding, it is simply a unique pointer to a row of data. If it does "mean something" in your code, you should remove the auto-increment specification and handle the incrementing yourself. It does not and should not be used to indicate the number of rows in a table.