Since varchar takes disk space proportional to the size of the field, is there any reason why we shouldn't always define varchar as the maximum, e.g. varchar(8000) on SQL Server?

On create table, if I see anyone doing varchar(100) I should tell them no you are wrong you should do varchar(8000)?

  • I think we have to differentiate here between use in create table and use in parameter declarations of stored procedures. For the second interpretation see my question dba.stackexchange.com/questions/1772/… – bernd_k Jun 4 '11 at 17:38
  • Length is a constraint on the data (like CHECK, FK, NULL etc)
  • Performance when the row exceeds 8060 bytes
  • Can not have unique constraint or index (key column width must be < 900)
  • The default is SET ANSI PADDING ON = potential for lots of trailing spaces to be stored
  • SQL Server will assume average length is 4000 for sort operations, allocating memory based on this (need to find a link to back this up but rust me while I do :-)

Summary: don't do it.

|improve this answer|||||
  • Performance when the row exceeds 8060 bytes. That refers to actual used bytes and not to maximal allowed bytes? – bernd_k Jun 5 '11 at 17:31
  • @bernd_k: 8060 = largest amount of data for one row that will fit into a single 8192 page. Include row overhead. Remainder (132 bytes) = page overhead – gbn Jun 5 '11 at 17:35
  • That means performance decreases when the bigger size is really used, not by the pure fact, that it it's use is allowed. – bernd_k Jun 5 '11 at 18:11
  • @bernd_k: any performance decrease is caused by 1. memory allocation for sorts etc 2. Row overflow (> 8060 bytes). The fact of having 10 chars in each varchar(8000) is irrelevant until these conditions are met (SQL will warn about potential errors later for index keys) – gbn Jun 5 '11 at 18:25
  • Is sorting a table with a varchar(100) column filled with fields of 100 byte length worth another question, because sorting assumes an average of 50 character ? – bernd_k Jun 5 '11 at 18:53

Assuming you are referring to SQL Server, I can think of one.

There is a limit to the size (8K) of a row in a table and SQL lets you define varchar fields that could theoretically exceed that limit. So the user could get errors if they put too much data in the field related to that.

Starting with SQL 2K8 you can exceed this limit, but there are performance implications.

Also, there is the whole reasonableness check of limiting the size to what you expect the data to look like. If you want an unbounded length field why not go with text or ntext?

|improve this answer|||||
  • so the text and ntext data types are stored in a way that does not impact performance? – Chad Jun 6 '11 at 17:57
  • Those types don't contribute as much to the 8K row size limit on a table because the actual data is stored in a separate table under the covers instead of inline with the rest of the columns. There is still a performance impact, but for different reasons. There are also limitations on feature support on these fields when compared to varchar and nvarchar – JohnFx Jun 6 '11 at 18:17
  • text and ntext are deprecated in favor of varchar(max). – Phil Helmer Jun 7 '11 at 5:49

Surely it depends on what information is being stored in the field?

Some things are going to have a maximum length for a number of reasons and if there has to be a maximum length then that should be the length of your field.

If theoretically there is no maximum length then I would question why varchar would be used.

|improve this answer|||||

I context of Oracle Databases I learned that always using the smallest field size for Database columns has one pitfall.

When moving data via import export from a database with single byte collation to one with multiple byte collation (like Oracle XE) the length in bytes can increase and importing the data into the tables created by import fails. Of course Oracle has the option to define varchar2 length either as char or as byte.

My point here, is that it is not always wise to define field with always as small as possible. I have seen a lot of alter tables to increase field withs later (caused by changed requirements).

Having 20% - 100% unused field with is a discussible option here.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.