Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    35

    Unanswered: Basic question - no one has the answer?

    Hi,

    I know the difference between char and varchar but there is conflicting advice from professionals about which one to use. In the case of a customer table (name of company, address etc) some professionals say use char because it will only occupy x amount of space and therefore querys will run faster because sql server does not need to check the length of each field. Others say use varchar because then there is no wasted space (seems obvious).

    Could anyone give me a description of why one is prefered over another?

    Thanks,

    Mark

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Basic question - no one has the answer?

    Speed will be required more for data path access...anything else is probably inconsequental...

    Sorry, still didn't give you an answer...

    But all development I've ever seen usually uses varchar...but I probably wouldn't say varchar(5) or something....

    All of Oracle is varchar2...except char(1)

    BOL was of no help...

    I'll give it a google...

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Basic question - no one has the answer?

    http://p2p.wrox.com/archive/sql_server/2001-04/101.asp

    Says almost the same thing....

    you can test it though...load up 2 identical tables and do some metrics

    If you're selecting lots of data it may be a problem...but you should use bcp in that case anyway.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Basic question - no one has the answer?

    Here check this site out...

    It's a research team the paired up with MS. They use SQL Server..

    anyway, they are mapping the entire nights sky.. I think they claim the database to be at 888GB right now...

    The even let you see their catalog on line...seems everything is varchar

    have a look

    http://skyserver.sdss.org/en/help/browser/browser.asp

  5. #5
    Join Date
    Sep 2003
    Posts
    35

    Re: Basic question - no one has the answer?

    Ok Brett,

    Thanks for the help and advice. You mentioned metrics. Do you know of any free software that can give performance metrics for sql?

    Many thanks,

    Mark




    Originally posted by Brett Kaiser
    Speed will be required more for data path access...anything else is probably inconsequental...

    Sorry, still didn't give you an answer...

    But all development I've ever seen usually uses varchar...but I probably wouldn't say varchar(5) or something....

    All of Oracle is varchar2...except char(1)

    BOL was of no help...

    I'll give it a google...

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Basic question - no one has the answer?

    Yeah,

    SQL Server itself....

    Build 2 tables that are identical except for the datatype differences you want to check out

    Create a while loop to populate with data for n times...yiou probably want to check it out for small medium and large row sets.

    Create a set of sql scripts to access the data in different ways and manners..

    set show stats and show plans in QA

    Start a trace with profiler...

    execute the 2 scripts in two different windows...

    do them seaparately at first..then run them against each other...

    Si?

  7. #7
    Join Date
    Sep 2003
    Posts
    35

    Re: Basic question - no one has the answer?

    Ok Brett,

    Thanks for the info,

    Mark




    Originally posted by Brett Kaiser
    Yeah,

    SQL Server itself....

    Build 2 tables that are identical except for the datatype differences you want to check out

    Create a while loop to populate with data for n times...yiou probably want to check it out for small medium and large row sets.

    Create a set of sql scripts to access the data in different ways and manners..

    set show stats and show plans in QA

    Start a trace with profiler...

    execute the 2 scripts in two different windows...

    do them seaparately at first..then run them against each other...

    Si?

  8. #8
    Join Date
    Jul 2003
    Location
    Penang, Malaysia
    Posts
    212
    1 major think that to take into consideration is:

    Does your column allow nullable values?

    If yes, then it is better not to have it as char. Because char datatype counts null as a storage value.

    e.g. if u declare char(1000) a null colum will store 1000 bytes of null data.

    If any of u still haven't thought of it from this angle!
    Patrick Chua
    LBMS ( Learn By My Self) NPQ ( No paper Qualification )

  9. #9
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Just my .02, if you will have to do any string manipulation, use varchar.

    Depending on the type of queries you will be running, char gives a predictable placing on the 8kb data page which may result in less logical IO.

    I usually use varchar unless I know there will be no nulls and it's a guaranteed length (like state abbreviations)


    HTH
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  10. #10
    Join Date
    Sep 2003
    Posts
    35
    Ok,

    Many thanks for the advice

    Originally posted by rhigdon
    Just my .02, if you will have to do any string manipulation, use varchar.

    Depending on the type of queries you will be running, char gives a predictable placing on the 8kb data page which may result in less logical IO.

    I usually use varchar unless I know there will be no nulls and it's a guaranteed length (like state abbreviations)


    HTH

Posting Permissions

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