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

Jeff Atwood
  • 2,374
  • 2
  • 21
  • 15
Aleksandr Levchuk
  • 1,227
  • 1
  • 10
  • 11

4 Answers4

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.

gbn
  • 70,237
  • 8
  • 167
  • 244
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?

JohnFx
  • 235
  • 1
  • 5
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.

Toby
  • 1,128
  • 2
  • 12
  • 12
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.

bernd_k
  • 12,369
  • 24
  • 79
  • 111