CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designvarchar
user517491
user517491

Reputation:

Difference between VARCHAR(x) and VARCHAR(y) in SQL

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?

Upvotes: 3

Views: 863

Answers (5)

aneroid
aneroid

Reputation: 15962

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.

Upvotes: 2

nickle
nickle

Reputation: 5196

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.

Upvotes: 0

Olaf Dietsche
Olaf Dietsche

Reputation: 74048

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.

Upvotes: 0

Amir Qayyum Khan
Amir Qayyum Khan

Reputation: 473

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

Upvotes: 0

F.P
F.P

Reputation: 17831

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?

Upvotes: 2

Related Questions