StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWhat size to pick for a (n)varchar column?
text
Body
copied!<p>In a <a href="http://thedailywtf.com/Comments/A-Confusing-SELECTion.aspx?pg=2#281839" rel="noreferrer">slightly heated discussion on TDWTF</a> a question arose about the size of varchar columns in a DB.</p> <p>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.</p> <p>However for most DBMS it makes no difference in size or speed whether you make a varchar(50) or a varchar(255).</p> <p>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.</p> <p><hr> <strong>Added:</strong> People want references to the statement about "no difference in size or speed". OK. Here they are:</p> <p>For MSSQL: <a href="http://msdn.microsoft.com/en-us/library/ms176089.aspx" rel="noreferrer"><a href="http://msdn.microsoft.com/en-us/library/ms176089.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms176089.aspx</a></a></p> <blockquote> <p>The storage size is the actual length of data entered + 2 bytes.</p> </blockquote> <p>For MySQL: <a href="http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html" rel="noreferrer"><a href="http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html" rel="noreferrer">http://dev.mysql.com/doc/refman/5.1/en/storage-requirements.html</a></a></p> <blockquote> <p>L + 1 bytes if column values require 0 – 255 bytes, L + 2 bytes if values may require more than 255 bytes</p> </blockquote> <p>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.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload