3

I have learnt that VARCHAR occupies the only memory which is required unlike CHAR which always occupy same amount of memory whether needed or not.

My question: Suppose I have a field VARCHAR(50), I know that if it needs to store 30 characters, it will only occupy 30 bytes and no more than that(assuming 1 char takes one byte). So why should I even mention 50 or 30 or any upper limit, since it will only take the memory which is required.

UPDATE: Why do I have to mention the upper limit since there will be no useless memory occupied?

  • 1
    One of the benefits is that database will raise an error every time someone tries to enter `n` (`n>50`) bytes in this column. So database designer can be sure that this record won't waste more than 50 bytes – default locale Dec 06 '12 at 08:01
  • 1
    Duplicate of this question: http://stackoverflow.com/questions/262238/are-there-disadvantages-to-using-a-generic-varchar255-for-all-text-based-field – Diego Basch Dec 06 '12 at 08:02
  • @DiegoBasch Perhaps I should modify the question tags –  Dec 06 '12 at 08:04

5 Answers5

2

You wouldn't. You would make it VARCHAR(30). It's about the maximum amount of characters allowed. So why would you even make a column that takes 30 characters accept anything up to 50?

F.P
  • 17,421
  • 34
  • 123
  • 189
  • my question is why do I have to mention the upper limit, since there will be no useless memory occupied. –  Dec 06 '12 at 08:02
  • 1
    Well, because you only want to ALLOW that much characters. That's not a decision of memory, but of your application design. If you only want to allow X characters, you NEED to specify that SOMEWHERE, don't you? – F.P Dec 06 '12 at 08:07
  • @djaqeel, how do you know that there will be no useless memory occupied? If you don't mention the upper limit then another developer working on the same database might think that it'd be cool to store a couple of thousands chars in this column. – default locale Dec 06 '12 at 08:10
2

UPDATE: Why do I have to mention the upper limit since there will be no useless memory occupied?

If you are sanitizing your inputs with something like final_value = left(provided_value, 30) then it's a non-issue for your database. You can set it to varchar(255) if you like.

The idea of putting the max limit is to ensure you don't mistakenly send more chars than what you actually plan for.

  1. Would be a pain in the future for code maintenance to recall the data size limit of every column of every table. You need to do that anyway but by looking at your table definitions as the single source for info about that.

  2. Would a table be written to (insert/update) from only one piece of code in your app or website? If there's another interface to the database like, say, a REST API listener, if you don't enter the same values again, you'll have an issue with non-uniform data - exactly what db's are able to prevent.

  3. If a coding error (or hack) bypasses your app/website controls for data (size limits, or worse) then at least your db will still be maintaining the data correctly.

aneroid
  • 12,983
  • 3
  • 36
  • 66
0

to make things dynamic you are using VARCHAR(50) because in future the string size can be exceed and you knows that the maximum size can be 50, But for constant(s) you can use CHAR(30),this means that the size of string will be always 30 , my sql will report exception if the size exceed or decrease

take a look http://dev.mysql.com/doc/refman/5.0/en/char.html

Amir Qayyum Khan
  • 473
  • 2
  • 15
0

You can take VARCHAR(50) or VARCHAR(30). It's not a problem, but if it's dynamic we can't tell the limit.

In that case we take maximum limit.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
nickle
  • 4,636
  • 1
  • 13
  • 11
0

When memory usage is your only concern, you can give any large number to varchar. But if you want to make sure that an upper limit is kept, than you give that as a maximum to varchar.

Olaf Dietsche
  • 72,253
  • 8
  • 102
  • 198