0

I'm using visual studio 2010 an working on asp.net mvc3 project and sql server 2008. I have a table that the primary key of this table is int data type. I set Identity Specificatin "yes", Is Identity "yes", Identity Increment and Identity Seed "1". everything is OK when table is empty and data can be saved easily, But when I close visual studio, open it again and want to save data a break occured that shows the value of primary key is "1". (I deleted the data of database once, after this deletion the primary key of first row is 5 and other rows 6,7,...). What's the solution for this problem? your answer will be so helpfull. Thanks

starrr
  • 1,013
  • 1
  • 17
  • 48

2 Answers2

2

There is no solution for *automatic gap removal": identity columns always have gaps for good reasons. For example, the deleted row is probably in some history or audit table and you don't wan to reuse it.

However, you can reset the column by using DBCC CHECKIDENT

Note the gotchas here: SQL server identity column values start at 0 instead of 1

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
1

Using Sequence type for the Primary Key may help...

juankysmith
  • 11,839
  • 5
  • 37
  • 62
  • 1
    @juankysmth: Thank you for answer. But I can't change table. I should change all te code and I haven't enough time. – starrr Jun 13 '12 at 07:10
  • There are a lot of free tools to manage databases. If you have the database login/password, just download one of these tools (http://www.freewarefiles.com/category_8_88.html) and modify the column type in order to make in autoincremental. – juankysmith Jun 13 '12 at 07:34
  • Why do you think this is an answer? Sequences still have gaps. And why not link to SQL Server Express tools instead of some 3rd party site – gbn Jun 13 '12 at 07:42