Is there any downside to giving extra size/length to your database columns?

I usually set all my varchars to 255 to be safe. Does it make开发者_如何学Go any difference in terms of the disk space or anything else? Is there any downside to having bigger varchars/ints/other fields than you would mostly need?

---

If you allow 255 characters then someone may use 255 characters. And so every report and online data entry screen has to be able to handle 255 characters. Messy.

I have a real-world example: in our system someone decided that department names could be 200 chars long. When someone put in a rather long value, it screwed up the layout of several HTML forms, pushing other fields off the right hand side and making them inaccessible. So some remedial work was required...

---

It takes more time and more disk transfers to load larger data items into memory. Defining large maximum sizes for columns increases the size of table rows. For many DBMS servers, table rows are the items transferred. So defining columns that are too fat does slow things down.

This effect is minimal for VARCHAR items. But VARCHAR is quite a bit slower than data types like integers. Eight byte integers take four times as much time to transfer as two byte integers. So, if a database is being designed for ultimate performance, limiting data columns to the range actually required will speed things up.The extent of this effect depends on whether the disk channel is a bottleneck or not.

Another possible bottleneck is the channel that links the server with the client, often a network channel. Bottlenecking in this channel can be reduced by queries that don't ask for data that will never be used, but there's a trade off here between asking for data only when you need it and making too many round trips.

There's also a trade off between designing for optimal performance and over designing in the anticipation of changing requirements.

---

No that does not effect any disk space. If you choosed any column to be varchar that will be variable length from 0 to 65535. if you declare it 255 or 65535 they both are same.

if you needs only 255 chars you should declare them as char so that if there is no variable length column in your table your queries will be fast.

---

With disk space as cheap as it is, I tend to not be as concerned over this as I was years ago. However, little things add up in VLDB's.

---

This might give insight:

What are the optimum varchar sizes for MySQL?