• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

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

 
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
 
author & internet detective
Posts: 40196
816
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
author & internet detective
Posts: 40196
816
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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.
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
That new kid is a freak. Show him this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic