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

Thread: Char vs Varchar

  1. #1
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506

    Talking Unanswered: Char vs Varchar

    Hi,
    This question may sound silly,but please comment.
    Please tell me a situation where char should be used and not varchar.
    Let us assume that we are dealing with non unicode characters.
    Well, I find varchar is always smarter than char, so why char?
    Thanks!!
    Rudra

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    fixed length identifier fields? even though smart numbers are stupid.
    “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
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use CHAR(n) instead of VARCHAR(n) when n<4

    consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table

    what did you mean by "smarter" anyway?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Thrasymachus
    fixed length identifier fields? even though smart numbers are stupid.
    Yea, I have seen those in fixed length identifier fields,any more use of char?
    Well, in database designing which attributes are assigned as char?
    If we don't use all the characters tehn its a mess....what are the best situation to use char? Plz comment....

    It may seem a silly one but I think it has an important significance in database designing....
    Thanks!!
    Joydeep

  5. #5
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by r937
    use CHAR(n) instead of VARCHAR(n) when n<4
    Yea thats a very good point...
    and
    consider using CHAR instead of VARCHAR when there's only one non-null VARCHAR in the table
    that should be applied when and only when n<4.Isn't it?
    ok,thank you for those info.

    what did you mean by "smarter" anyway
    by smarter I mean to say varchar though variable length does provide more efficient storage than char and also doesn't need any trim functions to compare....and many more are there...

    Thanks!!
    Joydeep

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, those are advantages for VARCHAR, good points

    there really isn't any reason to have CHAR, when you think about it

    i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by r937
    i'm guessing it must be an historic relic from back in the days when databases were a lot less efficient handling VARCHARs
    LOL,
    rudy.ca... its cool and your pic too
    Thanks again r937

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.

    In general, I prefer to use VARCHAR, but there are times and reasons to use CHAR, and I wouldn't want to be without it as a choice.

    -PatP

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Pat Phelan
    Oh heavens, there are lots of reasons for using the CHAR datatype. It is far more efficient when dealing with "character indicators" which are short, fixed length strings (like Y/N, M/F, etc). CHAR is also better for moving data back and forth between today's equipment and yesterday's equipment... It is practically impossible to deal with variable length columns in Z/OS, and many of us still have to deal with things like that.

    -PatP
    And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.

    Thanks Pat
    Joydeep

  10. #10
    Join Date
    Dec 2002
    Location
    Vancouver, BC
    Posts
    35
    Quote Originally Posted by rudra
    And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields.

    Thanks Pat
    Joydeep
    I would say using CHAR in the zipcode is a good idea. I'm in Canada and we have postal codes that contain letters and numbers. Not only that, they are 6 characters long! I've seen some pretty bad e-commerce sites that wouldn't let me put in my address because their "zip code" field wouldn't let me enter the last character of my postal code.
    INSERT INTO Jokes (statement) VALUES ('Here is my witty SQL signature');

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by rudra
    And also in fields like zip code but I fear zip/post code are always >4 but I have seen lots of databases using char in zip code fields
    Well, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters. Any time you have a fixed-length string (such as a five digit zip code or a nine digit social security number) CHAR is more appropriate and more efficient than VARCHAR.
    If it's not practically useful, then it's practically useless.

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

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    Well, the rule about using CHAR when length < 4 really applies to variable length strings less than four characters.
    i do believe i said that quite early in the thread

    Quote Originally Posted by blindman
    Any time you have a fixed-length string (such as a five digit zip code ...
    in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your app

    whereas with CHAR(5) for the zip code, you're screwed

    another fine example of the one of the many benefits of VARCHAR

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

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by r937
    i do believe i said that quite early in the thread
    Great advice is worth saying twice, eh?

    Quote Originally Posted by r937
    in this particular case VARCHAR(37) would've been way better, since it would allow you to store 9-digit (or 10 character, if you store the dash between the 5 digits and the 4) with absolutely no change to your database or your app
    I'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman
    I'm a strong believer in storing ZIP and ZIP4 as separate fields. Pesky normalization habits of mine...
    oh you silly man

    okay, either you are consistent and silly, or else you are inconsistent and pragmatic, but please don't use "normalization" as an excuse for rationalize it either way

    do you put house number in a separate column? i.e. not address1='123 sesame st' but address1_number='123', address1_street='sesame st'

    do you put apartment/suite number in a separate column?

    do you put zip code into a different table? after all, it's in a one-to-many relationship with addresses, so if a zip code changes, wouldn't you want to use a surrogate key instead?

    and really, the 4-digit zip code suffix is functionally dependent on the 5-digit zip code prefix, so if you have those two columns side by side in the same row, what does that do for your normalization efforts?

    address fields are NOTORIOUSLY the wrong example to use when discussing normalization

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

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I always enjoy a goo d pedantic discussion...

    Speaking of OS/390 z/OS

    varchar is still painful in DB2 for the Client and/or COBOL Sprocs?

    I'm about to launch a new dev project there and am in the middle of building the model soon...and they want free form description columns out the but at 300 bytes...

    I need to talk them down to 255 to avoid LONG datatypes, but since they have so many, I was hoping to use varhcar.

    I'll use char to make life easier, because I really don't care about DASD all that much...I just imagine speed will be impacted because of the misuse of the buffers...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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