Should Varchar Length Be Kept Small?

0
55
Asked By William Rossbach On

Whats the point of the length when defining a varchar in an SQL DB? It seems to default to 255, is there any point in limiting this to something like 5, if I know it will never exceed this value?

1 Answer

Answered By Keven Krok On

Well the answer here is not really, but it also doesn't make much of a difference on the small scale. On a large scale this should never even be a question.

The only time when the length of a varchar field matters is when you are using memory or temporary tables in MySQL. In this scenario the field will be padded to fill the space. For example, varchar(10) and you have a 4 letter string. This will be padded with an additional 6 characters to fill the space. This can become inefficient.

The main answer here is that it really doesn't matter that much. Most installs of SQL will not pad the fields to reach the maximum limit. If you are in a position where you are wondering if the field should be varchar(10) or varchar(50), just go for the larger. With such tiny values it really wont make a difference. This is enough to answer this question, but it does raise a different issue. If you are using a large value in the event that a user may enter 1000 characters, why is this happening? This might not be a problem, but when you have 10million rows, this 1000 character field will end up consuming an incredible amount of memory that will really hurt the performance of your server.

So if you are wondering the reason for varchar lengths in sql tables, the reason is not directly performance related. SQL is smart enough not to fill the column with pointless padding. What you should be asking is, is the design of my database efficient?

Related Questions

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.