Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2006

    Question Unanswered: Auto increment/seed value

    It is about sql server. In design phase if I put a column auto increment option enabled and if i set any specific seed value the row number starts increasing.

    For example if the seed value is 1 it becomes the first row. For the second row the number is incremented to 2 and 3 and so on... The problem is when I delete row number 3 and again insert any row..the inserted row number becomes 4 not 3. I mean sequence is broken whenever I delete any row. How to keep the sequence chronicle keeping the auto increment option enabled?

  2. #2
    Join Date
    Jan 2003
    Geneva, Switzerland
    In general you shouldn't use a synthetic value such as an auto-increment column for anything other than identifying a specific row. Trying to maintain such a sequence in order, with no gaps is IMHO an exercise in futility.

    Let's say you have inserted 5 rows (1, 2, 3, 4, 5) and now delete the row with id = 3. Should the next insert be 6 or 3?

    However, if you REALLY want to go in that direction then you need to write an ID allocator that keeps track of deleted values, and that assigns IDs based on available IDs (i.e. next highest if all previous values are in use, or fill a gap if there is one.) AFAIK there is no built-in functionality to handle this.


  3. #3
    Join Date
    May 2005
    South Africa
    Provided Answers: 1
    And note an identity column can jump (have gaps) see

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts