difference between char and varchar

[TechBoyJK]

I'm having trouble understanding what is meant by fixed length strings and variable length.

Is this right?

When declaring a column and its attributes, you can decide on many data types, 2 of which include char and varchar.

With char, it is for fixed length. So you would declare that fixed length when declaring the column. With varchar, there is no max lenth for a string, just the maximum that varchar supports.

I guess the reasoning is, if you need to input character strings, and you know those strings will never be more than 100 characters, you could chose char, and declare it to be 100 characters in size. That way, when creating the data pages for the table rows, the db only needs to allocate enough memory to accomodate that max of 100 characters.

But with varchar, the string length could vary greatly, so it just allocates the same maximum space for every row?

 

[nickbits]

No. varchar only stores actual data+length byte(s), it doesn't reserve space for the max size.

Generally you use varchars.

 

[TechBoyJK]

Originally posted by: nickbits
No. varchar only stores actual data+length byte(s), it doesn't reserve space for the max size.

Generally you use varchars.

Then what's the difference with char? Does char actually reserve the space for max size?

 

[bsobel]

Originally posted by: TechBoyJK

Originally posted by: nickbits
No. varchar only stores actual data+length byte(s), it doesn't reserve space for the max size.

Generally you use varchars.

Then what's the difference with char? Does char actually reserve the space for max size?

char fields allocate that amount of space, you can think of them as fixed size records. I char(40) will always take 40 bytes, a varchar(40) will take upto 40 bytes.

 

[TechBoyJK]

Originally posted by: bsobel

Originally posted by: TechBoyJK

Originally posted by: nickbits
No. varchar only stores actual data+length byte(s), it doesn't reserve space for the max size.

Generally you use varchars.

Then what's the difference with char? Does char actually reserve the space for max size?

char fields allocate that amount of space, you can think of them as fixed size records. I char(40) will always take 40 bytes, a varchar(40) will take upto 40 bytes.

Thank you. So I guess, ideally, if diskspace is a concern, use varchar, but if the records change often and all of a sudden a varchar goes from needing 1byte to 40byte, if a datapage hasn';t already allocated the space, it may need to spread that data out and it won't be orderly again until the db is degragged... right?

 

[Markbnj]

Originally posted by: TechBoyJK

Originally posted by: bsobel

Originally posted by: TechBoyJK

Originally posted by: nickbits
No. varchar only stores actual data+length byte(s), it doesn't reserve space for the max size.

Generally you use varchars.

Then what's the difference with char? Does char actually reserve the space for max size?

char fields allocate that amount of space, you can think of them as fixed size records. I char(40) will always take 40 bytes, a varchar(40) will take upto 40 bytes.

Thank you. So I guess, ideally, if diskspace is a concern, use varchar, but if the records change often and all of a sudden a varchar goes from needing 1byte to 40byte, if a datapage hasn';t already allocated the space, it may need to spread that data out and it won't be orderly again until the db is degragged... right?

ANSI compatible padding behavior is also different between char and varchar, so if that matters you'll need to review it. Generally char is useful for fixed length fields, or fields you always want padded out to a fixed length. About the only time I use it is for single character flags like 'Y' and 'N'.

 

[bsobel]

Correct, fixed size tables are generally faster since the DB can easily calculate where the next record is. Doesnt matter as much with small db's but is important in enterprise scenarios.

 

[Bulldog13]

obligatory use nvarchar instead of varchar

 

[TechBoyJK]

Originally posted by: Bulldog13
obligatory use nvarchar instead of varchar

what is that?

 

[Markbnj]

Originally posted by: TechBoyJK

Originally posted by: Bulldog13
obligatory use nvarchar instead of varchar

what is that?

Unicode-safe version of varchar.