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,

MarkSpeed 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...|||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.|||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|||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...|||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?|||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?|||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!|||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|||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