Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Question Unanswered: Kinder garden question about datatype

    Hi all,

    Suppose I know that the maximum length for my customerID field will not be more than 10 characters (95% of times this will be numeric digits like 1000127, while 5% of the times this may be alphanumeric like 1001MT56).

    Given this scenario should I use char(10) as the datatype for my customerID field or should I use varchar(10)?
    Can you please share your advise from a performance aspect.
    BOL has a lot of information but I could not the exact answer to this "kiddy" questions and thus ended up posting this question here.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the percentage of numeric/non-numeric doesn't really matter, what's important is the percentage of fully 10 bytes to less than 10 bytes

    since 100% of your examples (yeah, i know you gave only two) were less than 10 bytes, i would say use VARCHAR(10)

    performance difference should be too small to measure, but your file size will be less with VARCHAR
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    About the only times I use CHAR these days is for SSNs, Zip codes, State abbreviations, and occasionally boolean Y/N.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what size CHAR for zips? will it hold both 5-digit and 9-digit zips?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I usually use separate 5 and 4 byte columns for the two ZIP components.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman
    I usually use separate 5 and 4 byte columns for the two ZIP components.
    How do you deal with postal codes for non-United States addresses?

    -PatP

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan
    How do you deal with postal codes for non-United States addresses?
    most likely he rejects them as not valid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    It is my patriotic duty to impose my belief in nine-digit zip codes upon the rest of the world and liberate them from their godless postal-code systems.

    Unless they are an economic trading partner, in which case I just use varchar(20).
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Jan 2006
    Location
    Los Angeles, CA
    Posts
    63
    Quote Originally Posted by blindman
    It is my patriotic duty to impose my belief in nine-digit zip codes upon the rest of the world and liberate them from their godless postal-code systems.

    Unless they are an economic trading partner, in which case I just use varchar(20).
    I like that humor when it's most needed!

    Or you could quote our moron-in-chief as he says "I am the decider.." and do what ever you want without fear of any consequences .. I bet he added "bite me!" but no one heard it oops! .. that's too much politics isn't it?

    On a lighter note, one of the reasons I am hooked to this forum is the priceless sense of humor that Rudy and the Blind dude have

    Cheers!

  10. #10
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by GDMI..
    oops! .. that's too much politics isn't it?
    don't worry. the other group of talentless corrupt minions of corporate america will be back in power soon.

    Quote Originally Posted by GDMI..
    On a lighter note, one of the reasons I am hooked to this forum is the priceless sense of humor that Rudy and the Blind dude have .
    they are just masking their pain.
    “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.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Thrasymachus
    don't worry. the other group of talentless corrupt minions of corporate america will be back in power soon.
    Oh yes, but the problem is which one? There are SO many to choose from!

    -PatP

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I assumed he was talking about the Libertarian party...
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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