Disadvantage of choosing large MAX value for varchar or varbinary

Question

What's the disadvantage of choosing a large value for max when creating a varchar or varbinary column?

I'm using MS SQL but I assume this would be relevant to other dbs as well.

Thanks

Answer 1

You could be adding a risk of breaking your application if a large data got in somehow (like from an external interface) and your app isn't designed to handle it.

As a good design, you should always limit the size of the fields to a realistic value.

Answer 2

That depends on whether it is ever reasonable to store a large amount of data in the particular column.

If you declare a column that would never properly store much data (i.e. an employee first name as a VARCHAR(1000)), you end up with a variety of problems

1. Many if not most client APIs (i.e. ODBC drivers, JDBC drivers, etc) allocate memory buffers on the client that are large enough to store the maximum size of a particular column. So even though the database only has to store the actual data, you may substantially increase the amount of memory the client application uses.
2. You lose the ability to drive data validation rules (or impart information about the data) from the table definition. If the database allows 1000 character first names, every application that interacts with the database will probably end up having its own rules for how large an employee name can be. If this is not mitigated by putting a stored procedure layer between all applications and the tables, this generally leads to various applications having various rules.
3. Murphy's Law states that if you allow 1000 characters, someone will eventually store 1000 characters in the column, or at least a value large enough to cause errors in one or more application (i.e. no one checked to see whether every application's employee name field could display 1000 characters).

Answer 3

Depends on the RDBMS. IIRC, MySql allocates a 2 byte overhead for varchars > 255 characters (to track the varchar length). MSSQL <= 2000 would allow you to allocate a row size > 8060 bytes, but would fail if you tried to INSERT or UPDATE a row that actually exceeded 8060 bytes. SQL 2005[1] allows the insert, but will allocate a new page for the overflow and leave a pointer behind. This, obviously, impacts performance.

[1] varchar(max) is somewhat of a special case, but will also allocate an overflow page if the length of the field is > 8000 or the row > 8060. This is with MSSQL defaults, and behavior can change with the large types in data row option.