Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Lightbulb Unanswered: Identity column jumps indefinitely

    Guys,

    Iam new to this forum, Hello to all...
    Iam facing a problem in my application. Have recently noticed that my primary key column which is an " identity " with increment 1 being set.
    But now iam noticing a various jumps in the number instead of 1. The numbers in the jump is not consistent.
    Has anyone faced this kinda problem.
    ?????

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    many people have seen this situation

    those who are using identity columns only to provide identity (uniqueness) do not see a problem at all

    those who are concerned about gaps in the sequence of numbers, as representing a problem for their application, should re-design their application so that they don't rely on identity columns
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2006
    Posts
    2
    I agree with your comments not to use the identity on the application.
    But in my case, i dont delete the records, it automatically jumps the numbers.
    say for example a record is created with number 301 today morning
    during afternoon there is another new record with number 899.
    But why this jump is happening. Iam curious to know about it....

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i'm curious too

    when it gets close to the 2-billion number, you may want to have a look at it again

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2006
    Posts
    27
    This is old info, off the top of head, but as I remember it:

    Basically, the Identity feature 'grabs' a block of numbers, and doles them out. Not sure what the default is. Assume 100 (1-100). When the first insert happens, it gives out '1'.
    For performance, the server grabs them in bunches, and saves the next, (101), so it doesn't need to keep getting locks for each insert. If the db server goes down, the next record will get '101'.

    Don't use identity for consecutive numbering.

    Jay Grubb
    Technical Consultant
    OpenLink Software
    Web: http://www.openlinksw.com:
    Product Weblogs:
    Virtuoso: http://www.openlinksw.com/weblogs/virtuoso
    UDA: http://www.openlinksw.com/weblogs/uda
    Universal Data Access & Virtual Database Technology Providers

Posting Permissions

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