*
The moose likes JDBC and the fly likes simple question - varchar(100) does it matter in terms of space? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "simple question - varchar(100) does it matter in terms of space?" Watch "simple question - varchar(100) does it matter in terms of space?" New topic
Author

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

Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
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
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Peter Primrose
Ranch Hand

Joined: Sep 10, 2004
Posts: 755
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
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

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
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: simple question - varchar(100) does it matter in terms of space?
 
Similar Threads
how can we use where clause with insert query using preparedStatement?
Couldn't Load Database Driver
problem in creating table with FULLTEXT KEY index
Hypersonic DB question
Highest score on java 5