What is the difference between using varchar(100) and varchar(255)?

Question

I want to know difference between varchar(100) and varchar(255) in terms of memory usage and programming problems of using more memory.

Answer 1

The difference is going to be in how much data you are allowed to store there. The memory and disk space will depend on how much is actually stored, not what limits you put in place.

Most db's (MySQL included) store varchar fields with a length identifier and then a variable length data string. This means that the size of the field on disk goes up more or less linearly with the actual data stored. Similarly in memory, you aren't going to be allocating a large amount of memory for a small string unless it is padded (so varchar uses a lot less memory than char does regardless of underlying storage of the latter). In fact some db's like PostgreSQL store VARCHAR() and TEXT fields the same (and there is a 1GB compressed size limit there) and try to optimize storage based on actual requirements of the data being used.

In general these decisions should be made based on what your data retention and collection needs are, not based on the performance considerations. The performance can be tuned and that's the lovely thing about an RDBMS.

Answer 2

Since varchar takes disk space proportional to the size of the field, so the length of a field should be appropriate to the data that is being stored there, not least because there is a limit to the length of single record in each database e.g. SQL Server (it's ~8000 bytes).