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

Basic evaluations in SELECT statement

Is there a way to determine several fields under one CASE or IF in a SELECT statement? For example, I don't think I can do this (though I'd like to):

@.Type INT

AS

SELECT

CASE @.Type

WHEN 1 THEN

Field1 = <some calculation>

Field2 = <Some calculation>

WHEN 2 THEN

Field1 = <some calculation>

Field2 = <some calculation>

END

The alternative, of course, is to evaluate @.Type twice, once for Field1 and again for Field2. But this seems like such a waste. Is there a better way?

Thanks,

One alternative might be to use an inline table -- maybe something like this:

declare @.type integer
set @.type = 2

select type,
field1,
field2
from ( select 1 as type, 1+3+5 as field1, 1*3*5 as field2 union all
select 2, 2+4+6, 2*4*6
) a
where type = @.type

/*
type field1 field2
-- -- --
2 12 48
*/

|||

Jararaca,

You are correct that a CASE can only return a single value.

So in YOUR case, you must use two CASE statements: one for Field1, and then another for Field2.

I think the speed of evaluation of the CASE usually exceeds the speed of retrieving the rows of data and/or formatting the result set.

Dan

|||

If only one condition value, How about this.

Code Snippet

If @.Type = 1

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

if @.Type = 2

begin

select
Field1 = <some calculation>
Field2 = <some calculation>

end

|||

Another method of doing this if you are wanting to apply it to an entire table is something like this:

declare @.tString char(20)
set @.tString = convert(char(10), 2*4*6)
+ convert(char(10), 1*3*5)

declare @.demo table
( rid integer,
type tinyint
)
insert into @.demo
select 1, 1 union all
select 2, 2 union all
select 3, 1 union all
select 4, 1 union all
select 5, 2

select rid,
rtrim(substring (@.tString, 10*type-9, 10))
computedField
from @.demo

/*
rid computedField
-- -
1 48
2 15
3 48
4 48
5 15
*/

bad case statement?

CASE WHEN u.sImage1_nm is NULL then 0
ELSEWHEN u.sImage2_nm is NULL then 1
ELSEWHEN u.sImage3_nm is NULL then 2
ELSEWHEN u.sImage4_nm is NULL then 3
ELSEWHEN u.sImage5_nm is NULL then 4
end
-- what's wrong here?
Looks good to me. Can you show the whole (simplified) query? Or, better yet, a repro?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.c om...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?
|||Tibor
It is wrong syntax assuming that below query (he missed addition CASE and
END respectively)
select CASE WHEN EndDate is NULL then 0
ELSE CASE WHEN InDate is NULL then 1
ELSE CASE WHEN Stardate is NULL then 2
end END END
from table
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8UlrvdfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Looks good to me. Can you show the whole (simplified) query? Or, better
yet, a repro?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0408082134.496c2e97@.posting.google.c om...
>
|||The extra else's are what is killing you... Try
select
CASE
WHEN u.sImage1_nm is NULL then 0
WHEN u.sImage2_nm is NULL then 1
WHEN u.sImage3_nm is NULL then 2
WHEN u.sImage4_nm is NULL then 3
WHEN u.sImage5_nm is NULL then 4
end from yourtable
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.c om...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?

bad case statement?

CASE WHEN u.sImage1_nm is NULL then 0
ELSE WHEN u.sImage2_nm is NULL then 1
ELSE WHEN u.sImage3_nm is NULL then 2
ELSE WHEN u.sImage4_nm is NULL then 3
ELSE WHEN u.sImage5_nm is NULL then 4
end
-- what's wrong here?Looks good to me. Can you show the whole (simplified) query? Or, better yet, a repro?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.com...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?|||Tibor
It is wrong syntax assuming that below query (he missed addition CASE and
END respectively)
select CASE WHEN EndDate is NULL then 0
ELSE CASE WHEN InDate is NULL then 1
ELSE CASE WHEN Stardate is NULL then 2
end END END
from table
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e8UlrvdfEHA.2468@.TK2MSFTNGP12.phx.gbl...
> Looks good to me. Can you show the whole (simplified) query? Or, better
yet, a repro?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
> news:1faa8782.0408082134.496c2e97@.posting.google.com...
> > CASE WHEN u.sImage1_nm is NULL then 0
> > ELSE WHEN u.sImage2_nm is NULL then 1
> > ELSE WHEN u.sImage3_nm is NULL then 2
> > ELSE WHEN u.sImage4_nm is NULL then 3
> > ELSE WHEN u.sImage5_nm is NULL then 4
> > end
> >
> >
> > -- what's wrong here?
>|||The extra else's are what is killing you... Try
select
CASE
WHEN u.sImage1_nm is NULL then 0
WHEN u.sImage2_nm is NULL then 1
WHEN u.sImage3_nm is NULL then 2
WHEN u.sImage4_nm is NULL then 3
WHEN u.sImage5_nm is NULL then 4
end from yourtable
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"n_o_s_p_a__m" <n_o_s_p_a__m@.mail.com> wrote in message
news:1faa8782.0408082134.496c2e97@.posting.google.com...
> CASE WHEN u.sImage1_nm is NULL then 0
> ELSE WHEN u.sImage2_nm is NULL then 1
> ELSE WHEN u.sImage3_nm is NULL then 2
> ELSE WHEN u.sImage4_nm is NULL then 3
> ELSE WHEN u.sImage5_nm is NULL then 4
> end
>
> -- what's wrong here?