5

I am using godaddy shared hosting & SQL Server. There are two tables (usertbl, patienttble) with primary key auto-incremented by 1. I am using LINQ to SQL classes for DB operation.

Problem is after adding uid (primary key from usertbl) as 1443, SQL Server added next row uid as 11443 and at the same operation in patienttble previous id was 1439, it added next row and id was 11439. I am not able to understand how this happen. Before and after this problem happen id in both the table was incremented by 1 properly.

James Z
  • 12,209
  • 10
  • 24
  • 44
Pritam Ajmire
  • 47
  • 1
  • 4

1 Answers1

1

SQL Server does not guarantee that there are no gaps in identity fields, and that's what Books online also tells you:

Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed.

Like mentioned above, using SEQUENCES with NOCACHE could be good enough alternative.

James Z
  • 12,209
  • 10
  • 24
  • 44