Results 1 to 5 of 5

Thread: autonumbering?

  1. #1
    Join Date
    May 2006
    Posts
    1

    Unanswered: autonumbering?

    Hello

    First post to this forum.

    My question: I'm sure I read somewhere that ideally you shouldn't use the auto-increment facility in a table's id field but should generate you're own when you insert. Does this make any sense to anyone or is it just some rubbish I read online somewhere.

    Any thoughts appreciated.

    zing

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it's rubbish, sorry to say

    go ahead and use IDENTITY, lots of people do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2006
    Posts
    4
    Quote Originally Posted by zingmatter
    Hello

    First post to this forum.

    My question: I'm sure I read somewhere that ideally you shouldn't use the auto-increment facility in a table's id field but should generate you're own when you insert. Does this make any sense to anyone or is it just some rubbish I read online somewhere.

    Any thoughts appreciated.

    zing
    The only problem you'll have is if you want to copy the data from the one server to another i.e moving from development to production or whatever, as your id's will be reset to match the current counter in the target table.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by zingmatter
    Hello

    First post to this forum.

    My question: I'm sure I read somewhere that ideally you shouldn't use the auto-increment facility in a table's id field but should generate you're own when you insert. Does this make any sense to anyone or is it just some rubbish I read online somewhere.

    Any thoughts appreciated.

    zing
    Read up in Books Online about IDENTITY values and GUIDs (globally unique identifiers). You will find that each has advantages and disadvantages in different situations. IDENTITY columns should not be generated outside the database layer, but GUIDs can be generated at any layer of the application and there are some occasions when this is advantageous.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Another issue that is sometimes a problem is that IDENTITY values are guaranteed to be unique. That does not mean (in fact it nearly prohibits) them from being sequential. Any ROLLBACK or DELETE operation leaves gaps in the numbering. This isn't a problem for code that needs a unique, consistantly ascending or descending number sequence, but it is a serious problem for poorly written code that demands a contiguous sequence.

    I'll also second Blindman's notiion of giving GUID values consideration. They tend to either expose or fix many of the problems I've seen caused by poor use of sequences.

    -PatP

Posting Permissions

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