23

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)?

1
  • 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, 2011 at 17:38

4 Answers 4

18
  • 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.

6
  • Performance when the row exceeds 8060 bytes. That refers to actual used bytes and not to maximal allowed bytes?
    – bernd_k
    Jun 5, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 18:53
7

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?

3
  • so the text and ntext data types are stored in a way that does not impact performance?
    – Chad
    Jun 6, 2011 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, 2011 at 18:17
  • text and ntext are deprecated in favor of varchar(max). Jun 7, 2011 at 5:49
3

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.

3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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