Menu
Yes, it matters from the performance point-of-view. Query Optimizer looks at this meta data to plan the query. It estimates the row size based on the provided length and this can cause a performance issue.
The size of the maximum size (m) parameter of a VARCHAR column can range from 1 to 255 bytes. If you are placing an index on a VARCHAR column, the maximum size is 254 bytes. You can store character strings that are shorter, but not longer, than the m value that you specify.
You must specify max of the NVARCHAR column. The size of this parameter cannot exceed 255 bytes. When you place an index on an NVARCHAR column, the maximum size is 254 bytes. You can store shorter, but not longer, character strings than the value that you specify.
The minimum size of the NVARCHAR value is 1 byte. The total length of an NVARCHAR column cannot exceed 255 bytes. You can store character strings that are shorter - but not longer - than the specified value.
In a slightly heated discussion on TDWTF a question arose about the size of varchar columns in a DB.
For example, take a field that contains the name of a person (just name, no surname). It's quite easy to see that it will not be very long. Most people have names with less than 10 characters, and few are those above 20. If you would make your column, say, varchar(50), it would definately hold all the names you would ever encounter.
However for most DBMS it makes no difference in size or speed whether you make a varchar(50) or a varchar(255).
So why do people try to make their columns as small as possible? I understand that in some case you might indeed want to place a limit on the length of the string, but mostly that's not so. And a wider margin will only be beneficial if there is a rare case of a person with an extremely long name.
For MSSQL: http://msdn.microsoft.com/en-us/library/ms176089.aspx
The storage size is the actual length of data entered + 2 bytes.
For MySQL: http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html
L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes
I cannot find documentation for Oracle and I have not worked with other DBMS. But I have no reason to believe it is any different there.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With