Databases
StackOverflow2013
Postsdbo
POWhy use shorter VARCHAR(n) fields?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy use shorter VARCHAR(n) fields?
    text
    copied!<p>It is frequently advised to choose database field sizes to be as narrow as possible. I am wondering to what degree this applies to SQL Server 2005 <code>VARCHAR</code> columns: Storing 10-letter English words in a <code>VARCHAR(255)</code> field will not take up more storage than in a <code>VARCHAR(10)</code> field.</p> <p>Are there other reasons to restrict the size of VARCHAR fields to stick as closely as possible to the size of the data? I'm thinking of</p> <ul> <li>Performance: Is there an advantage to using a smaller n when selecting, filtering and sorting on the data?</li> <li>Memory, including on the application side (C++)?</li> <li>Style/validation: How important do you consider restricting colunm size to force non-sensical data imports to fail (such as 200-character surnames)? </li> <li>Anything else? </li> </ul> <p>Background: I help data integrators with the design of data flows into a database-backed system. They have to use an API that restricts their choice of data types. For character data, only <code>VARCHAR(n)</code> with n &lt;= 255 is available; <code>CHAR</code>, <code>NCHAR</code>, <code>NVARCHAR</code> and <code>TEXT</code> are not. We're trying to lay down some "good practices" rules, and the question has come up if there is a real detriment to using <code>VARCHAR(255)</code> even for data where real maximum sizes will never exceed 30 bytes or so. </p> <p>Typical data volumes for one table are 1-10 Mio records with up to 150 attributes. Query performance (<code>SELECT</code>, with frequently extensive <code>WHERE</code> clauses) and application-side retrieval performance are paramount.</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