Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: String as primary key? Or Number?

    Here's another tricky one.

    Let's say I have a table where each row requires a field called part_number and several other (irrelevent) fields.

    part_number is actually an 11-digit number. This may increase to 12 someday in a future

    Should I make part_number a VARCHAR, or a BIGINT?

    I will also want to make this the primary key

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    why would it be a bigint?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by Thrasymachus
    why would it be a bigint?
    So it could fit such a large number, the other datatypes would be insufficient

  4. #4
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by dbguyfh
    So it could fit such a large number, the other datatypes would be insufficient
    Actually, I think I misinterpreted your question.

    The resaon I would use bigint would be perhaps using a numeric datatype would be faster (better performance) than using a varchar for the primary key

  5. #5
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Also less storage space: 8 bytes for BIGINT, 11 bytes for VARCHAR (because you said you have an 11-digit number)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    +3 bytes per record.

    1 million records.
    3 million bytes.
    3 Mb.
    Ouch
    George
    Home | Blog

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's more about data per page, and (assuming it is the clustered key) data per NCI leaf page. But yeah - 3 bytes (plus the overhead of a VARCHAR don't forget!) ain't much.

    My vote goes for CHAR with a constraint.

    Do you need to do any string manipulations on this? Is the "number" meaningful?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2008
    Location
    Columbus, OH
    Posts
    26
    Things to consider...

    - A BIGINT is 8 bytes. A CHAR(11) is 11 bytes (I'm opting for CHAR instead of VARCHAR because I'm inclined to believe it will always be 11 until you need 12).
    - Indexing on numbers is typically better than indexing on text.
    - At the risk of starting a natural vs surrogate key debate, have you thought about using a 16 byte GUID as the primary key?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And let's go back a minute... You could have more than 10,000,000,000 part_numbers ?!
    George
    Home | Blog

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    i'd go with bigint unless you need to do string parsing on this column.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    And let's go back a minute... You could have more than 10,000,000,000 part_numbers ?!
    It ain't a count dude
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2008
    Posts
    186
    I wont need to do string comparisons on the column

    The number is meaningful only in a sense that it tells me which part the row in the table refers to.

    The other alternative is to create a sequence surrogate key... Although, to me, that seems unnecessary because the part_number already does that job

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If I ain't doing maffs with it I default for text column. How many peeps here use numerics for tel numbers?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dbguyfh
    The other alternative is to create a sequence surrogate key... Although, to me, that seems unnecessary because the part_number already does that job
    Well that is the natural vs surrogate debate
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by pootle flump
    It ain't a count dude
    Hah, yeah think of them as just unique identitfers -- but created in the real world

Posting Permissions

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