simple question - varchar(100) does it matter in terms of space?

[Peter Primrose]

guys

varchar(100) I learned, is a variable length string character field up to
100 characters in length.

now say I build a database in mySql, is this ok???

FirstName varchar(100)

Or maybe this is better
FirstName varchar(20)

Does it matter?

thanks for any help.

[Shailesh Chandra]

Originally posted by Peter Primrose:

now say I build a database in mySql, is this ok???

FirstName varchar(100)

Or maybe this is better
FirstName varchar(20)

Does it matter?

Peter ,

There is difference between variable length and variable value

FirstName varchar(100) means your variable FirstName can hold a value uuto hunder character.
and
FirstName varchar(20) means variable FirstName can hold a value upto 20 character

but length of your variable is same in both case someone correct me if I am wrong.

hope I have interpreted your question correct

[Jeanne Boyarsky]

Peter,
I agree with Shailesh that only the number of characters actually in use is stored. Note this is not the case for char(100).

It's conceivable that a first name be more than 20 characters, so you should go a little longer. 100 is overkill though.

[Peter Primrose]

thank you.

I am still confused. if varchar(100) is just like varchar(20) than what is the diff? I mean, ok, the length is an issue but isn't there some kind of space issue?

say the name is: Peter (which is 5 chars) will firstName hold 100 chars for it -when using varchar(100)?

Peter

[Shailesh Chandra]

Originally posted by Peter Primrose:

say the name is: Peter (which is 5 chars) will firstName hold 100 chars for it -when using varchar(100)?

Peter,

Jeanne's post has your answer

only the number of characters actually in use is stored.

but don't forget to read this url, however it is oracle specific but you will get answer.

thanks

[Jeanne Boyarsky]

Originally posted by Peter Primrose:
if varchar(100) is just like varchar(20) than what is the diff? I mean, ok, the length is an issue but isn't there some kind of space issue?

Peter,
Yes, it only uses 5 bytes to store the 5 byte name. It's more a matter of intent. I could declare all my fields to be varchar(255), but this would be misleading. varchar(30) better specifies my intent because I am declaring that there will not be more than 30 characters.

Also, it helps if you have a front end. If the database field is varchar(100), you have to be able to handle 100 character first names in your GUI.

[Adeel Ansari]

As we know that there is a difference between char and varchar. VARCHAR doesn't use all given space.

Note the difference. Assume we have

FIRST_NAME varchar(20), and in other table we have
FIRST_NAME varchar(100)

We insert same name in both, "peter". "peter" will occupy same space of 5 characters in both. But when it is about update name, then the DB should take care of it. Now the modified name could be of 98 characters for the second table. But for the first table it cant exceed more than 20.

So, why the hell you are saying the DB server that there might be 100 characters in future, although you know it is not going to happen ever. As it is the FIRST_NAME column not a COMMENT column. Why Bothering DB by saying something, which never gonna happen.