SQL Server VARCHAR(8000) – Consequences and Best Practices

sql servervarchar

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

Best Answer

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

Related Question