Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Autonumber gaps

  1. #1
    Join Date
    Nov 2007
    Posts
    35

    Unanswered: Autonumber gaps

    Hi there, I'm struggling here.

    My autonumber field is crazy. It will increment when I test my tables by adding records, once deleting them (or cancling the save function) it won't go back! This produces gaps between records.
    Can I actually initialize the autonumber field back to its last value?

    I basically want a field that will record a running number from 1 onwards, without any gaps in between. Is this possible?

    Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi ronh

    This is by design. Filling in the gaps is a pain and in the vast majority of circumstances you should not care whether or not it is consecutive. An autonumber is a convenient data type\ property and is typically used as a surrogate key. I.e. you define it as a primary key and use it in your database relationships. As such, the idea is that it is used internally only and not exposed to any user. In this case - any gaps are irrelevant.

    Make sense? How come you require a gapless number?

  3. #3
    Join Date
    Nov 2007
    Posts
    35
    Yes it does make sense. Some situations can make it complex, such as deleting a record somewhere inside my table (and maybe several users working simultaneously on the same table?). I do understand it's cavemen style, and it actually shouldn't matter if there are gaps so long as I can calculate how many records are there.
    Anyway, in order to keep the number small - how can I at least initialize the autonumber back to 0 once I've finished testing and the database is ready?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is called reseeding. There are a few methods:
    http://www.dbforums.com/showthread.php?t=1607477

  5. #5
    Join Date
    Nov 2007
    Posts
    35
    That will do. Thank you!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I like to think of autonumber as a purely internal value.... it should not have any significance outside the system, in the human world. that means you don't use it in place of invoice numbers, receipt numbers, or anywhere where you must have sequential numbers. About the only time I would expect to use an autonumber column and be visible outside the computer is for say an audit file, and even then it has limited use.

    Outside of that your autonumber should in my books not even be visible to users... it should have no significance or relevance to them as its used to maintain data integrity.

    Out of curiousity, why do you feel the need to reseed?

  7. #7
    Join Date
    Nov 2007
    Posts
    35
    Because I do intend to use it outside of the database -
    My database basically records work I do (test I do). Every test gets a number and is filed on the shelf. I was hoping to automatise giving every test a number, won't this method do?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with having gaps in the numbers that are sitting on the shelf?

    if what you want is to know at a glance how many you've done, use SELECT COUNT(*) FROM ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you don't care about gaps, and just want to use it as a value to act as pointer to an external physical file containing details of the actual test for reference then its fine. (say for example you want cross reference the computer data to say a paper report / physical paper file then using the number is fine). But I'd strongly recommend that its not used for anything other than that. if it doesn't matter that there is a gap between say test 52 & test 56 then its fine.

    if however you need to allocate numbers such that test 53 follows test 52, then you need to do something else. If your accountants, auditors, sales or whatever demand that the numbers be in ascending sequence then use another method. Providing you keep to the principle that the autonumber value has no meaning outside the system then its fine.

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    as healdem said: the raison d'être of an autonumber surrogate key is precisely to have zero meaning - it's inner beauty is that you will never be tempted to edit (reseed, whatever) the key since the value of the key has no meaning outside of the internal workings of the db.

    (BTW i posted a sequential numbering example in the codebank)

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Gotta admit - I like to reseed before deployment. I guess I deserve six of the best and should read up on the surrogate Vs natural keys debate one more time

    Naughty poots.

  12. #12
    Join Date
    Nov 2007
    Posts
    35
    Thanks everyone, I am kind of required to keep the sequence gapless, but I will convince the supervisors to accept gaps, since there's no much meaning now. There was a use for it when there wasn't a computerised database - to quickly know how many tests were done. Will also use the autonumber as a cross reference to the shelf, this will make life easy.

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    poots! super to have a "fight" with you.

    OP talks of "test" and reseed so your comment is reasonable.
    ...if there is a risk of hitting the two dot something billion limit of the autonumber before hitting the two dot something billion byte limit of an A-file (...each autonumber taking a smidgen more than one Byte if i recall correctly).

    otherwise??????????

    izy

    LATER: forget it - i'm being obstinate after surviving a(nother) lousy day! there is ZERO sense in refusing to reseed before deploy
    Last edited by izyrider; 12-19-07 at 13:33.
    currently using SS 2008R2

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by izyrider
    otherwise??????????
    I like it because it is "clean". It is almost like superstition - there is no reasonable explanation - I just like my surrogates to start from 1 when data gets entered for real. I know this flies in the face of everything we tell people getting to grips with surrogates but there it is

  15. #15
    Join Date
    Nov 2007
    Posts
    35
    To add a point to the discussion of reseeding autonumber, it is easier to write down 3-digit numbers on the physical papers, rather than 9-digit numbers.

Posting Permissions

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