This question already has an answer here:
- Unexpected gaps in IDENTITY column 2 answers
In one of our clients' (testers') databases the Identity column of a SQL Server Local Db datatable looks thus:
So, most of the time the identity increases by one, but sometimes the identity jumps up by 10,000. Possibly this coincides with moving the database between computers and reattaching them to SQL Server Local Db.
What may be related is the fact that this is an Entity Framework Code First generated database with Migrations. At one time there was a migration that executed the following Transact SQL:
Sql("DBCC CHECKIDENT (Sessions, RESEED, 10000)"), This database may have seen this migration. However, I deleted the migration afterwards and the _MigrationsHistory table in the database does not have a record for this particular migration.
If I have a look at the design for the table it specifies an identity of (1,1).
So, does anyone have some inkling about what is going on? Can I stop the 10,000 jumps?
EDIT: The reseed had nothing to do with it. As indeed can be read here the jump by 10,000 is a feature of SQL server for identity columns of type big int (64 bit).
Why I bother? The SQL Server Localdb engine is connected to a legacy VB6 application that we are migrating to .Net. The VB6 code is geared towards using 16 bit identity fields. Jumps of 10,000 at a time are disastrous...