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

Bad ordering when ordering by varchar field

Hi guys,
did anyone come across this problem?
I have a query where I ORDER BY Part_Number field which is varchar but
the ordered result is
10169-G
10169-MVS
10169
which is mess because normal person would put first the 10169 and then
the others?
Thanks for any help Milan
hi Milan,
Milan Reznicek wrote:
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar
> but the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and
> then the others?
> Thanks for any help Milan
can you please post your actual code, as simple repro are correct, on my
instance..
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number
--<--
Part_Number
10169
10169-G
10169-MVS
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||What collation is your database using?
"Milan Reznicek" <reznicek@.rezna.info> wrote in message
news:uZCQw9ZSFHA.356@.TK2MSFTNGP14.phx.gbl...
> Hi guys,
> did anyone come across this problem?
> I have a query where I ORDER BY Part_Number field which is varchar but
> the ordered result is
> 10169-G
> 10169-MVS
> 10169
> which is mess because normal person would put first the 10169 and then
> the others?
> Thanks for any help Milan
>
|||Thanks both, you and JJ. Your code is working all right until I add my
collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and which
completely messes up the ordering.
Milan
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> pe v diskusnm pspvku
news:3d4df0F6opoh9U1@.individual.net...
> hi Milan,
> Milan Reznicek wrote:
> can you please post your actual code, as simple repro are correct, on my
> instance..
> SET NOCOUNT ON
> DECLARE @.t TABLE ( Part_Number varchar(10) )
> INSERT INTO @.t VALUES ( '10169-G' )
> INSERT INTO @.t VALUES ( '10169-MVS' )
> INSERT INTO @.t VALUES ( '10169' )
> SELECT t.Part_Number
> FROM @.t t
> ORDER BY t.Part_Number
> --<--
> Part_Number
> --
> 10169
> 10169-G
> 10169-MVS
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Milan,
Milan Reznicek wrote:
> Thanks both, you and JJ. Your code is working all right until I add my
> collation which is SQL_Czech_CP1250_CI_AS (JJ pointed about it) - and
> which completely messes up the ordering.
of course collation change the way the sort order is performed...
you can, if you are allowed to from your app design, change the sort order
rules for queries like that like
SET NOCOUNT ON
DECLARE @.t TABLE ( Part_Number varchar(10) )
INSERT INTO @.t VALUES ( '10169-G' )
INSERT INTO @.t VALUES ( '10169-MVS' )
INSERT INTO @.t VALUES ( '10169' )
SELECT t.Part_Number
FROM @.t t
ORDER BY t.Part_Number COLLATE Latin1_General_BIN
you specify that way a different sort order and rule the order by clause
must conform with... but verify this do not comprimise other app designs and
constraints...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.11.1 - DbaMgr ver 0.57.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply